CODEWITHSUNDEEP
sql
Panks
Panks

Reputation: 611

Is it useful to Explicitly close SqlConnection just before exiting method

I have 100s of such methods and each method is called more than thousand times. Here with each call a new SqlConnection is created)(taken from pool). Though the methods are small and control immediately leaves the method and SqlConnection is supposed to be collected by GC. 

Method()
 { 
   MyComponent adapter = new MyComponent (); 
   adapter.Connection = GetConnection(dbContext);//Here I get new SqlConnection 
   adapter.Update(_SqlTable);

} //End of Method

My question is - Does the following optimization makes any difference ?? 

Method(){
        MyComponent adapter = new MyComponent ();
        adapter.Connection = GetConnection(dbContext);//Here I get new SqlConnection
        adapter.Update(_SqlTable);
        adapter.Connection.Close() // Or Dispose()
   } //End of Method

Is there any better way to write these methods (e.g make them static static methods )

Upvotes: 1

Views: 166

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1502816

Yes, you should absolutely close a connection rather than just letting it get finalized. Are you trying to write your own connection pooling? If so, don't - the framework provides it automatically. Just create a new SqlConnection and it will take the underlying connection from a pool.

You should use a using statement to make sure the connection is disposed whatever happens:

using (SqlConnection connection = GetConnection(dbContext))
{
    MyComponent adapter = new MyComponent(connection);
    adapter.Update(_SqlTable);
}

You should document that MyComponent does not take responsibility for the lifetime of the connection in this case. An alternative would be to make MyComponent implement IDisposable, pass it the data context in the constructor, and dispose of that:

using (MyComponent adapter = new MyComponent(dbContext))
{
    adapter.Update(_SqlTable);
}

It's hard to give much further design advice without knowing what MyComponent does.

Upvotes: 5

Related Questions