Close not closing...

ADO >> Close not closing... 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

DotNet57

ADO >> Close not closing... 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

"theinvisibleGhost" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
> 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
>

ADO >> Close not closing... 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();
}
}

"theinvisibleGhost" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
> 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
>

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

ADO >> Close not closing... > 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.

ADO >> Close not closing...
"Scott M." <EMail@HideDomain.com> wrote in message
news:eeTQUV$EMail@HideDomain.com...
>> 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.

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

>
>

ADO >> Close not closing... 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?

"W.G. Ryan [MVP]" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
>
> "Scott M." <EMail@HideDomain.com> wrote in message
> news:eeTQUV$EMail@HideDomain.com...
>>> 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.
>
> 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
>
>>
>>
>
>

ADO >> Close not closing... 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
--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

"Scott M." <EMail@HideDomain.com> wrote in message
news:%EMail@HideDomain.com...
> 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?
>
> "W.G. Ryan [MVP]" <EMail@HideDomain.com> wrote in message
> news:EMail@HideDomain.com...
>>
>> "Scott M." <EMail@HideDomain.com> wrote in message
>> news:eeTQUV$EMail@HideDomain.com...
>>>> 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.
>>
>> 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
>>
>>>
>>>
>>
>>
>
>