急，访客增多，系统经常报错为：连接超时，如何解决？
报错如下：
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
在网上搜了一下，解释为连接过多，sqlconnection 打开后未关闭造成的！
下面是我的SQL访问代码，大家帮我看看有无关闭连接的问题？
如无问题，访客增多，如何增加连接池数量？谢谢！
public sealed class sql
{
private sql() { }
//Database connection strings
public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings[ "space_wenxueConnectionString "].ConnectionString;
/// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders ", new SqlParameter( "@prodid ", 24));
/// </remarks>
/// <param name= "cmdType "> the CommandType (stored procedure, text, etc.) </param>
/// <param name= "cmdText "> the stored procedure name or T-SQL command </param>
/// <param name= "cmdParms "> an array of SqlParamters used to execute the command </param>
/// <returns> A SqlDataReader containing the results </returns>
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(CONN_STRING);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
// cmd.CommandTimeout = 150;
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(CONN_STRING);
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
conn.Close();
}
public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
conn.Close();
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name= "cmd "> SqlCommand object </param>
/// <param name= "conn "> SqlConnection object </param>
/// <param name= "trans "> SqlTransaction object </param>
/// <param name= "cmdType "> Cmd type e.g. stored procedure or text </param>
/// <param name= "cmdText "> Command text, e.g. Select * from Products </param>
/// <param name= "cmdParms "> SqlParameters to use in the command </param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
[解决办法]
你把SqlDataReader 取值放在arraylist
关闭掉连接 试试
[解决办法]
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(CONN_STRING);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
// cmd.CommandTimeout = 150;
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
------------------
这里打开没关
[解决办法]
DataReader这样的数据集是要占用数据库连接的，建采用DataSet或DataTable这样的离线型数据集
[解决办法]
在CATCH后边加上finally,在finally块里关闭连接
这样不管是否有异常都会关闭了
[解决办法]
try
{
// cmd.CommandTimeout = 150;
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
conn.Close();//這裡也要關啊
return rdr;
}
catch
{
conn.Close();
throw;
}
[解决办法]
close()
[解决办法]
记得连接和DataReader用完都要关闭
如果访问量很大还是超时，可以考虑设置超时时间
[解决办法]
在CATCH后边加上finally,在finally块里关闭连接
这样不管是否有异常都会关闭了
//切记不要这么做
//欣赏这句的都是没做过大项目的
数据库的connection在用完后立即显式关闭
[解决办法]
DataReader这样的数据集是要占用数据库连接的，建采用DataSet或DataTable这样的离线型数据集
------------------------------------------------
顶一个，我也碰到过这样的，结果进程一会就吃光了
[解决办法]
给示例:
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
注意加上 CommandBehavior.CloseConnection 这句.
[解决办法]
我以前也碰到这种情况,感觉SqlDataReader在用户量大时就会出现这个问题,
解决方案是:把SqlDataReader换成DataTable或DataSet就行了.
[解决办法]
建议把数据库操作写成一个类。要改也方面些。
[解决办法]
DataReader是速度最快的选择 你的访问量大建议还是用DataReader
DataReader不是一次取完所有数据 所以不能立即关闭连接 需要在你处理完所有数据后关闭。
实际上关闭DataReader也可以关闭连接。
