lentinant lentinant - 1 year ago 64
C# Question

Get a rid of SqlConnection, which remains in Activity Monitor after being closed/disposed

I'm creating database, using ADO.NET. Basically, I'm executing SQL commands in next way:

private bool ExecuteSqlCommand(string command)
var success = true;

using (var connection = GetSqlConnection())
if (connection == null)
return false;

using (var myCommand = new SqlCommand("query", connection))
myCommand.CommandText = command;
catch (Exception ex)
success = false;
Log.LogMessage(string.Format("Unable to execute SQL command: {0}", ex.Message));

return success;

GetSqlConnection just gets some proper SqlConnection with connection string like

"Server={0}\\{1};User Id={2};Password={3};Application Name={4};"

It works well, and executes command properly, with one exception - according to Sql Manager Studio activity monitor, it remains as active connection even after method was executed.

According to question Why does my SqlConnection remain in the SSMS Processes list after closing it?, this is correct behavior, since connection might be reused. But the serius issue is, that later,
command will be called for this database, while using different SqlConnection. Which leads to exception, since
requires, that connection, which is used to call this command, should be the only connection to database.

I can't reuse this connection for further operations with database, since I use different connection string for them, with database specified as InitialCatalog (obviously, I can't use it, while database doesn't exist).

So, can I somehow remove this initial connection?

Answer Source

Execute SqlConnection.ClearPool This will mark all connections to be discarded instead of recycled.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download