SQL Server 2008 in C#: Connections and Command Objects

Question

There's a lot of non-detailed questions on this one, so here goes.

What is the best practice for connection handling in C# with SQL Server 2008? We have an assembly (which in our case is used by a WCF Service) that makes calls to an SQL Server. In general it seems like you need three objects to do this: The connection object, the command object, and the reader object.

The only reliable way we've been able to get the calls to work is to do the following:

1. Open the connection.
2. Create the Command in a using() { } block
3. Create the Reader to handle the response.
4. Dispose of the reader.
5. Implicitly dispose of the Command at the end of the using() block
6. Close the connection.

We ran into an unusual problem when running the same command multiple times iteratively, where it would complain that there was already a command or reader object attached to the connection that was still open. The only rock solid solution was to close and reopen the connection with every command we did, iterative or just sequential (different commands.)

So this is the question, since I come from a mysql_pconnect background on DB connection handling.

1. Is it going to significantly impact performance to be opening and closing a connection for each command?
2. If so for 1., what is the proper workaround, or code structure to handle serially repeating a command?
3. Is there any way to reuse a connection, command or reader at all?
4. If not for 3., does this really impact performance or memory usage significantly (As in, our users would notice.)

Answer 1

To answer point 1, if you look at the documentation for SqlConnection you'll see it explain about connection pooling. This means that the SQL Server provider has a collection of connections readily available and each SqlConnection created simply gets the next available connection. Therefore, to get the best performance, it is advisable to keep creating SqlConnection objects and using them for short operations and then disposing of them, thereby returning back to the connection pool.

For point 3, I believe you can re-use an SqlConnection if you do SqlCommand.ExecuteNonQuery(), but if you use an SqlDataReader you cannot re-use the connection - it is tied to the SqlDataReader and must be closed/disposed of once finished.

Answer 2

In addition to @PeterMonks answer:

1. The "expensive", unmanaged part of the SqlConnection is re-used by the provider (connection pooling) as long as you use the same connection string. So while there is a small overhead to creating a new managed wrapper each time, it isn't actually a 1:1 relationship with creating connections to the SQL server instance, so it isnt as expensive as you might think.

2. To serially repeat a command that returns a data reader, you must a) always execute the command on the same thread (commands are not thread safe) and b) Close() or Dispose() the DataReader instances before creating the next one. You can do that by putting the DataReaders in a using block as well.

Here is how you put the reader into a using block:

using (var dr = myCommand.ExecuteReader(...)) {
    // Previous discussions have indicated that a close in here, 
    // while seemingly redundant, can possibly help with the error 
    // you are seeing.
    dr.Close();
}

Another useful technique, as @DavidStratton mentions, is to enable MARS, but be aware that there is overhead associated with keeping resultsets open- you still want to close your readers as soon as you are done with them, because unclosed, undisposed readers do represent significant resource allocations on the server and the client.