4609

How do you completely close an SQLConnection string in vb.net?

Question:

I'm running a multithreaded application that connects to a database, the connections are closed when I'm done with them (and I dispose of all the threads). I've tried clearing the connection pools, setting pooling=false and I've used both .dispose and .close.

I get the error "cannot drop the database X because it is currently in use" if I try to drop the database I connected to after the connections have all been closed. Below is my code:

Dim comExecuteInsert As New SqlCommand Dim comm As New SqlConnection If (Not comm Is Nothing) Then comm = Nothing End If comExecuteInsert.Connection = comm comExecuteInsert.CommandType = CommandType.StoredProcedure comExecuteInsert.CommandText = strProcedureName comExecuteInsert.CommandTimeout = 26000 comExecuteInsert.Parameters.Add("@tableName", SqlDbType.VarChar, 100).Value = strTableName comExecuteInsert.Parameters.Add("@filename", SqlDbType.VarChar, 500).Value = strFileName comExecuteInsert.ExecuteScalar() comExecuteInsert.Parameters.Clear() comExecuteInsert = Nothing comm.Close() SqlConnection.ClearPool(comm)

The stored procedure creates temporary tables which it later drops and inserts data into tables existing in the database.

Answer1:

You are not disposing your command object.

Instead of

comExecuteInsert = Nothing

try

comExecuteInsert.Dispose

Or try the following:

Using comm As New SqlConnection Using comExecuteInsert As New SqlCommand comExecuteInsert.Connection = comm comExecuteInsert.CommandType = CommandType.StoredProcedure comExecuteInsert.CommandText = strProcedureName comExecuteInsert.CommandTimeout = 26000 comExecuteInsert.Parameters.Add("@tableName", SqlDbType.VarChar, 100).Value = strTableName comExecuteInsert.Parameters.Add("@filename", SqlDbType.VarChar, 500).Value = strFileName comExecuteInsert.ExecuteScalar() comExecuteInsert.Parameters.Clear() comm.Close() End Using End Using

Answer2:

If you are trying to close any connection to a db so you can drop it, you can do execute something like the following against the master database in Management Studio:

Alter Database MyDatabaseName Set Single_User With Rollback Immediate GO Drop Database MyDatabaseName GO

To do this in code, you would need to open a separate connection to the master database and execute each of the above statements individually (and not try to execute the GO words). Keep in mind that this will kill <em>all</em> connection to the database including ones other than your own no matter what they might be doing.

Answer3:

I missed the bit where you said you were dropping a database. You need to use

SqlConnection.ClearAllPools()

SQL Server caches connections for re-use. The above clears the cached connections.

For example...

Sub Demo_DropDatabase(ByVal strCnn As String, ByVal strDBName As String) Using cnnSQLS As New SqlConnection(strCnn) SqlConnection.ClearAllPools() cnnSQLS.Open() Dim strSQL As String = "DROP DATABASE [" & strDBName & "]" Using cmdDrop As New SqlCommand(strSQL, cnnSQLS) cmdDrop.ExecuteNonQuery() 'N.B. may throw exception ' End Using cnnSQLS.Close() End Using End Sub

Answer4:

use the using keyword, see <a href="https://stackoverflow.com/questions/376068/does-end-using-close-an-open-sql-connection" rel="nofollow">Does End Using close an open SQL Connection</a>

Recommend

  • Adding parameters to the WHERE clause of a SQL query
  • How do you completely close an SQLConnection string in vb.net?
  • Can't Insert or update values to dynamically created textbox into SQL Server database Windows F
  • Using Parameters.AddWithValue to pass database name fails
  • Retrieving a ConnectionString through System.Configuration Throws Exception
  • How to put a concatenated data from database to a label
  • Getting Result From Select Command SQL Server
  • Read UTF8(XML) Data from SQL Server 2005 the most efficient way
  • C# Console App, slow to exit
  • Unit Test for n tier architecture
  • Call SQL Function using ADO .NET
  • ExecuteScalar SQLException: scalar variable not declared
  • Looking for the best way to use ExecuteScalar()
  • MySQL select result save into c# variable
  • How to block a user after a specified failed attempts and unblocking the user after 5 minutes
  • What happens to Unicode in a System.Data.SQLCommand
  • Conditional operator in C# and return types [duplicate]
  • What does the maxJsonLength property refer to?
  • “Error converting data type nvarchar to int” when executing Stored Procedure and reading return valu
  • Datatable class in asp.net core
  • Linq Boolean returns exception DROPDOWNLIST has a SelectedValue which is invalid because it does not
  • Length of Greek character string is larger than it should be
  • sql query to select record having same id but different value in two columns
  • Retrieve all Indexes for a given Table with JDBC
  • SQL Query Clarification Required [closed]
  • Delete suddenly taking a long time
  • DELETE statement issues within a trigger definition
  • Get Quarters StartDate and EndDate from Year
  • Memory usage of storing strings as varchar in MySQL
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • How to find data from last week in MySQL
  • How do I write an item to a DynamoDb with the AWS DynamoDB DocumentClient?
  • Serverless Framework Dynamo DB Table Resource Definition with Sort Key
  • Does for loop open and close a database connection on each iteration?
  • Identifier too long in Oracle
  • Eliminate partial duplicate rows from result set