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