如何在执行多个查询时保持Connection处于打开状态？

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.

6 个解决方案

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

这可能听起来像很多开销,但SQL Server的.NET Framework数据提供程序中的连接池实际上会为您优化这一点。

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.

建议您在使用完毕后始终关闭Connection,以便将连接返回到池中。这可以使用Connection对象的Close或Dispose方法完成。未显式关闭的连接可能不会添加或返回到池中。例如,如果已达到最大池大小且连接仍然有效,则超出范围但尚未显式关闭的连接将仅返回到连接池。

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

#2

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.

如果您使用具有相同连接字符串的ASP.NET,则您将使用可能永远不会物理关闭的池化连接,因此您几乎总是使用可用的打开连接。

#3

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.

这很有可能。假设您正在谈论Connection和DataReader。如果你每次都要创建一个不同的连接,那听起来好像出了问题。

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.

没有看到任何代码,我猜你正在打开DataReader。这是一个大错误。默认情况下,DataReaders完全使用连接并使其未关闭可能导致泄漏。关闭DataReader,然后执行另一个。我建议将DataReader包装在一个使用块中。

Rob

#4

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.

答案很长:这取决于您用于数据访问的内容。但是,您可能不需要担心它。许多数据提供程序框架都内置了连接池,因此后续连接创建/打开不应该“真正”打开连接。

#5

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

当然,如果您使用的是SqlConnection对象,您可以执行以下操作:

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.

我还想补充一点,如果你使用一些SqlDataAdapters进行查询,虽然你通常不需要自己打开连接,如果你明确调用connection.Open()它就不会关闭自动连接,允许您只使用一个连接执行多个查询。

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

如果您使用C#打开连接。使用using语句将帮助您清理资源/连接,即使有一些异常抛出。

 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.

http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx,您可以“使用连接字符串关键字控制连接池”,系统将为您处理池。

#1