报错如下: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>
/// <res>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </res>
/// <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);
}
}
}
在网上搜了一下,解释为连接过多,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>
/// <res>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </res>
/// <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);
}
}
}
关闭掉连接 试试
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;
}
------------------
这里打开没关
{
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(); }
--------------
这里没关
{
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(); }
------------
汗一个,都没关
{
里面能关啥..........
}
finally
{
rdr.dispose();
conn.close();
}
唉.......
这一句,不是在ExecuteReader关闭时,自动关闭连接吗?
这样不管是否有异常都会关闭了
{
SqlDataReader rdr = null;
SqlConnection conn = new SqlConnection(CONN_STRING);
SqlCommand cmd = new SqlCommand(); try
{
cmd.CommandTimeout = 100; PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
finally
{
if (rdr != null) rdr.Close();
if (conn != null) conn.Close(); }
}
改成这样了,应该没问题了吧?
SqlDataReader rd = sql.ExecuteReader(CommandType.Text, sql_show);
行 22: shows.DataSource = rd;
行 23: shows.DataBind();
看了:
http://topic.csdn.net/t/20050822/09/4222337.html
这个贴子里四楼的提示,好像conn.close不能在函数里显式关闭!
这样不管是否有异常都会关闭了欣赏这句
{
// 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;
}
http://topic.csdn.net/t/20021224/23/1296722.html即:不能在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;
}
finally
{
rdr.dispose();
conn.close(); //也不能在此关闭
}
因为阅读器DataAdapter在连接关闭的情况下,是无法进行read(),fieldCount()...好像只能在外面:SqlDataReader rd = sql.ExecuteReader(CommandType.Text, sql_show);
行 22: shows.DataSource = rd;
行 23: shows.DataBind();这儿关闭conn连接了
shows.DataSource = rd;
shows.DataBind();
rd.Close();这样把rd关了,但我为了省事,写成这样了: shows.DataSource= sql.ExecuteReader(CommandType.Text, sql_show);
shows.DataBind();是否这个原因?没有把连接关闭?
还是
rd.Dispose(); ?谢谢!
如果访问量很大还是超时,可以考虑设置超时时间
这样不管是否有异常都会关闭了
//切记不要这么做
//欣赏这句的都是没做过大项目的数据库的connection在用完后立即显式关闭
------------------------------------------------------------------------------------
顶一个,我也碰到过这样的,结果进程一会就吃光了
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 这句.
DataReader不是一次取完所有数据 所以不能立即关闭连接 需要在你处理完所有数据后关闭。
实际上关闭DataReader也可以关闭连接。
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 这句.********************上面的代码是我以前用的,是从sqlhelper里拷贝出来的!问题在于返回的sqldatareader 忘记关闭了!
类似于:
Sqldatareader rd = ....();rd.close (这一句很重要)注意上面代码里的这一句:CommandBehavior.CloseConnection当rd.close关闭,会自动关闭相应的conn.open!
不必在函数里显式关闭!
放在函数里会报错!以后全部检查重写后,一切正常!希望可以给后面的同学借鉴!