|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Close not closing...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 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 Show quote "theinvisibleGhost" <theinvisibleGh***@yahoo.com> wrote in message news:1170091502.205177.158340@p10g2000cwp.googlegroups.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 > > The conneciton pooling that works by default with asp.net keeps the I don't know about that. The connection object is placed back in the pool > conneciton open for a while in case you will need it again. 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. "Scott M." <s-mar@nospam.nospam> wrote in message I think you may be mistaken here. Close a connection and pull the network news:eeTQUV$QHHA.4832@TK2MSFTNGP04.phx.gbl... >> 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. 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 Show quote > > 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? Show quote "W.G. Ryan [MVP]" <WilliamRyan@nospam.gmail.com> wrote in message news:uv39OvLRHHA.4384@TK2MSFTNGP04.phx.gbl... > > "Scott M." <s-mar@nospam.nospam> wrote in message > news:eeTQUV$QHHA.4832@TK2MSFTNGP04.phx.gbl... >>> 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 > >> >> > > 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 -- Show quote____________________________________ 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." <s-mar@nospam.nospam> wrote in message news:%23RBXBPMRHHA.2256@TK2MSFTNGP02.phx.gbl... > 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]" <WilliamRyan@nospam.gmail.com> wrote in message > news:uv39OvLRHHA.4384@TK2MSFTNGP04.phx.gbl... >> >> "Scott M." <s-mar@nospam.nospam> wrote in message >> news:eeTQUV$QHHA.4832@TK2MSFTNGP04.phx.gbl... >>>> 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 >> >>> >>> >> >> > > 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(); } } Show quote "theinvisibleGhost" <theinvisibleGh***@yahoo.com> wrote in message news:1170091502.205177.158340@p10g2000cwp.googlegroups.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 > Thanks Shkedy, that makes more sense I'll try that.
And good point Scott M, some lazy coding on my part there ;-) Cheers Chris.  |
|||||||||||||||||||||||
Other interesting topics