How can I keep a Connection open when performing multiple queries?

Question

I am using multiple queries to pull data from the same server in my application. The issue is that I have to open a new connection every time I have a new query.

Is it even possible to:

• Open the connection
• Run query
• Pull results
• Run another query
• Pull another result
• Run final query
• Pull another result
• Close connection.

Answer 1

Although you may not yet know it, you are doing it correctly.

Open the connection, do your query, close it. Preferably using a using block or try/finally.

This may sound like a lot of overhead, but the connection pool in the .NET Framework Data Provider for SQL Server will actually optimize this for you.

In fact closing the connection is recommended. Here is a quote from the documentation:

It is recommended that you always close the Connection when you are finished using it in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Here is an example of some code that does this:

try {
    conn.Open();
    // Perform query here
} finally {
    conn.Close();
}

For reference:

http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx