C# SQL Connection Pool

Question

Good day guys,

I am running into a peculiar issue where I am running out of space in the connection pool for my C# application. I have written a custom SqlHelper class that should be returning the same connection to be used by each class.

But it would seem that this isn't working as expected. While debugging it seems like the same connection is returned unless the connection has been closed already. I thought I was instantiating multiple instances of my SqlHelper class and changed that to use a session variable which returns the same instance of my SqlHelper for each class.

Would appreciate any assistance in this, as I cannot see why this isn't working as expected.

Below is my SqlHelper class:

public class SqlHelper {

    /// <summary>
    /// Private connection string which reads the connection string from the web.config file
    /// </summary>
    private string _connectionString {
        get {
            //Get the connection string from the web.config
            return ConfigurationManager.ConnectionStrings["defaultConn"].ConnectionString;
        }
    }

    /// <summary>
    /// Internal connection
    /// </summary>
    private SqlConnection _connection;
    /// <summary>
    /// Public connection that instantiates the connection
    /// </summary>
    public SqlConnection Connection {
        get {
            //Check if the connection has not been created
            if (_connection == null)
                _connection = new SqlConnection(_connectionString);
            //Check if the connection is closed, if it is, open it back up again
            if (_connection.State == ConnectionState.Closed || _connection.State == ConnectionState.Broken)
                _connection.Open();
            //Return the connection
            return _connection;
        }
    }

    /// <summary>
    /// Executes SQL query with(out) parameters which will return a SqlDataReader to access results
    /// </summary>
    /// <param name="sqlCommand"></param>
    /// <param name="para"></param>
    /// <returns>SqlDataReader filled with results</returns>
    public SqlDataReader GetSqlReader(string sqlCommand, SqlParameter[] para = null) {
        //Build up the command with the connection
        SqlCommand command = new SqlCommand(sqlCommand, Connection);
        //Add all the parameters to the sql command
        if (para != null)
            foreach (SqlParameter param in para)
                command.Parameters.Add(param);
        return command.ExecuteReader();
    }

Below is my BaseController which returns the same SqlHelper instance for each session:

public class BaseController : Controller
{
    //protected SqlHelper sqlHelper = new SqlHelper();

    protected SqlHelper sqlHelper {
        get {
            SqlHelper helper = Session["SqlHelper"] as SqlHelper;
            if (helper == null) {
                helper = new SqlHelper();
                Session["SqlHelper"] = helper;
            }
            return helper;
        }
    }

I am suggesting to implement `IDisposable` interface in your `SqlHelper` class to close connection and release resources. — Krishnraj Rana, Jul 09 '18 at 11:43
https://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 — Tim Schmelter, Jul 09 '18 at 11:44
SQL server has excellent pool management - don't do something that the server does better. As have been suggested elsewhere, open a connection, use it, close it whenever you need one. Wrap it in `using` to ensure it goes. — Peter Smith, Jul 09 '18 at 11:47
@PeterSmith: .NET connection pooling isn't about SQL Server only. It's ADO .NET infrastructure feature, available to any provider. — Dennis, Jul 09 '18 at 11:59
Thank you very much for all the comments guys, I came right by storing an instance of my SqlHelper in the session and keep reusing it. — Tachyon, Jul 09 '18 at 12:33
In practice, you may want to open and close your SQL-Server connection after every use. Normally doing this causes a delay to executing SQL but by default SQL-Server uses "connection pooling" which means that past connections aren't really closed. It kind of happens auto-magically and happens quickly. Also because you always close the connection you never have to worry about using more connections than you actually need. Read up on "SQL Server Connection Pooling" for more information. — Jason Geiger, Jul 09 '18 at 12:42

score 1 · Answer 1 · answered Jul 09 '18 at 11:45

1

Make your class Disposable as you keep _connection object as a field.

answered Jul 09 '18 at 11:45

Sergei Zinovyev

1,238
14
14

C# SQL Connection Pool

1 Answers1