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.
– Kenneth K., Commented Feb 28, 2018 at 4:40

TheGeneral · Accepted Answer · 2018-10-04 22:46:06Z

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 4, 2018 at 22:46

answered Feb 28, 2018 at 4:40

TheGeneral

82k9 gold badges110 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TheGeneral Over a year ago

@AntoshaShmonoff stop fibbing :)

shmnff Over a year ago

anyway it is necessary to open connection

TheGeneral Over a year ago

@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.

Terry Over a year ago

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.

EdSF · Accepted Answer · 2018-02-28 05:26:19Z

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, 2018 at 5:26

EdSF

12.5k6 gold badges45 silver badges85 bronze badges

3 Comments

shmnff Over a year ago

do you mean that I have to collect all queries and then perform one connection to database?

EdSF Over a year ago

@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.

Nate Over a year ago

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?

Collectives™ on Stack Overflow

C# Using one SqlConnection for multiple queries

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related