none
Maintain Connection Between Calls RRS feed

  • Question

  • I'm running a loop with a LINQ to SQL call for each interation.  My context is scoped outside of the loop.  When I run SQL Profiler, I see a sp_reset_connection and an Audit Logout after each execution of my LINQ to SQL call.  Back in the old VB6 days of ADO, we could get each call to executed without connections resets between executions.  How can I accomplish this in LINQ to SQL?  This process seems awfully slow.  Thanks.
    Monday, September 8, 2008 3:11 PM

All replies

  • You may create an explicit connection by yourself, open it explicitly and pass it to datacontext constructor. However when you are done using the connection you will need to close and clear it yourself.

     

    ~Deepti
    Wednesday, September 10, 2008 5:26 PM
  • If I supply my own connection object, would I no longer require the using block around the DataContext and move it to the Connection object?

     . Deepti[MSFT] wrote:
    You may create an explicit connection by yourself, open it explicitly and pass it to datacontext constructor. However when you are done using the connection you will need to close and clear it yourself.

     

    ~Deepti
    Friday, September 12, 2008 6:15 PM
  • If I create a SqlConnection and pass it to my DataContext, there is still a exec sp_reset_connection and an Audit Logout between each sproc execution.  Your solution did not solve the problem.
    Friday, September 12, 2008 6:22 PM
  • When you use connection pooling to connect to SQL Server, SQL Server calls the sp_reset_connection stored procedure before re-using a connection from the pool.

     

    SQLClient should call sp_reset_connection only when a new connection is obtained from the connection pool. LinqToSQL will not try to close the connection if i has not opened it, that is why I suggested opening a connection outside of context and passing it to the context.  

     

    Something in application is refetching the connection from the pool and hence reseting it. You can explicity set the connectionpooling's "Connection Reset" to "false" to avoid this, but that will not be a good way to handle it.

     

    Can you forward the code snippet to us so that we can determine what exactly is reseting the connection?

     

    ~Deepti

     

    Friday, September 12, 2008 11:01 PM