C# Using one SqlConnection for multiple queries

Question

How to correctly use one SqlConnection object for multiple queries?

SqlConnection connection = new SqlConnection(connString);

static void SqlQuery(SqlConnection conn, string cmdString)
{
    using (conn)
    { 
        if (conn.State != ConnectionState.Open)
        {
            conn.Close();
            conn.Open();
        }
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = cmdString;
        cmd.ExecuteNonQuery();
    }
}

Function SqlQuery after 1st invoking throws System.InvalidOperationException "ConnectionString property not initialized"

You can reuse the same connection so long as you don't already have an open `SqlDataReader` reading query results from a previous query. — Kenneth K., Feb 28 '18 at 04:40

TheGeneral · Accepted Answer · 2018-10-04T22:46:06.260

10

In short don't do it

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.

Update

This is a better pattern for your method, you dont have to worry about the connections state

static void SqlQuery(string cmdString)
{
    using (var connection = new SqlConnection(connString))
    using (var cmd = connection.CreateCommand(cmdString, connection))
    { 
        connection.Open();    
        // query        
        cmd.ExecuteNonQuery();
    }
}

edited Oct 04 '18 at 22:46

answered Feb 28 '18 at 04:40

TheGeneral

79,002
9
103
141

@AntoshaShmonoff stop fibbing :) – TheGeneral Feb 28 '18 at 04:46
anyway it is necessary to open connection – shmnff Mar 01 '18 at 00:41
@AntoshaShmonoff you can use the one connection, but there should never really be a situation were you need to test if its closed, it should always be wrapped in a using, even at a higher level. your code should be simple enough to have this relationship obvious. – TheGeneral Mar 01 '18 at 00:48
What about when you are inside a TransactionScope and need to call helper methods...originally, my helper method was isolated and created its own connection and ran a query, but now when I try to open the second SqlConnection is says it does not support distributed transactions. – Terry Apr 13 '21 at 18:59

score 2 · Answer 2 · answered Feb 28 '18 at 05:26

2

It depends on what you really mean/intend to do. If you mean batching a set of commands? Then yes,

it's arguably better to use one connection. Yes, connection pooling does save (all of) us, but if you really thought about it, what does it do? Yup, it reuses connections...
Performing Batch Operations
tips/pointers on SqlCommand as well

Hth.

answered Feb 28 '18 at 05:26

EdSF

11,753
6
42
83

do you mean that I have to collect all queries and then perform one connection to database? – shmnff Feb 28 '18 at 08:01
@AntoshaShmonoff Yes, aka "batch" as all the linked references show. That would be the **only** valid reason to keep the (single) connection open. Obviously that won't work in all cases so again, it _depends_ on what you want to do. – EdSF Feb 28 '18 at 18:13
I am looking to make a second query dependent on results from a previous insert using SCOPE_IDENTITY() . Will I loose the SCOPE_IDENTITY if I close the connection between SQL statements? – Nate Jan 17 '20 at 22:07

C# Using one SqlConnection for multiple queries

2 Answers2

Linked