Connection open and Close issue in Sql Connection

Question

Find to many solution but not a single solution fit on my scenario.

Problem: I am working on online software which is build under asp.net. From the past few days my application is working slow and some time its crash. When I try to find the issue, then I find that connection pool have connections which are in sleeping mode. I know that some connection are open but not closed properly. In below I will show you My DBManager file. Please review it and give me suggestion which can help me to open and close my connection properly.

Note: exception on connection is thrown when user use application fastly. My application use many data entry operator which are type with speed. And move between pages again and again.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for DBManager
/// </summary>
public class DBManager
{
    public static SqlConnection _connection;
    public static SqlCommand _command;
    public static SqlDataReader _reader;
    public static SqlDataAdapter _dataAdapter;
    public List<SqlParameter> Parameters = new List<SqlParameter>();
    public static string _connectionString = "DefaultConnectionString";
    SqlTransaction _sqlTransaction;
     
    public DBManager()
    {
        // TODO: Add constructor logic here
    }

    public DBManager(SqlTransaction sqlTransaction = null)
    {
        _sqlTransaction = sqlTransaction;
    }

    public DBManager(string connectionStringName, SqlTransaction sqlTransaction = null)
    {
        _connectionString = connectionStringName;
        _sqlTransaction = sqlTransaction;
    }

    public static string CreateConnection()
    {
        string ConnectionString = ConfigurationManager.ConnectionStrings[_connectionString].ConnectionString;
        _connection = new SqlConnection(ConnectionString);
        _connection.Open();
        return "0";
    }

    public static void CloseConnection()
    {
        _connection.Close();
        _connection.Dispose();
    }

    public void AddParameter(string parameterName, object value, SqlDbType sqlDbType, int size)
    {
            SqlParameter parameter = new SqlParameter(parameterName, sqlDbType, size);
            parameter.Value = value;

            Parameters.Add(parameter);      
    }

    public void AddParameter(string parameterName, object value, SqlDbType sqlDbType, int size,ParameterDirection parameterDirection)
    {
        SqlParameter parameter = new SqlParameter(parameterName, sqlDbType, size);
        parameter.Value = value;
        parameter.Direction = parameterDirection;
        Parameters.Add(parameter);
    }

    public void AddParameter(string parameterName, object value)
    {
        SqlParameter parameter = new SqlParameter(parameterName,value);
        Parameters.Add(parameter);
    }

    public int ExecuteNonQuery(string procedureName)
    {
        int result = 0;

        try
        {
           // if (CreateConnection() == "1") { return 0; }
            CreateConnection();
            _command = new SqlCommand(procedureName, _connection);
            if (Parameters.Count != 0)
            {
                for (int i = 0; i < Parameters.Count; i++)
                {
                    _command.Parameters.Add(Parameters[i]);
                }

            }
            _command.CommandType = CommandType.StoredProcedure;
            result = _command.ExecuteNonQuery();
            CloseConnection();
            _command.Dispose();
        }
        catch (Exception)
        {
            CloseConnection();
            _command.Dispose();
            throw;
        }

        return result;
    }

    public SqlDataReader ExecuteReader(string procedureName)
    {
        SqlDataReader reader;

        try
        {
            CreateConnection();
           // if (CreateConnection() == "1") { return reader=0; }
            _command = new SqlCommand(procedureName, _connection);

            if (Parameters.Count != 0)
            {
                for (int i = 0; i < Parameters.Count; i++)
                {
                    _command.Parameters.Add(Parameters[i]);
                }

            }

            _command.CommandType = CommandType.StoredProcedure;
            reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
            CloseConnection();
            _command.Dispose();
        }
        catch (Exception)
        {
            CloseConnection();
            _command.Dispose();
            throw;
        }
      
        return reader;
    }

    public DataSet ExecuteDataSet(string procedureName)
    {
        DataSet dataSet = new DataSet();

        try
        {
            CreateConnection();
            _command = new SqlCommand(procedureName, _connection);

            if (Parameters.Count != 0)
            {
                for (int i = 0; i < Parameters.Count; i++)
                {
                    _command.Parameters.Add(Parameters[i]);    
                }
                
            }

            _command.CommandType = CommandType.StoredProcedure;
            _dataAdapter = new SqlDataAdapter(_command);
            _dataAdapter.Fill(dataSet);
            CloseConnection();
            _command.Dispose();
            _dataAdapter.Dispose();
        }
        catch (Exception)
        {
            CloseConnection();
            _dataAdapter.Dispose();
            _command.Dispose();
            throw;
        }
           return dataSet;
    }

