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.

Answer 2

Those 2 errors:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

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.)

Were related to DateTime value being inserted into SQL with Date before 1900 year. The Microsoft rule here is .. don't store DateTime value less then 1900 year in DateTime value in SQL. Use string instead...

Answer 3

"I have a method that I reuse by using it either once or more times. It does it all from establishing a connection (or using the one that's already connected) to returning the connection to the pool. Those are advices I got. This way I always reuse the connection if it's open and if it's closed I always open it up."

This sounds like you have reinvented the Connection-Pool. It always reuses connections if they are closed and cannot reuse them if they are open.

So close the connection in the catch block:

public static SqlConnection sqlConnectOneTime(string varSqlConnectionDetails) {
    var sqlConnection = new SqlConnection(varSqlConnectionDetails);
    try {
        sqlConnection.Open();
    } catch {
        //log and
        sqlConnection.Close();
        throw
    }
    return sqlConnection;
}

Edit: To be honest i wouldn't use such factory methods at all. They are just a source for unreproducable errors. What's so time consuming in creating and opening the connection where you're using it?

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

The first two links are from your linked question(not the accepted answer), they might also be helpful:

• http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/9609559d-f7ce-4bd8-97d0-0003ff7c9c98/
• http://blogs.msdn.com/b/spike/archive/2009/04/16/a-transport-level-error-has-occurred-when-sending-the-request-to-the-server-provider-tcp-provider-error-0-an-existing-connection-was-forcibly-closed-by-the-remote-host.aspx
• ExecuteReader requires an open and available Connection. The connection's current state is Connecting (not the same subject but apparently a similar background)