using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// DataBase 的摘要说明
/// </summary>
public class DataBase
{
private static DataBase _instance; //私有化构造器
private DataBase()
{
} //获取DataBase的实例
public static DataBase GetInstance()
{
if (_instance == null)
{
_instance = new DataBase();
}
return _instance;
} //创建数据库连接,返回一个SqlConnection
public static SqlConnection ReturnConn()
{
string strConn = ConfigurationManager.ConnectionStrings["GDOUSCIMISConn"].ConnectionString;
SqlConnection Conn = new SqlConnection(strConn);
if (Conn.State.Equals(ConnectionState.Closed))
{
Conn.Open();
}
return Conn;
} //根据参数,调用重载方法,执行存储过程,返回一个SqlCommand
public static SqlCommand CreateCmd(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = Conn;
Cmd.CommandText = procName;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
return Cmd;
}
//根据参数调用重载方法,执行存储过程返回DataTable
public static DataTable RunProcGetTable(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName, prams);
SqlDataAdapter Dtr = new SqlDataAdapter();
DataSet Ds = new DataSet();
Dtr.SelectCommand = Cmd;
Dtr.Fill(Ds);
DataTable Dt = Ds.Tables[0];
Conn.Close();
return Dt;
}原来我是这样写的,这样的话多人一起访问的时候,就会出现
"异常详细信息: System.InvalidOperationException: 超时时间已到。超时时间已到,但是尚未从池中获取连接。出现这种情况可能是因为所有池连接均在使用,并且达到了最大池大小。"
在web.config里我已经设置了如下:
<connectionStrings>
<add name="GDOUSCIMISConn" connectionString="Data Source=K513CSERVER;Initial Catalog=GDOUSCIMIS;User ID=sa;Password=123456;Max Pool Size = 300;" providerName="System.Data.SqlClient"/>
</connectionStrings>
请问如何解决?
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// DataBase 的摘要说明
/// </summary>
public class DataBase
{
private static DataBase _instance; //私有化构造器
private DataBase()
{
} //获取DataBase的实例
public static DataBase GetInstance()
{
if (_instance == null)
{
_instance = new DataBase();
}
return _instance;
} //创建数据库连接,返回一个SqlConnection
public static SqlConnection ReturnConn()
{
string strConn = ConfigurationManager.ConnectionStrings["GDOUSCIMISConn"].ConnectionString;
SqlConnection Conn = new SqlConnection(strConn);
if (Conn.State.Equals(ConnectionState.Closed))
{
Conn.Open();
}
return Conn;
} //根据参数,调用重载方法,执行存储过程,返回一个SqlCommand
public static SqlCommand CreateCmd(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = Conn;
Cmd.CommandText = procName;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
return Cmd;
}
//根据参数调用重载方法,执行存储过程返回DataTable
public static DataTable RunProcGetTable(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName, prams);
SqlDataAdapter Dtr = new SqlDataAdapter();
DataSet Ds = new DataSet();
Dtr.SelectCommand = Cmd;
Dtr.Fill(Ds);
DataTable Dt = Ds.Tables[0];
Conn.Close();
return Dt;
}原来我是这样写的,这样的话多人一起访问的时候,就会出现
"异常详细信息: System.InvalidOperationException: 超时时间已到。超时时间已到,但是尚未从池中获取连接。出现这种情况可能是因为所有池连接均在使用,并且达到了最大池大小。"
在web.config里我已经设置了如下:
<connectionStrings>
<add name="GDOUSCIMISConn" connectionString="Data Source=K513CSERVER;Initial Catalog=GDOUSCIMIS;User ID=sa;Password=123456;Max Pool Size = 300;" providerName="System.Data.SqlClient"/>
</connectionStrings>
请问如何解决?
如果发生异常,你如何保证连接能关闭?CreateCmd中负责打开连接,但是在哪里关闭呢?
你如何保证用完后关闭连接?
把可能发生异常的代码放在try里面
如果有事务,要在catch中回滚事务
而连接关闭要放在finally里面
public static DataTable RunProcGetTable(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName, prams);
SqlDataAdapter Dtr = new SqlDataAdapter();
DataSet Ds = new DataSet();
Dtr.SelectCommand = Cmd;
Dtr.Fill(Ds);
DataTable Dt = Ds.Tables[0];
Conn.Close();
return Dt;
}非常感谢你的回答。
我们每个方法都在Conn.Close();这里关闭,也尝试了在Conn.Close();后面加Conn.Dispose();但是还是出现连接池错误。这位老兄,可否发一份代码上来看看。
谢谢了!
但你得到这个SqlCommand对象后做了什么,我就不知道了
关闭连接了吗?
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// DataBase 的摘要说明
/// </summary>
public class DataBase
{
private static DataBase _instance; //私有化构造器
private DataBase()
{
} //获取DataBase的实例
public static DataBase GetInstance()
{
if (_instance == null)
{
_instance = new DataBase();
}
return _instance;
} //创建数据库连接,返回一个SqlConnection
public static SqlConnection ReturnConn()
{
string strConn = ConfigurationManager.ConnectionStrings["GDOUSCIMISConn"].ConnectionString;
SqlConnection Conn = new SqlConnection(strConn);
if (Conn.State.Equals(ConnectionState.Closed))
{
Conn.Open();
}
return Conn;
} //根据参数,调用重载方法,执行存储过程,返回一个SqlCommand
public static SqlCommand CreateCmd(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = Conn;
Cmd.CommandText = procName;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
return Cmd;
}
public static SqlCommand CreateCmd(string procName)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = Conn;
Cmd.CommandText = procName;
return Cmd;
} //根据传入的Sql语句,返回一个SqlCommand
public static SqlCommand CreateSqlCmd(string sqlSting)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = new SqlCommand(sqlSting, Conn);
return Cmd;
}
//根据参数调用重载方法,执行存储过程返回数据集SqlDataReader
public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams)
{
SqlCommand Cmd = CreateCmd(procName, prams);
SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
return Dr;
} public static SqlDataReader RunProcGetReader(string procName)
{
SqlCommand Cmd = CreateCmd(procName);
SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
return Dr;
}
///根据传入的Sql语句,返回数据集SqlDataReader
public static SqlDataReader RunStringGetReader(string sqlString)
{
SqlCommand Cmd = CreateSqlCmd(sqlString);
SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
return Dr;
}
//根据参数调用重载方法,执行存储过程返回DataTable
public static DataTable RunProcGetTable(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName, prams);
SqlDataAdapter Dtr = new SqlDataAdapter();
DataSet Ds = new DataSet();
Dtr.SelectCommand = Cmd;
Dtr.Fill(Ds);
DataTable Dt = Ds.Tables[0];
Conn.Close();
return Dt;
} public static DataTable RunProcGetTable(string procName)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName);
SqlDataAdapter Dtr = new SqlDataAdapter();
DataSet Ds = new DataSet();
Dtr.SelectCommand = Cmd;
Dtr.Fill(Ds);
DataTable Dt = Ds.Tables[0];
Conn.Close();
return Dt;
}
//根据传入的Sql语句,返回数据集DataTable
public static DataTable RunStringGetTable(string sqlString)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateSqlCmd(sqlString);
SqlDataAdapter Dtr = new SqlDataAdapter();
DataSet Ds = new DataSet();
Dtr.SelectCommand = Cmd;
Dtr.Fill(Ds); DataTable Dt = Ds.Tables[0];
Conn.Close();
return Dt; } //根据参数,调用重载方法,执行存储过程,返回受影响的行数
public static int RunExecuteProc(string procName)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName);
int intResult = Cmd.ExecuteNonQuery();
Conn.Close();
return intResult;
}
public static int RunExecuteProc(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName, prams);
int intResult = Cmd.ExecuteNonQuery();
Conn.Close();
return intResult;
} //根据传入的sql语句,返回受影响的行数
public static int RunExecuteString(string sqlString)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateSqlCmd(sqlString);
int intResult = Cmd.ExecuteNonQuery();
Conn.Close();
return intResult;
} //根据参数,调用重载方法,执行存储过程,返回首行首列
public static string RunExecuteProcScalar(string procName)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName);
string stringScalar = Convert.ToString(Cmd.ExecuteScalar());
Conn.Close();
return stringScalar;
} public static string RunExecuteProcScalar(string procName, SqlParameter[] prams)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateCmd(procName, prams);
string stringScalar = Convert.ToString(Cmd.ExecuteScalar());
Conn.Close();
return stringScalar;
} /// <summary>
/// 根据传入的sql语句,返回首行首列
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static string RunExecuteStringScalar(string sqlString)
{
SqlConnection Conn = ReturnConn();
SqlCommand Cmd = CreateSqlCmd(sqlString);
string stringScalar = Convert.ToString(Cmd.ExecuteScalar());
Conn.Close();
return stringScalar;
}
//根据传入的存储过程名和参数,获得分页数据源PagedDataSource
public static PagedDataSource RunProcGetPds(string procName, SqlParameter[] prams)
{
PagedDataSource pds = new PagedDataSource();
pds.DataSource = RunProcGetTable(procName, prams).DefaultView;
pds.AllowPaging = true;
return pds;
} //根据传入的存储过程名,获得分页数据源PagedDataSource
public static PagedDataSource RunProcGetPds(string procName)
{
PagedDataSource pds = new PagedDataSource();
pds.DataSource = RunProcGetTable(procName).DefaultView;
pds.AllowPaging = true;
return pds;
} //根据传入的sql语句,获得分页数据源PagedDataSource
public static PagedDataSource RunStringGetPds(string sqlString)
{
PagedDataSource pds = new PagedDataSource();
pds.DataSource = RunStringGetTable(sqlString).DefaultView;
pds.AllowPaging = true;
return pds;
}
/// <summary>
/// 编辑时,根据传过来的id绑定对应的内容显示出来
/// </summary>
/// <returns></returns>
public static DataRowView RunViewGetString(String str)
{
SqlConnection Conn = ReturnConn(); SqlDataAdapter sda = new SqlDataAdapter(str, Conn);
DataSet ds = new DataSet();
sda.Fill(ds,"edittable");
return ds.Tables["edittable"].DefaultView[0];
}}请问如何改比较好,才不会出现连接池错误的问题与并发访问错误。
改大点不就得了。
数据库操作时用using语句来关闭连接。
或者用在连接语句中加入polling=false