SqlConnection - The timeout period elapsed prior to obtaining a connection from the pool

Question

Can anyone see anywhere in the code below where connections might leak out for this logging to database code? I am receiving the error in the title occasionally but not always when running this application.

The only thing I have left to try is to include: SqlConnection.ClearAllPools(); before the dispose. Would this help or cause undue load?

   private void InsertToDb(EventLogDto e, string connectionString)
    {
        var cmd = new SqlCommand();
        using (var sqlConn = new SqlConnection(connectionString))
        {
            try
            {
                // Open the connection.
                sqlConn.Open();

                // Build the SqlCommand object
                cmd = BuildSqlCommand(e, sqlConn);

                // Execute the Stored Proc.
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Instance.Fatal(ex);
            }
            finally
            {
                sqlConn.Close();
                sqlConn.Dispose();
            }
        }
    }

where BuldSqlCommand(e, sqlConn); is:

private SqlCommand BuildSqlCommand(EventLogDto e, SqlConnection sqlConn)
    {
        var cmd = new SqlCommand
        {
            CommandText = "dbo.InsertEventLog",
            CommandType = CommandType.StoredProcedure,
            Connection = sqlConn
        };

        InsertValue(cmd.Parameters, "@EventId", e.EventId);
        InsertValue(cmd.Parameters, "@DateCreated", e.EventDate);
        InsertValue(cmd.Parameters, "@Severity", e.Severity);
        InsertValue(cmd.Parameters, "@Message", e.Message);
        InsertValue(cmd.Parameters, "@Source", e.Source);
        InsertValue(cmd.Parameters, "@TargetSite", e.TargetSite);
        InsertValue(cmd.Parameters, "@StackTrace", e.StackTrace);

        return cmd;
    }

I think that the `sqlConn.Dispose()` might cause the problem. As the `close()` will return the connection to the connectionpool. While dispose gets rid of it. — CuccoChaser, Nov 26 '12 at 14:17
You are creating an instance of SqlCommand to only throw it away in favor of the new instance made by BuildSqlCommand. — Rob, Nov 26 '12 at 14:18
What does Logger.instance.Fatal() do? Does it create SqlConnections in an endless recursive nature? — Rob, Nov 26 '12 at 14:21
Also your finally statement to dispose the connection is pointless. The using statement wrapped around the connection does that same thing. — Rob, Nov 26 '12 at 14:22
Please don't write your own logger. This problem has already been solved and [the solution is free](http://stackoverflow.com/q/710863/299327). — Ryan Gates, Nov 26 '12 at 16:03
Thanks for the helpful comments. With regards to Ryan's comment - there were no frameworks that met my requirements for this project. — M05Pr1mty, Nov 26 '12 at 23:29

score 1 · Accepted Answer · answered Nov 26 '12 at 14:24

1

Try this and see if it helps.

try {
    using (var sqlConn = new SqlConnection(myConnectionString)) {
        using (SqlCommand cmd = sqlConn.CreateCommand()) {
            sqlConn.Open();
            AttachParameters(cmd, e);
            cmd.ExecuteNonQuery();
        }
    }
} catch (Exception ex) {
    Logger.Instance.Fatal(ex);
}

If its still happening I'd guess its because of Fatal().

answered Nov 26 '12 at 14:24

Rob

517
3
10

Thanks rob - as per your above comment looking closer at the underlying framework that had been written it appeared the fatal call was also hooked into a database logger which failed recursively. Good shout. – M05Pr1mty Nov 26 '12 at 23:27

SqlConnection - The timeout period elapsed prior to obtaining a connection from the pool

1 Answers1