Close not closing...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

theinvisibleGhost - 29 Jan 2007 17:25 GMT

I've written a small app which has 2 buttons.

The 1st button goes away to a directory full of SQL Scripts, and then
runs them against the database.

The 2nd button has code to do some modifications to the database,
looking like the code below:
_______________________________________________________________________
_________
string connectionString;
connectionString =
Properties.Settings.Default.DatabaseConnectionString + databaseName;

// create a new SqlConnection object with the appropriate
connection string
SqlConnection sqlConn = new
SqlConnection(connectionString);
try
{
// open the connection
sqlConn.Open();

// create the command object
SqlCommand sqlComm = new SqlCommand(SQLCommands,
sqlConn);
sqlComm.ExecuteNonQuery();

// close the connection
sqlConn.Close();
MessageBox.Show("Operation Completed");
}
_______________________________________________________________________
_________

One of the scripts run from the script folder Drops a user, and then
Re-Creates them.
If I hit the script button first, then everything works fine.
However if I run the code button first, and then hit the script
button, an exception is thrown
saying that the user can not be dropped since they are currently
logged in!
I'm guessing I'm missing something, but it appears the Close method on
the sqlConnection object isn't
actually closing the connection...

Terminating the application and starting again resets the problem,
however there is nothing in
the dispose methods which would do that.

I've tried exchanging Close for Dispose but it made no difference.
Any help would be appreciated.
Cheers
Chris.
saying that a user is

Reply to this Message

Shkedy - 29 Jan 2007 18:40 GMT

The conneciton pooling that works by default with asp.net keeps the
conneciton open for a while in case you will need it again.
When you run close the connection goes back to the pool of available
connections.

Seems like You need to disable the connection polling for the application
add Pooling=false to the connection string.
Sagi Shkedy
http://blog.shkedy.com

> I've written a small app which has 2 buttons.
>
[quoted text clipped - 50 lines]
> Chris.
> saying that a user is

Reply to this Message

Scott M. - 29 Jan 2007 22:32 GMT

> The conneciton pooling that works by default with asp.net keeps the
> conneciton open for a while in case you will need it again.

I don't know about that. The connection object is placed back in the pool
when you close it, but it should be closed. If the connection was kept open
for indeterminate amount of time, that would defeat the entire purpose of
having a close method for your connection in the first place.

Reply to this Message

W.G. Ryan [MVP] - 30 Jan 2007 22:13 GMT

>> The conneciton pooling that works by default with asp.net keeps the
>> conneciton open for a while in case you will need it again.
[quoted text clipped - 3 lines]
> open for indeterminate amount of time, that would defeat the entire
> purpose of having a close method for your connection in the first place.

I think you may be mistaken here. Close a connection and pull the network
cable on your computer. Then plug it back in and try to reuse it. If
pooling is on, when you call close, it's returned to the pool but the
physical connection is held on to until the pool timeout is reached. So
if/when another attempt is made to open a connection w/ the same string,
that connection is used. This is where the efficiency actually comes from

Reply to this Message

Scott M. - 30 Jan 2007 23:10 GMT

But do we need to distinguish between the physical connection and that
physical connection's state?

If you call close, the physical connection's state becomes closed. Now,
sure you can call open and use it again or the same connection object can be
recycling via pooling, but we're talking about the connection's state here,
not its existance, right?

>>> The conneciton pooling that works by default with asp.net keeps the
>>> conneciton open for a while in case you will need it again.
[quoted text clipped - 10 lines]
> if/when another attempt is made to open a connection w/ the same string,
> that connection is used. This is where the efficiency actually comes from

Reply to this Message

William (Bill) Vaughn - 31 Jan 2007 00:03 GMT

Ah, no.
When you have the connection pool enabled, when you call Connection.Close,
the physical connection is left open, the Connection.State changes to Closed
and the connection is released to the pool for other code in the same
AppDomain to reuse. The pooling mechanism is fairly complex in some respects
and the 2.0 implementation is very different from the 1.0 or 1.1. For
example, in the 1.1 implementation, if the server goes down or the
connection is closed from the SQL Server end (as when a procedure has a
severe error), your application is notified but the connection remains in
the pool until some other use is attempted. At this point the connection is
dropped. However, since all other connections that use this connection
string might also be bad (as when the server goes down), the application
continues to get errors until all pooled connections are touched.

In the 2.0 version of ADO.NET, when the server goes down, the entire pool is
flushed. You can also clear the pool on demand.

See Chapter 9 for more information.

hth

Signature

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

> But do we need to distinguish between the physical connection and that
> physical connection's state?
[quoted text clipped - 18 lines]
>> if/when another attempt is made to open a connection w/ the same string,
>> that connection is used. This is where the efficiency actually comes from

Reply to this Message

Scott M. - 29 Jan 2007 19:39 GMT

Put your close code inside a finally and add a catch. If you encounter an
exception prior to your close code, that code will not run

string connectionString;
connectionString = Properties.Settings.Default.DatabaseConnectionString +
databaseName;

// create a new SqlConnection object with the appropriate
connection string SqlConnection sqlConn = new
SqlConnection(connectionString);

using sqlConn
{
try
{
// open the connection
sqlConn.Open();

// create the command object
SqlCommand sqlComm = new SqlCommand(SQLCommands, sqlConn);
sqlComm.ExecuteNonQuery();

MessageBox.Show("Operation Completed");
}
catch
{
// handle exceptions here
}
finally
{
// close the connection
sqlConn.Close();
}
}

> I've written a small app which has 2 buttons.
>
[quoted text clipped - 50 lines]
> Chris.
> saying that a user is

Reply to this Message

theinvisibleGhost - 29 Jan 2007 21:14 GMT

Thanks Shkedy, that makes more sense I'll try that.
And good point Scott M, some lazy coding on my part there ;-)
Cheers
Chris.

Reply to this Message

Close not closing...

Free Magazines