代码之家 › 专栏 › 技术社区 › Ali Imran

Sql连接中的连接打开和关闭问题

connection-pooling asp.net c#

Ali Imran · 技术社区 · 8 年前

找到多个解决方案,但没有一个解决方案适合我的场景。

问题: 我正在开发基于asp的在线软件。网从过去几天开始,我的应用程序运行缓慢,有时会崩溃。当我尝试查找问题时,我发现连接池中有处于睡眠模式的连接。我知道有些连接已打开,但未正确关闭。在下面,我将向您展示我的DBManager文件。请检查它,并给我建议,这可以帮助我打开和关闭我的连接正确。

注: 当用户快速使用应用程序时,会引发连接异常。我的应用程序使用了许多数据输入操作符,这些操作符的类型与速度有关。并在页面之间反复移动。

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

例外情况包括:

InnerException系统。InvalidOperationException:ExecuteReader需要打开且可用的连接。连接的当前状态为“正在连接”。
at系统。数据SqlClient。SqlConnection。

InnerException系统。InvalidOperationException:操作无效。连接已关闭。
at系统。数据ProviderBase。数据库连接已关闭。

InnerException系统。NullReferenceException:对象引用未设置为对象的实例。

1 回复 | 直到 6 年前

Pranay Rana 8 年前

你们一起做的都是错误的,你们应该创建连接,但不应该打开它,你们应该在需要的时候打开它,然后关闭它

我建议也删除此功能,只需使用 using

    public static string CreateConnection()
    {

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

您也不需要此功能

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

按照建议使用会有帮助的

我建议最好的方法就是 使用 并按如下方式处理conenction

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

如果您担心它会创建太多的对象,那么for information与数据库的连接是池化的,这意味着您可以在连接字符串中单独指定连接池信息,这样您就不必担心在创建连接对象时建立连接。

<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;" />

上面是处理池的连接字符串

示例代码,这是我在项目中的做法,如果您看到我每次使用 使用

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
}