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"

Answer 1

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

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