lentinant lentinant - 14 days ago 6
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))
{
try
{
connection.Open();
myCommand.CommandText = command;
myCommand.ExecuteNonQuery();
}
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,
READ_COMMITTED_SNAPSHOT
command will be called for this database, while using different SqlConnection. Which leads to exception, since
READ_COMMITTED_SNAPSHOT
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

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