CODEWITHSUNDEEP
c#sql-serverasp.net-mvc
Tachyon
Tachyon

Reputation: 2411

C# SQL Connection Pool

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;
        }
    }

Upvotes: 0

Views: 639

Answers (1)

Sergei Zinovyev
Sergei Zinovyev

Reputation: 1286

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

Upvotes: 1

Related Questions