Using SQLConnection across multiple methods ('using' keyword yes or no)

Question

I want to re-use the same SQLConnection accros different methods within the class. What I'm doing right now (testing only) is creating and opening a connection in the constructor:

SQLConnection Connection;

Constructor(string connection_string)
{
    this.Connection = new SqlConnection(connection_string);
    this.Connection.Open();
}

then I use "this.Connection" inside methods and finally use this.Connection.Close() and Dispose() at the end when the object is not needed anymore. From what I know it would be cleaner to use 'using' inside each method like this (the constructor would only set the connection_string):

using (SqlConnection connection = new SqlConnection(connection_string)) {
 connection.Open(); ...
}

Because of connection pooling, only one connection is actually used, despite the above 'using' line being placed in multiple methods (e.g. when they are called one after another), correct? However, wouldn't this create many SQLConnection instances where only one would be needed? E.g.:

MyClass obj(some_string);
obj.Method1(); // calls 'using SqlConnection connection = new SqlConnection'
obj.Method2(); // calls 'using SqlConnection connection = new SqlConnection'
obj.Method3(); // calls 'using SqlConnection connection = new SqlConnection'

So what's the proper, optimal way to share the SQLConnection?

[See @TimSchmelter's answer here](http://stackoverflow.com/a/9707060/4068) — Austin Salonen, Jan 30 '13 at 16:30
Constructor's should not perform long running operations such as opening a connection to a DB. Also, I tend to obtain my connection string from a static (or singleton based on need) vs passing into constructors. This avoids verbose DL's and offers a single place to update. — P.Brian.Mackey, Jan 30 '13 at 16:35
...and just to make it a known-unknown: Ensuring you also get your database transaction handling / unit-of-work correct is left as an exercise for the reader. — mwardm, Mar 10 '16 at 17:17

Mike Dinescu · Accepted Answer · 2013-01-30T20:32:51.293

You are correct about all your statements. However you are missing one important point: creating lots of instances of a type in .NET is not necessarily a bad thing!

You should not create a single connection instance in the constructor
You should create a connection locally, when needed
It is best practice to use the using paradigm when creating disposable objects
You should make a habit of calling Dispose on disposable objects in a try...finall block (if not using using) - in case your specific scenario doesn't land itself well for the use of usings; for instance using asynchronous methods)
Finally, you should not keep a SQL connection open for longer than you need it to be. Again, just take advantage of connection pooling in the ADO.NET provider for SQL Server

Now, the reason it's not a problem to create lots of instances of the Connection type is because creating objects in the .NET CLR is optimized (fast memory allocation and object instantiation) and relatively pain free (no need to worry about releasing memory thanks to Garbage Collection).. You also have the benefits of connection pooling in case of the ADO.NET providers so really, you shouldn't concern yourself with managing the number of instances of this Type.

It should be obvious that in other cases (such as heavy/large objects) creating lots of them can have an impact on memory pressure and performance. So always asses the situation as best as you can..

Using SQLConnection across multiple methods ('using' keyword yes or no)

1 Answers1