Best practice for reusing SqlConnection

Question

I've come from Java experience and am trying to start with C#. I've read SqlConnection SqlCommand SqlDataReader IDisposable and I can understand that the best practice to connecting to a DB is wrapping SqlConnection, SqlCommand and SqlDataReader in their own using block.

But in Java we use to encapsulate the connection into a factory method, create it only once, and reuse it for all queries, even multithreaded ones. Only statements and result sets are created for each query and closed ASAP.

Isn't creating a new SqlConnection for each query kinda overkill? Can't it be reused?

Mckoy

you could use a pooling mechanism – Middleman

msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx – Middleman

@DanielA.White The .net framework provides the pooling mechanism for you (which is why the advice is to always use a using block) – Designed

Make sure you error handle the connection, and in the Finally block close it. Otherwise you'll wind up with orphaned connections that will drag your performance down. – Cerotype

@Tim, not if its wrapped in a using block, try finally isn't required. – Tool

@RowlandShaw - I know this is an old post. Can you tell me if there is ever a case not to use "using"? For example, if you know a connection is going to be re-used rapidly, would you just do: var context = new myEntitites(); This is in relation to using .Net Framework 4.8. – Beilul

@JamesLee I would always advocate each SqlConnection being in a using block - whilst on a "happy path" you wouldn't see anything different, you could end with the thread pool being exhausted if something didn't go to plan, and debugging that is going to be "fun" – Designed

Thank you @RowlandShaw - appreciate you taking the time to answer a question on an old post! – Beilul

Answer 1

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

When you have finished with your connection (through which you can send multiple queries) just Close() or Dispose() (or use a using{} block preferably).

There is no need, and not good practise, to cache instances of the SqlConnection class.

Irreconcilable

True for MS SQL. Tested with MySQL - reusing connections gives considerable performance gain. However - own "connection pool" is needed to make operations thread-safe. Accessing connection object from a different thread causes an exception. So for MySQL I reuse connections for the same ManagedThreadId. – Glop

@Glop - its a shame that the MySql library writers didn't follow the pre-existing SqlClient methodology, but glad you pointed it out. – Irreconcilable

Great answer. Linking official docs: learn.microsoft.com/en-us/dotnet/framework/data/adonet/… – Demetriusdemeyer

Answer 2

As VMAtm has said, .net Pools the connections on it's own, so it is perfectly ok to recreate them. As such I generally 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]);
                }
            }
        });

Pamela

Of course you could just use a Micro ORM like Peta Poco and make your whole world 100* easier without all the bloat of the Entity Framework. – Pamela

very nice wrapper, would like to see if it can be used as a singleton, upvoted! – Kinesiology

@transformer We have modified this a lot since I wrote this. I'll try to remember to post a better example sometime soon. Basically though we've warfed into something like "GetListFromProc<MyThing>(storedProcName, getItemFromReader); Which returns a list of MyThing and getItemFromReader is a function that get's passed the SqlDataReader row where you instantiate MyThing and return it. – Pamela

Answer 3

MS SQL server manages connections in it's own connection pool, and they aren't actually disposed. But they are closed so you minimize the network traffic and release the available connections to your server.

Also you should note that if you are using the Linq-To-SQL, the data context will not release the connection until being disposed, so I suggest you just use already working code and do not try to optimize it by yourself.

Bufford

I believe it's the .Net client, rather than the server, that is managing those connections and pooling them as required. – Designed

"and they aren't actually disposed. But they are closed" - other way around; they are disposed (if you are correctly using using, etc), and quite possibly garbage-collected (non-determinstic etc), but the underlying unmanaged connection remains open – Regarding

Thanks. I don't use Linq, it's trash. I use pure and optimized SQL. – Mckoy

Answer 4

To answer your specific question, you can reuse a SqlConnection for each query. Just make sure to close your current query (SqlDataReader, etc.) before you run another one, ie. wrap them in their own using blocks.

Lockard

Problem comes in with Unit testing from a build machine that does not have permissions or access to the database. How do you handle this type of testing? – Bladderwort

Answer 5

Yes, you can create a global SqlConnection instance. In my case I use the SqlConnection as member of my DataContext that I access via 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 might leave the connection open, but instead specifically begin and end transactions:

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();

Upsetting

I think this is a flawed approach. Firstly there is no advantage to caching a SqlConnection in code - the framework does it for you, and secondly it prevents multi-threaded use of the DbContext you have created. – Irreconcilable

PhillipH hit the nail on the head. Also, reuse of an existing SqlConnection requires thread confinement at the very least (plus excessive amounts of coordination among all users so they don't stomp on each others transactions or run into reuse restrictions (MultipleActiveResultSets), even if everything is single-threaded). The use of a singleton runs counter to all that; in a way it is pretty much the exact opposite of what is required. – Illuminance