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

If you are using ASP.NET with the same connection string you will be using a pooled connection that may never get physically closed, so you will pretty much always use an available open connection.

Answer 2

Short answer: Yes. This should be possible with most data providers.

Long answer: It depends on what you are using for your data access. However, you probably do not need to worry about it. Many data provider frameworks have connection pooling built in, so the subsequent connection creation/opening shouldn't "really" open a connection.

Answer 3

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

Answer 4

Sure, if you're using a SqlConnection object you can just do something like this:

connection.Open();
cmd.ExecuteReader(); // or any other form of getting the data
cmd2.ExecuteReader();
.
.
.
.
connection.Close();

I'd also like to add, if you're using a few SqlDataAdapters for your queries, although you normally don't need to open the connection by yourself, if you DO explicitly call connection.Open() it then won't close the connection for you automatically, allowing you to execute multiple queries with only one connection.

Answer 5

It's very possible. Assuming that you are talking about Connection and a DataReader. If you have to create a different connection every time, it sound like something is going wrong.

Without seeing any code, I am guessing that you are leaving the DataReader open. This is a BIG mistake. By default DataReaders completely consume the connection and leaving it unclosed can lead leaks. Close the DataReader, then execute another. I'd recommend wrapping the DataReader in a using block.

Rob

Answer 6

If you are using C# to open a connection. use using statement will help you clean up the resource/connection even if there is some excepion throwing out.

 using (SqlConnection connection =
                new SqlConnection(connectionString)
            {
                connection.Open();
                //issue command
            }

And read this:

http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx, you can "Controlling Connection Pooling with Connection String Keywords", and the system will handle pooling for you.