return 语句能否阻止 using 语句关闭与数据库的连接？
当我创建临时表时，我收到一条错误消息，告诉我临时表已存在。 临时表对于会话来说是唯一的，因此我的连接似乎没有正确关闭，我认为它可能与我在 using 语句中的 return 语句有关。
我有以下代码：
using (IDbConnection connection = dbConnectionHandler.CreateConnection())
{
connection.Open();
CreateATempTable();
PopulateTempTable();
DataSet ds = CallStoredProcThatUsesTempTable();
return ds;
}
我在多个地方使用此类代码来创建具有相同名称的临时表。
不幸的是，我收到以下错误：数据库中已存在一个名为“#MyTempTable”的对象。
现在，我知道临时表对于会话来说是唯一的，因此一旦会话关闭，它就应该消失。
我认为三件事可能会导致此问题...
- 我需要调用 connection.Close()
- 我需要将 return 语句放在 using 语句之外
- 我需要删除在返回之前创建的临时表
有谁知道是哪一个这是？ 或者如果这是我没有想到的？
When I'm creating temp tables I get an error message telling me that the temp table already exists. The temp table is unique to the session so it seems my connection isn't closing properly and I think it may have something to do with a return statement I have in my using statement.
I have the following code:
using (IDbConnection connection = dbConnectionHandler.CreateConnection())
{
connection.Open();
CreateATempTable();
PopulateTempTable();
DataSet ds = CallStoredProcThatUsesTempTable();
return ds;
}
I use this sort of code in several places to create a temp table with the same name.
Unfortunately, I'm getting the following error: There is already an object named '#MyTempTable' in the database.
Now, I know that the temp table is unique to the session and so once the session is closed it should disappear.
There are three things that I believe might cause this...
- I need to call connection.Close()
- I need to place the return statement outside my using statement
- I need to drop the temp table I created prior to returning
Does anyone know which one it is? or if its something I haven't thought of?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
我在这里猜测，但请检查您的数据库连接池设置。 尝试关闭池化，看看是否有帮助。
通常，当您在 .NET 库级别关闭/处置连接时，真正的数据库服务器连接不会关闭。 它只是返回到数据提供者内部的连接池，并且当程序请求具有相同参数和凭据的另一个连接时将被重用。 我认为数据库会话在返回到池之前不会以任何方式重置，除了开放事务和一些基本参数之外。 更昂贵的对象（例如临时表）将被保留。
您可以关闭池化（非常低效）。 或者，您可以在尝试创建临时表之前检查其是否存在，并删除其内容（如果存在）。 或者您可以在关闭连接之前删除临时表。
I'm guessing here but check your database connection pooling settings. Try turning pooling off and see if it helps.
Usually, when you close/dispose connection on the .NET libraries level, real database server connection is not closed. It is just returned to connection pool inside data provider and will be reused when program asks for another connection with the same parameters and credentials. I don't think database session is reset in any way before being returned to the pool, except for open transactions and maybe some basic parameters. More expensive objects, like temporary tables, are left alone.
You can turn pooling off (very inefficient). Or you can check temporary table existence before trying to create it and delete its content if it exists. Or you can drop temporary table before closing connection.
using 块在底层被转换为 try/catch/finally 块。 是的，无论 using 块中的返回如何，它都会进行处理。
A using block is translated into a try/catch/finally block under the hood. Yes it will dispose regardless of the return within the using block.
我非常确定将调用connection.Dispose()（以及connection.Close()）。
您可以通过执行 1) 和 2) 并检查问题是否仍然存在来轻松验证这一点。 解决方案可能是 3)，解释是连接池。
I am pretty sure that connection.Dispose() (and thus connection.Close() as well) will be called.
You can verify that easily enough by doing 1) and 2) and checking that the problem still exists. The solution is probably 3) and the explanation would be Connection pooling.
除非发生电源循环或其他一些严重奇怪的极端情况，否则将调用处置。
如果你想证明包装对象并放置一个断点。
Unless a power cycle occurs or some other seriously bizarre corner case dispose WILL be called.
If you want proof wrap the object and put a breakpoint in.
不，connection.Close 将始终被调用，因为在内部 using 将其放入 try/finally 块中。
您可能还需要考虑连接池。 尝试将代码包装在 TransactionScope 中。
No, connection.Close will always be called because internally using puts it in a try/finally block.
You also may want to consider connection pooling. Try wrapping your code in a TransactionScope.
回答您的问题：
To answer your questions:
如果对象的类是 IDisposable，则 using 语句将处理该对象，即使 using 块中有 return 语句。
连接池保留了 #temptable，您可能需要手动删除该表。
using statement will dispose the object if its class is IDisposable even there is a return statement within the using block.
It is the connection pooling that keeps your #temptable, you might want to drop that table manually.
如果不了解更多有关正在使用的数据库连接库的信息，我猜它不是前两个；
using是专门引入的，以便在方法返回时更轻松地清理此类资源； 它直接类似于 Java 中的普通try...finally块或类似块。换句话说，
return将离开该块，并且将在连接上调用Dispose方法，假设有一个合理的实现，该方法应该调用Close方法作为该过程的一部分。这里的关键点是“合理的实施”。
Without knowing more about the database connection library in use, I'd guess it's neither of the first two;
usingwas introduced specifically to make cleaning up such resources easier when returning out of methods; it's directly analogous to an ordinarytry...finallyblock in Java or similar.In other words, the
returnwill leave the block and theDisposemethod will be called on the connection, which should, assuming a sane implementation of such, call theClosemethod as part of that process.The key point here is "sane implementation".
这是由连接池引起的。 将您正在执行的操作包装在事务中，并在最后回滚。 或者，在填充 ds 后删除临时表。
It's being caused by connection pooling. Wrap what you are doing in a transaction, and roll it back at the end. Or, drop the temp table after populating the ds.