26

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?

Community
  • 1
  • 1
Hikari
  • 3,797
  • 12
  • 47
  • 77
  • you could use a pooling mechanism – Daniel A. White Dec 29 '14 at 15:58
  • 2
    http://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx – Daniel A. White Dec 29 '14 at 15:59
  • 4
    @DanielA.White The .net framework provides the pooling mechanism for you (which is why the advice is to always use a `using` block) – Rowland Shaw Dec 29 '14 at 16:00
  • 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. – Tim Dec 29 '14 at 16:02
  • @Tim, not if its wrapped in a using block, try finally isn't required. – prospector Dec 29 '14 at 16:03
  • @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. – James Lee Apr 25 '23 at 19:57
  • 1
    @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" – Rowland Shaw May 05 '23 at 07:51
  • 1
    Thank you @RowlandShaw - appreciate you taking the time to answer a question on an old post! – James Lee May 09 '23 at 20:29

5 Answers5

50

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
PhillipH
  • 6,182
  • 1
  • 15
  • 25
  • 10
    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`. – Harry Dec 17 '16 at 22:08
  • 8
    @Harry - its a shame that the MySql library writers didn't follow the pre-existing SqlClient methodology, but glad you pointed it out. – PhillipH Dec 19 '16 at 22:51
  • 1
    Great answer. Linking official docs: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-creation-and-assignment – Sudhanshu Mishra Oct 06 '21 at 01:14
7

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]);
                }
            }
        });
Ryan Mann
  • 5,178
  • 32
  • 42
  • 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. – Ryan Mann Dec 29 '14 at 16:33
  • very nice wrapper, would like to see if it can be used as a singleton, upvoted! – Transformer Jan 07 '17 at 22:31
  • 4
    @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(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. – Ryan Mann Jan 09 '17 at 01:53
6

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.

VMAtm
  • 27,943
  • 17
  • 79
  • 125
  • 6
    I believe it's the .Net client, rather than the server, that is managing those connections and pooling them as required. – Rowland Shaw Dec 29 '14 at 16:01
  • 7
    "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* – Marc Gravell Dec 29 '14 at 16:36
  • Thanks. I don't use Linq, it's trash. I use pure and optimized SQL. – Hikari Jan 26 '15 at 14:16
3

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.

ElGavilan
  • 6,610
  • 16
  • 27
  • 36
  • 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? – user3846642 Oct 12 '21 at 21:50
-3

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();
flaudre
  • 2,358
  • 1
  • 27
  • 45
  • 6
    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. – PhillipH Jun 18 '16 at 12:06
  • 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. – DarthGizka Aug 24 '21 at 09:14