Geek Answers Handbook

Can a return statement prohibit the use of a close database connection statement?

When I create temporary tables, I get an error message telling me that the temp table already exists. The temporary table is unique to the session, so it seems that my connection is not closing properly, and I think this may have something to do with the return statement that 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 kind of code in several places to create a temporary table with the same name.

Unfortunately, I get the following error: There is already an object named '#MyTempTable' in the database .

Now I know that the temporary table is unique to the session, so as soon as the session is closed, it should disappear.

There are three things that I believe can cause this ...

  • I need to call connection.Close ()
  • I need to put a return statement outside my using statement
  • I need to drop the temporary table that I created before returning

Does anyone know who he is? or if this is something that I did not think about?

+4
source share
9 answers

I assume that here, but check your database connection pool settings. Try disabling the pool and see if that helps.

Usually, when you close / delete a connection at the .NET library level, the real connection to the database server does not close. It simply returns to the connection pool within the data provider and will be reused when the program requests another connection with the same parameters and credentials. I do not think that the database session is reset in any way before returning to the pool, with the exception of open transactions and possibly basic parameters. More expensive objects, such as temporary tables, remain valid.

You can disable merging (very inefficient). Or you can check the existence of a temporary table before trying to create it, and delete its contents if it exists. Or you can delete the temporary table before closing the connection.

+6
source

I am sure that connection.Dispose () will be called (and therefore connection.Close ()).

You can verify this fairly easily by doing 1) and 2) and checking that the problem still exists. The solution is probably 3), and the explanation will be the pool of connections.

+3
source

Unless a force cycle has occurred or any other serious bizarre corner case will not be called WILL.

If you want evidence to wrap the object and set a breakpoint.

+1
source

A block using a block is translated into a try / catch / finally block under the hood. Yes, it will be located regardless of the return inside the used block.

+1
source

No, connection.Close is always called because internal use puts it in a try / finally block.

You can also consider pooling. Try wrapping your code in TransactionScope.

+1
source

Without knowing more about the database connection library used, I would not assume that this is not the first of two; using was introduced specifically to facilitate cleaning up such resources when returning from methods; it is directly similar to regular try...finally blocks in Java or similar.

In other words, return will leave the block, and the Dispose method will be called on the connection, which should, assuming a reasonable implementation of this, call the Close method as part of this process.

The key point here is the “right implementation”.

0
source

To answer your questions:

0
source
Operator

using the instruction will position the object if its class is IDisposable, even the operator uses the return statement.

This is a connection pool that #temptable does not allow; you can delete this table manually.

0
source

This is caused by the connection pool. Wrap what you do in the transaction and return it at the end. Or, change the temporary table after filling in ds.

0
source

More articles:


All Articles