SqlConnection、连接池和工作单元的设计模式
很明显，SqlConnections 是池化的，因此 using 关键字看起来很完美，这是我一直使用和看到使用的方法。
例如...
public List<string> MyQuery()
{
List<string> list = new List<string>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// do some stuff with connection, process the resultset and fill the list.
}
return list;
}
问题是：对于一个网页的多个查询，每次都创建连接更好，还是共享查询之间的连接，为一个工作单元共享它更好？
性能会更好，还是只是过早的、不必要的微优化？
尽快关闭每个连接真的很重要，还是尝试将查询全部打包在一个工作单元中更好？
工作单元的一个示例可以是......
List<string> list1, list2;
string myvalue1, string myvalue2;
using (SqlConnection conn = new SqlConnection(connection))
{
list1 = MyQuery1(conn);
list2 = MyQuery2(conn);
myvalue1 = MyQuery3(conn);
myvalue2 = MyQuery4(conn);
}
这可能发生在页面加载期间，其中应该从数据库获取多个数据。
It is clear that SqlConnections are pooled, so the using keyword seems perfect and this is the approach I always used and seen used.
For example...
public List<string> MyQuery()
{
List<string> list = new List<string>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// do some stuff with connection, process the resultset and fill the list.
}
return list;
}
The question is : with several queries for a webpage, is better to create the connection each time or to share the connection between queries, sharing it for an unit of work?
The performances will be much greater or is just a premature unnecessary microoptimization?
Is really important to close every connection as soon as possible or is better to try to pack the queries all together in an unit of work?
An example of an unit of work could be for example...
List<string> list1, list2;
string myvalue1, string myvalue2;
using (SqlConnection conn = new SqlConnection(connection))
{
list1 = MyQuery1(conn);
list2 = MyQuery2(conn);
myvalue1 = MyQuery3(conn);
myvalue2 = MyQuery4(conn);
}
This can happens during page load where multiple data should be get from database.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
请注意，使用连接池，您实际上并没有为每个“新”连接创建新连接......您通常只是从池中取回一个连接。
因此，您应该始终尽可能晚地打开连接，并尽早关闭它们。
如果一组命令需要成为一个事务的一部分，那么它需要一起完成。但除此之外，我认为您应该总是在完成后关闭并购买一个新的。
即使您在这些命令之间没有运行其他代码...如果在两者之间发生线程上下文切换怎么办？您的连接对象不必要地打开，什么都不做，等待重新获得控制权。
如果你关闭它，它可能会回到游泳池。
Note that with connection pooling, you actually are not creating new connections with each 'new'... you are generally just getting one back from the pool.
Therefore, you should always open connections as late as you can, and close them as early as you can.
If a set of commands needs to be part of one transaction, then it needs to be done together. But beyond that, I think you should always close when you are done, and get a new one.
Even if you have no other code running between those commands... what if a thread context switch happens in between? Your connection object is needlessly open, doing nothing, waiting to get control back.
If you had closed it, it could have gone back to the pool.
在我看来，如果您已经有一些准备好执行的查询，那么使用单个连接来执行多个查询就可以了。
如果您在不久的将来有一些查询想要通过已打开的连接执行，则不建议保持连接打开。在这种情况下，建议尽快关闭连接，并为将来执行查询建立新连接
In my opinion if you already have a few queries which are ready to be executed then using a single connection for executing multiple queries is fine.
It is not recommended to keep the connection open if you will have some queries in near future which you want to execute through already open connection. In this case it is recommended to close the connection as soon as possible and for execution of future queries make a new connection
Microsoft 有一个很好的链接 http://msdn.microsoft.com/en-us/ library/ms971481.aspx ，“使用连接”部分。
建议在使用连接后始终关闭连接，以便将其返回到连接池。
There's good link from Microsoft http://msdn.microsoft.com/en-us/library/ms971481.aspx , "Using Connections" part.
Its recommended to always close a connection after its use in order for it to be returned to the connection pool.