Using SQLConnection for multiple methods ("using" the yes or no keyword)
I want to reuse the same SQLConnection that activates various 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 the methods and finally I use this.Connection.Close () and Dispose () at the end when the object is no longer needed. From what I know it would be cleaner to use "use" inside every method like this (the constructor would only set the connection_string):
using (SqlConnection connection = new SqlConnection(connection_string)) {
connection.Open(); ...
}
Because of the connection pooling, only one connection is actually used, even though the above "using" line is put into multiple methods (for example when they are called one after the other), right? However, wouldn't that create many SQLConnection instances where only one is needed? For example:.
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 correct, optimal way to exchange SQLConnection?
source to share
You treat all your statements correctly. One important point you are missing, however, is that creating many instances of a type in .NET is not necessarily bad. !
- You shouldn't create one instance of the connection in the constructor
- You must create a local connection if needed
- Best practice use a paradigm
using
when creating disposable objects - You should get in the habit of calling Dispose on disposable objects in a block
try...finall
(if not usedusing
) - in case your particular scenario is not suitable for using ofusings
; for example using asynchronous methods) - Finally, you shouldn't keep the SQL connection open for longer than you need to. Again, just use connection pooling in the ADO.NET Provider for SQL Server
Now, the reason there is no problem creating multiple instances of type Connection is that object creation in the .NET CLR is optimized (fast memory allocation and object creation) and relatively painless (no need to worry about freeing memory thanks to assembly garbage). You also have the benefits of connection pooling with ADO.NET providers, so you don't have to worry about managing the number of instances of this type.
It should be obvious that in other cases (e.g. heavy / large objects) creating many of these can affect memory pressure and performance. So always challenge the situation as best you can.
source to share