When to use SqlConnection.ClearAllPools() in c#

Question

I've noticed that my code errors out on sqlWrite.ExecuteNonQuery(); after executing 200 Insert queries in couple of seconds. I always thought that using will make sure the resources are reused properly and there will be no need to do anything. This is the first time I get this error and I've been dealing with sql/c# for almost 3 years doing different things.

using (SqlConnection varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails)) 
{
    using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) 
    {
        sqlWrite.Parameters.AddWithValue("@var_agr_fname", var_agr_fname == "" ? (object) DBNull.Value : var_agr_fname);
        sqlWrite.ExecuteNonQuery();
    }
}


public static SqlConnection sqlConnectOneTime(string varSqlConnectionDetails)
{
    var sqlConnection = new SqlConnection(varSqlConnectionDetails);
    try
    {
        sqlConnection.Open();
    }
    catch
    {
        DialogResult result = MessageBox.Show(new Form {TopMost = true},
                                              "Błąd połączenia z bazą danych. Czy chcesz spróbować nawiązac połączenie ponownie?",
                                              "Błąd połączenia (000001)",
                                              MessageBoxButtons.YesNo,
                                              MessageBoxIcon.Stop);
        if (result == DialogResult.No)
        {
            if (Application.MessageLoop)
            {
                Application.Exit(); // Use this since we are a WinForms app
            }
            else
            {
                Environment.Exit(1); // Use this since we are a console app
            }
        }
        else
        {
            sqlConnection = sqlConnectOneTime(varSqlConnectionDetails);
        }
    }
    return sqlConnection;
}

Error message: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Considering advice for this error I should be using SqlConnection.ClearAllPools(); to make sure connections are reset or discarded properly. So I can use it but the question is where to use it and when? How to know if the limit is going to break? Where's the limit? at 50 / 150 / 200 ? or should I use it every single time in a loop?

Answer 1

First, let me say that this code is horrible. You're mixing UI with data connection creation. What's more, you show a dialog window inside a catch section and do a recursive call! This is very messy and in itself can lead to errors and unpredictable behaviour. And (original) formatting makes it hard to read. Sorry for the harsh comment but you really should redesign this code.

Apart from that your code should work fine but if you're getting No process is on the other end of the pipe. error that means there is something wrong with your database and/or SQL Server. It looks like it gets clogged up and just does not accept any more connections. If you run batch of inserts in a short time do them on one connection if possible. ClearAllPools is a way to recover when something wrong happens and it would be best to find out what it is instead of covering that up. It's like taking paracetamol when your tooth hurts and never going to a dentist.

One other thing is that using multiple SqlConnections create separate transaction for each connection. This adds load on SQL Server although it can surely do more than hundreds of transactions per sec.

Also, you can change transport to named pipe and TCP to see if it changes anything.