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