Can a return statement prevent a using statement from closing a connection to a database?

Question

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

1. I need to call connection.Close()
2. I need to place the return statement outside my using statement
3. 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?

Answer 1

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.

Answer 2

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.