How to Close SQL Database Connection
-
I'm Using Visual Studio 2008 C# Windows Forms Application. My database connections are made using DataTableAdapter and everything works from a data access point of view. The connection is opened as soon as I do the TableAdapter.Fill The trouble is the database connection stays open for as long as the application runs. As soon as I close the application the connection is closed.
try
{
this.ownersCorporationStreetTableAdapter.FillOwnersCorporationStreet(this.strataDataSet.OwnersCorporationStreet);
}
catch (SystemException ex)
{
if (LocalUtilities.Debug > 0) LocalUtilities.writeLogFile(2, formName, LocalUtilities.getCurrentMethod() + "() Error:" + ex);
MessageBox.Show("Could not read from the Database.\n Error:" + ex, "Error");
}
finally
{
this.ownersCorporationStreetTableAdapter.Connection.Close();
}I have tried the finally clause but this doen't close the connection. I watched the connections is SQL Management studio and it sets their for over an hour and closes as soon as I exit the application. My connection string is in the application settings and beyond that the magic is all hidden in the automatically generated code. Any ideas How I can close the connections when they are no longer required when using Table Adapters? Background Normally this is not a problem but my application accesses more than one database. The primary database is not problem. The secondary connection is to a database that belongs to another application and I'm accessing it for reads only. The other third party application counts it license usage by the number of database connections. So my application counts as a license in use for the third part application for as long as my program is open. The other company has no problem with me reading the data and I do not require a license for that.
-
I'm Using Visual Studio 2008 C# Windows Forms Application. My database connections are made using DataTableAdapter and everything works from a data access point of view. The connection is opened as soon as I do the TableAdapter.Fill The trouble is the database connection stays open for as long as the application runs. As soon as I close the application the connection is closed.
try
{
this.ownersCorporationStreetTableAdapter.FillOwnersCorporationStreet(this.strataDataSet.OwnersCorporationStreet);
}
catch (SystemException ex)
{
if (LocalUtilities.Debug > 0) LocalUtilities.writeLogFile(2, formName, LocalUtilities.getCurrentMethod() + "() Error:" + ex);
MessageBox.Show("Could not read from the Database.\n Error:" + ex, "Error");
}
finally
{
this.ownersCorporationStreetTableAdapter.Connection.Close();
}I have tried the finally clause but this doen't close the connection. I watched the connections is SQL Management studio and it sets their for over an hour and closes as soon as I exit the application. My connection string is in the application settings and beyond that the magic is all hidden in the automatically generated code. Any ideas How I can close the connections when they are no longer required when using Table Adapters? Background Normally this is not a problem but my application accesses more than one database. The primary database is not problem. The secondary connection is to a database that belongs to another application and I'm accessing it for reads only. The other third party application counts it license usage by the number of database connections. So my application counts as a license in use for the third part application for as long as my program is open. The other company has no problem with me reading the data and I do not require a license for that.
If the adapter opens the connection, it should automatically close it for you as well. I suspect the connection is being "closed", but is kept alive by connection pooling: Connection Pooling - ADO.NET | Microsoft Docs[^] When you open another connection to the same database, it should reuse the pooled connection rather than opening a new connection. If you want to disable connection pooling for a connection, add
Pooling=false;to the connection string.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
