Best practices for reusing SqlConnection

Question

I came from a Java experience and am trying to start with C #. I read the SqlConnection SqlCommand SqlDataReader IDisposable , and I understand that the best practice for connecting to a database is to wrap SqlConnection , SqlCommand and SqlDataReader in their own using block.

But in Java, we use to encapsulate the connection in the factory method, create it only once and reuse it for all requests, even multi-threaded ones. For each query, only statements and result sets are created and closed as soon as possible.

Isn't a new SqlConnection created for every request, like overkill? Is it possible to reuse it?

Answer 1

Creating a new instance of the SqlConnection class does not create a new network connection to SQL Server, but rents an existing connection (or creates a new one) .. NET manages the pool of physical connections for you.

When you are done with your connection (through which you can send multiple requests), simply Close() or Dispose() (or preferably use the using{} block).

It is not necessary, but not good practice, to cache instances of the SqlConnection class.

Answer 2

The MS SQL server manages connections in its own connection pool, and they are not actually located. But they are closed, so you minimize network traffic and free up available connections to your server.

It should also be noted that if you use Linq-To-SQL, the data context will not release the connection until it is deleted, so I suggest you use the already existing code and not try to optimize it yourself.

Answer 3

As VMAtm said, .net pools connect to it independently, so it’s normal to recreate them. As a rule, I usually write a wrapper for the whole process, like this one.

  public static void RunWithOpenSqlConnection(string connectionString, Action<SqlConnection> connectionCallBack) { SqlConnection conn = null; try { conn = new SqlConnection(connectionString); connectionCallBack(conn); } catch (Exception ex) { //Log Error Here } finally { if (conn != null) conn.Dispose(); //will close the connection } } public static void ExecuteSqlDataReader(string connectionString, string sqlCommand, Action<SqlDataReader> readerCallBack) { RunWithOpenSqlConnection(connectionString, delegate(SqlConnection conn) { SqlCommand cmd = null; SqlDataReader reader = null; try { cmd = new SqlCommand(sqlCommand, conn); reader = cmd.ExecuteReader(); readerCallBack(reader); } catch (Exception ex) { //Log Error Here } finally { if (reader != null) reader.Dispose(); if (cmd != null) cmd.Dispose(); } }); } //Example calling these ExecuteSqlDataReader(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString, "Select EmployeeID FROM Employees;", delegate(SqlDataReader reader) { List<string> employeeIds = new List<string>(); if (reader.HasRows) { while(reader.Read()) { employeeIds.Add((string)reader[0]); } } }); 

Answer 4

To answer your specific question, you can reuse SqlConnection for each request. Just close your current request ( SqlDataReader , etc.), before starting another, i.e. wrap them in your own using blocks.

Answer 5

Yes, you can create a global instance of SqlConnection. In my case, I use SqlConnection as a member of my DataContext, which I access through Singleton.

 public class DatabaseDataContext : DataContext { private static DatabaseDataContext instance; private SqlConnection sqlConnection; private SqlTransaction sqlTransaction; //... public static DatabaseDataContext Instance { get { return instance ?? (instance = new DatabaseDataContext(connectionString)); } set { instance = value; } } } 

You can encapsulate your transactions by closing and opening this connection, i.e.:

 DatabaseDataContext.Instance.sqlConnection.Open(); // your transactions... sqlConnection.Close(); 

Or you can leave the connection open, but instead, start and end the transaction:

 DatabaseDataContext.Instance.sqlConnection.Open(); sqlTransaction = sqlConnection.BeginTransaction("Example Insert users"); try{ // ...your first transaction sqlTransaction.Commit(); } catch{sqlTransaction.Rollback();} sqlTransaction = sqlConnection.BeginTransaction("Update baked breads"); try{ // ...your second transaction sqlTransaction.Commit(); } catch{sqlTransaction.Rollback();} // Close the connection at some point sqlConnection.Close();