Connection pool

Question

I have the following code. If I use conn == null, after all, am I still using the connection pool? I know this is good practice to close your connection, but how to get rid of the whole connection object?

public void ExecuteNonQuery(SqlCommand Cmd) { //========== Connection ==========// SqlConnection Conn = new SqlConnection(strConStr); try { //========== Open Connection ==========// Conn.Open(); //========== Execute Command ==========// Cmd.Connection = Conn; Cmd.CommandTimeout = 180; Cmd.ExecuteNonQuery(); } catch (Exception Exc) { throw Exc; } finally { //======== Closing Connection ========// if (Conn.State == ConnectionState.Open) { Conn.Close(); } //======== Disposing object ========// Conn = null; } } 

Answer 1

First, consider using using , and secondly, enable the framework utility. managed providers will do the pool based on the connection string.

 public void ExecuteNonQuery(SqlCommand Cmd) { //========== Connection ==========// using(SqlConnection Conn = new SqlConnection(strConStr)) { //========== Open Connection ==========// Conn.Open(); //========== Execute Command ==========// Cmd.Connection = Conn; Cmd.CommandTimeout = 180; Cmd.ExecuteNonQuery(); } } 

Answer 2

• You do not need to set anything to null or nothing to .NET. This is automatically handled by the Garbage Collection .

• It is imperative that Conn.Dispose () be called in both your error handling and if everything goes according to plan. The Try Try Catch block is great for this. Another alternative is to use the Using keyword when declaring your connection, so that the Connection object will be properly deleted no matter what happens.

• Do not worry about combining your connections. If you open a new one every time you need to use it, that's fine. ADO.NET links connect you backstage. There is little performance if you reuse the same open connection object, but to keep things simple, don't worry about it.

You can do the same with the following code. The result will be the same. Regardless of whether this is an error or not, Conn will be handled properly. Any error will seep up, as before.

 public void ExecuteNonQuery(SqlCommand Cmd) { Using (SqlConnection Conn = new SqlConnection(strConStr)); { //========== Open Connection ==========// Conn.Open(); //========== Execute Command ==========// Cmd.Connection = Conn; Cmd.CommandTimeout = 180; Cmd.ExecuteNonQuery(); } } 

As you can see, when the only error handling you want / need is to make sure your connection objects are removed properly, Using syntax can make things tidy.

Answer 3

Setting it to null is redundant since it will go beyond the scope at the end of the function. Yes, you are still using the connection pool if you do.

Answer 4

You can dispose of your object and use the pool.

As well as ff him

• Managed application
• Is the web application
• Has heavy traffic

Then getting rid of the item will do you a lot of good, since the garbagecollector will usually not support enough to maintain performance.