    public DataTable ExecuteDataTable(string procedureName)
    {
        DataTable dataTable = new DataTable();

        try
        {
            CreateConnection();
            _command = new SqlCommand(procedureName, _connection);

            if (Parameters.Count != 0)
            {
                for (int i = 0; i < Parameters.Count; i++)
                {
                    _command.Parameters.Add(Parameters[i]);
                }
            }

            _command.CommandType = CommandType.StoredProcedure;
            _dataAdapter = new SqlDataAdapter(_command);
            _dataAdapter.Fill(dataTable);
            CloseConnection();
            _command.Dispose();
            _dataAdapter.Dispose();
        }
        catch (Exception)
        {
            CloseConnection();
            _dataAdapter.Dispose();
            _command.Dispose();
            throw;
        }
       
        return dataTable;
    }

    public string ExecuteScalar(string procedureName)
    {
        string result = "";

        try
        {
            CreateConnection();
            _command = new SqlCommand(procedureName, _connection);

            if (Parameters.Count != 0)
            {
                for (int i = 0; i < Parameters.Count; i++)
                {
                    _command.Parameters.Add(Parameters[i]);
                }

            }

            _command.CommandType = CommandType.StoredProcedure;
            result = _command.ExecuteScalar().ToString();
            CloseConnection();
            _command.Dispose();
        }
        catch (Exception)
        {
            CloseConnection();
            _command.Dispose();
            throw;
        }
       
        return result;
    }
}

Exceptions are:

InnerException System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting.
at System.Data.SqlClient.SqlConnection.

InnerException System.InvalidOperationException: Invalid operation. The connection is closed.
at System.Data.ProviderBase.DbConnectionClosed.

InnerException System.NullReferenceException: Object reference not set to an instance of an object.

It depends on the code calling this class. Do you ever call `CloseConnection`? Why not use entity framework? — Nick.Mc, Apr 18 '18 at 05:00
See [this post](https://stackoverflow.com/a/9707060/304683) and heed the advice. Hth — EdSF, Apr 18 '18 at 05:34
I suppose you have already moved on from this, but one **huge** problem with your class (as presented in this question) is that it is using **static** instances of `SqlConnection` and other classes. This means different users will access the *same* instances, opening and closing them behind each others back etc... — user1429080, Aug 17 '18 at 07:06

Pranay Rana · Accepted Answer · 2018-04-18T05:05:50.950

You are doing it wrong way all together , you should create connection but should not open it , you should open it when needed and close it

I suggest remove this function also and just make use of using

    public static string CreateConnection()
    {

   string ConnectionString = ConfigurationManager.ConnectionStrings[_connectionString].ConnectionString;
        _connection = new SqlConnection(ConnectionString);
           //remove this line 
        //_connection.Open();
        return "0";
    }

you dont need this function also

 public static void CloseConnection()
    {
        _connection.Close();
        _connection.Dispose();
    }

make use of using as suggested that will help

Best way I suggest is always make use of using and dispose conenction , as below

using(SqlConnection con = new SqlConnection() )
{
}

If you are worrying about it will create too many object, then for information connection with database is pooled means you can sepecify connection pooling information in connection string, so that way you dont have to worry about making connection when you create connection object.

<add name="sqlConnectionString" connectionString="Data 
Source=mySQLServer;Initial Catalog=myDatabase;Integrated 
Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool Size=0;Max 
Pool Size=100;Pooling=true;" />

above is connection string which take cares of pooling

Sample code , this i how i did in my poroject , if you see the code i disponse connection object every time by making use of using

public class DbHelper
{
    #region Private methods

    private static OracleConnection GetConnection()
    {
        string connectionString = DbConnectionString.ConnectionString;
        return new OracleConnection(connectionString);
    }

    private static OracleCommand GetCommand(OracleConnection connection, string commandText, OracleParameter[] param, bool isProcedure)
    {
        OracleCommand dbCommand = new OracleCommand();
        dbCommand.Connection = connection;
        dbCommand.CommandText = commandText;
        if (param != null)
            dbCommand.Parameters.AddRange(param);
        if (isProcedure)
            dbCommand.CommandType = CommandType.StoredProcedure;
        return dbCommand;
    }
    #endregion

    #region public methods
    public static DataTable GetDataTable(string commandText, OracleParameter[] odbcPrams, bool isProcedure = false)
    {
        DataTable dt = new DataTable();
        using (OracleConnection ODBCConn = GetConnection())
        {
            using (OracleCommand dbCommand = GetCommand(ODBCConn, commandText, odbcPrams, isProcedure))
            {
                ODBCConn.Open();
                OracleDataAdapter da = new OracleDataAdapter(dbCommand);
                da.Fill(dt);
            }
        }

        return dt;
    }
    #endregion
}

Connection open and Close issue in Sql Connection

1 Answers1