If I use SqlCommand in a method as a parameter, does it block a SqlConnection dispose?

Question

I'm having connection pool issues on my service (max reached), everywhere that I try to open a connection I wrap it on a using statement to dispose it correctly, but I think something is not allowing it to work. I think it is because I'm using a method that expects a SqlCommand as a parameter, this is an example:

private void QueryDB(string sConnString, SqlCommand oComm)
{
    using (SqlConnection connection = new SqlConnection(sConnString))
    {
        try
        {
            connection.Open();
            oComm.Connection = connection;
            oComm.CommandTimeout = 2;
            oComm.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //log exception
        }
        catch (Exception e)
        {
            //log exception
        }
    }
}

The reason why I do this is because I need to assemble the parameters outside that method, like this:

public void Example1()
{
    using (SqlCommand command = new SqlCommand())
    {
        command.CommandText = "SELECT TOP 1 FROM Table ORDER BY column1 DESC";
        QueryDB(_connString, command));
    }
}

public void Example2()
{
    SqlCommand command= new SqlCommand();
    command.CommandText = "UPDATE Table set column1 = @value where column2 = @number";
    command.Parameters.Add(new SqlParameter { ParameterName = "@value", Value = "someValue", SqlDbType = SqlDbType.VarChar });
    command.Parameters.Add(new SqlParameter { ParameterName = "@number", Value = 3, SqlDbType = SqlDbType.Int });

    QueryDB(_connString, command));
}

In Example1 I try disposing the SqlCommand but I don't know if it works like that. Another thing to considerate is that I'm running a timer every second that executes Example1 or Example2, and I don't know if that has something to do with the problem, the Max pool size error happens sometimes, not everyday and it delays other queries. Is there something that I can do to improve this behavior? Thanks

try dispose it manually or try SqlCommand.Cancel(); and EndExecuteNonQuery(); — amirhosein adlfar
– amirhosein adlfar, Commented Jun 3, 2021 at 16:12
How long do the commands take to execute? If you're running them every second and they take more than one second, you might run into the issue because there are too many running in parallel. — Christian
– Christian, Commented Jun 3, 2021 at 16:13
Rather than pass in the SqlCommand instance, consider passing in a delegate that gets called to populate the command, and construct the command instance internally. — Jack A.
– Jack A., Commented Jun 3, 2021 at 16:43
Could be your application has deadlocks that are piling up. In SSMS connect to the instance and use the Monitor view to take a look at active queries and open connections. You should be able to check the state. Are they all WAITING or perhaps something else? That should give you some more perspective as to what is actually happening. — Igor
– Igor, Commented Jun 3, 2021 at 19:51
But the code you have shown above in your question should not keep any open managed connections. There might be some unmanaged connections that are pooled by .net CLR but providing your app is executing OK these should be minimal. — Igor
– Igor, Commented Jun 3, 2021 at 19:52

Dharman · Accepted Answer · 2021-06-03 19:51:52Z

I don't really know if this will solve your problem with respect to the connection pool issues, but, expanding on @Jack A's comment to your question, perhaps, a better way to structure your code would be to change your QueryDB method to take a delegate that updates the SqlCommand variable with the necessary information and, then, you can make sure both your SqlConnection and SqlCommand and taken care of correctly within that method.

private void QueryDB(string sConnString, Action<SqlCommand> commandDelegate)
{
    using (SqlConnection oCon = new SqlConnection(sConnString))
        using(SqlCommand oComm = new SqlCommand())
        {
            try
            {
                oCon.Open();
                oComm.Connection = oCon;
                oComm.CommandTimeout = 2;
                commandDelegate(oComm);
                oComm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                //log exception
            }
            catch (Exception e)
            {
                //log exception
            }
        }
}

You could then use it in either of the following ways in your code:

public void Uses()
{
    QueryDB(_connString, (oComm) => oComm.CommandText = "SELECT TOP 1 FROM Table ORDER BY column1 DESC");

    QueryDB(_connString, longerDelegate);
}

private void longerDelegate(SqlCommand oComm)
{
    oComm.CommandText = "UPDATE Table set column1 = @value where column2 = @number";
    oComm.Parameters.Add(new SqlParameter { ParameterName = "@value", Value = "someValue", SqlDbType = SqlDbType.VarChar });
    oComm.Parameters.Add(new SqlParameter { ParameterName = "@number", Value = 3, SqlDbType = SqlDbType.Int });
}

Again, I'm not sure this will solve your pooling problem, but it ensures everything is, at least, neatly wrapped in your QueryDB method.

qhart · Accepted Answer · 2021-06-10 18:53:04Z

I want to thank you all for your responses! After doing a lot of research and modifications, I implemented @Jack A's and @Jhon Busto's recomendations. But you where right John it didn't solve the connection pool problem, it turns out that the real problem was the timer, I didn't notice that it was constantly executing Example1 and Example2 but not every second, it was every 50ms or less, so I presume that it created a lot of connections in the pool. I was changing the Timer.Interval property of the timer but I didn't know this:

If Enabled and AutoReset are both set to false, and the timer has previously been enabled, setting the Interval property causes the Elapsed event to be raised once, as if the Enabled property had been set to true. To set the interval without raising the event, you can temporarily set the Enabled property to true, set the Interval property to the desired time interval, and then immediately set the Enabled property back to false.

Source: https://learn.microsoft.com/en-us/dotnet/api/system.timers.timer.interval?view=netframework-4.7.2

So, if I needed to change the Timer.Interval I followed Microsoft's documentation, I tested everything again and it worked. Be careful using Timers! hehe :)

Collectives™ on Stack Overflow

If I use SqlCommand in a method as a parameter, does it block a SqlConnection dispose?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related