为什么在某些情况下我们必须关闭并重新打开连接? [英] Why do we have to close and re-open the connection in some cases?

问题描述

我正在使用SqlCommand，SqlConnection，SqlDataAdapter，SqlDataReader类，在某些情况下，必须先关闭该连接，然后再重新打开它，然后才能对该连接执行一些代码.我以为，一旦打开连接，便可以在需要的任何时候将其用于该连接上的任何操作，并且只有当用户关闭应用程序时，才应关闭该连接.
您能否进一步向我解释一下，还是请我参考一些有关此问题的外部有用文章?
非常感谢！

I am using the classe SqlCommand, SqlConnection, SqlDataAdapter, SqlDataReader, and in some cases, I have to close the connection first and re-open it before executing some code with the connection. I thought that once we open a connection, we can use it for any operations on that connection at any time we want, and the only time the connection should be closed is when the user closes the application.
Could you explain more to me or refer me to some external helpful article about this problem?
Thank you very much!

推荐答案

当您调用Open()和Close()时，SqlConnection有一个连接池，您实际上并没有打开和关闭物理连接.到服务器.您只是从可用连接池中添加/删除连接.因此，最好在执行命令后尽可能晚地打开连接并尽早关闭连接.
主要是程序员相信迟到早开.仅当每次打开和关闭连接的延迟导致整个应用程序变慢时，这才是问题.
参考链接:- Link1- [我何时应打开和关闭与SQL Server的连接] [ MSDN- [SQL Server连接池(ADO.NET)] [^ ]
MSDN- [SqlConnection.Open方法] [ ^ ]
来自参考:-如果SqlConnection超出范围，则不会关闭它.因此，您必须通过调用Close显式关闭连接.

The SqlConnection has a connection pool when you call Open() and Close() you aren''t actually opening and closing the physical connection to the server. You are just adding / removing the connection from a pool of available connections. For this reason it is a good and best practice to open the connection as late as possible and close the connection as early as possible after executing your command.
Mainly programmers believe in open late and close early. This is only a problem if the latency for opening and closing the connection each time causes the entire application to slow down.
Reference Link :- Link1-[ When should I open and close a connection to SQL Server ][^]

Further Reference :- MSDN-[ SQL Server Connection Pooling (ADO.NET) ][^]
MSDN-[SqlConnection.Open Method][^]
from Reference :- If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close.

通常，打开和关闭连接是我在需要时要做的事情.因此，想象一下数据层中的一种方法，如下所示:

Generally, opening and closing connections is something I would do on a need basis. So imagine a method in the data layer like this:

private List<employee> GetEmployees()
{
   //create any local variables and non-db related code
   OpenSQLConnection();
   //populate my data list
   CloseConnection(); // close it as soon as i m done with db related work
   //return the list and do any other non-db bits
}</employee>

因此，建议尽可能晚地打开连接并尽快关闭它，不建议保持持久连接打开，尤其是在Web应用程序迟早出现的情况下，服务器将达到连接数量的最大容量，并开始在某一点拒绝连接.因此，快速打开和关闭连接将意味着池中存在稳定的连接可用性.

人们还喜欢在try块中执行try..catch().. finally {}并关闭连接，只有在我使用完try块中没有其他与数据库无关的代码后，我才会这样做.联系.因为否则，您将不必要地且无意识地保持连接打开，直到您击中了finally块.就我的看法.

干杯.

So open connections as late as possible and close it ASAP, keeping persistent connections open is not advised especially in case of web apps coz sooner or later the server will hit the max capacity of number of connections and will start rejecting connections at one point. So rapidly opening and closing connections would mean there is a steady availability of connections in the pool.

People also prefer to do a try..catch()..finally{} block and close connections in the finally block, I would do that only if there is no other non-db related code in my try block after i have used the connection. Because otherwise you would be unnecessarily and unwittingly keeping the connection open until you hit the finally block. Just my views.

Cheers.

应该将其发布在解决方案部分:如果您在两者之间调用某些必须与数据库连接配合使用的代码，则可能会出现异常.我记得做过这样的事情，我当时是从一个函数内部调用一个函数，两者都需要访问数据库，但是在完成连接后，被调用函数关闭了连接，由于连接具有连接，这导致了调用函数中的某些异常已关闭.我通过创建另一个函数将连接重置为有效状态来修复它.那是几年前的事，可能不是最佳实践，因为您实际上不必在数据层中调用任何其他与数据库相关的功能.因此，是的，这种方法也有优缺点，但是这种方法的十分之九十分有效.因此，只需确保未通过其他代码设置/重置连接会导致异常，请尝试调试代码以查看失败的确切位置.将连接状态保留在Watch上，然后继续进行操作...

通常，已经打开的DataReader可能是罪魁祸首.

希望对您有帮助

thought to post it in the solution section: if you are calling some code in between that has to work with db connections, then yes there is a possibility of an exception. I remember doing something like that, where I was calling a function from within a function, and both needed to access the database but the called function after it was done closed the connection which resulted in some exception in the calling function due to the connection having been closed. I fixed it by creating another function to reset the connection to a valid state. It was years ago and wasn''t probably the best practice coz you shouldn''t really have to call any other db related function within the data layer. So, yes there are pros and cons of this approach as well, but 9 out of 10 times this approach works well. So just make sure the connection is not being set/reset by some other code which results in exception, try debugging your code to see where exactly it fails. Keep the connection state on Watch and give it a go...

Mostly an already open DataReader can be the culprit.

hope this helps

这篇关于为什么在某些情况下我们必须关闭并重新打开连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！