以前和程序没有问题,现在老是出现这"连接都已被使用并已达到最大池大小"
错误提示
超时时间已到。在从池中获取连接之前超时时间已过。出现这种情况可能是因为所有池连接都已被使用并已达到最大池大小。
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。
以前是用的Sql句语,后来改成存储过程之后就出现在上面的这种情况.
代码如下:
SqlConnection cn = new SqlConnection(DbHelperSQL.connectionString);
if( cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn ;
cmd.CommandText = "Add_SaleSettleBill ";
cmd.CommandType = CommandType.StoredProcedure ;
SqlParameter parm = new SqlParameter( "@CompanyId ",SqlDbType.VarChar,20);
parm.Value = ssbm.CompanyId;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@BillNo ",SqlDbType.VarChar,20);
parm.Value = ssbm.BillNo ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@CustId ",SqlDbType.VarChar,20);
parm.Value = ssbm.CustId ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@Sales ",SqlDbType.VarChar,20);
parm.Value = ssbm.Sales ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@doMan ",SqlDbType.VarChar,20);
parm.Value = ssbm.doMan ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@PayBillNo ",SqlDbType.VarChar,20);
parm.Value = ssbm.PayBillNo ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@ifDuct ",SqlDbType.Bit);
parm.Value = ssbm.ifDuct ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@VoiceNo ",SqlDbType.VarChar,20);
parm.Value = ssbm.VoiceNo ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@RealAmount ",SqlDbType.Float);
parm.Value = ssbm.RealAmount ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@FeeAmount ",SqlDbType.Float);
parm.Value = ssbm.FeeAmount ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@InstFeeAmount ",SqlDbType.Float);
parm.Value = ssbm.InstFeeAmount ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@BalanceAmount ",SqlDbType.Float);
parm.Value = ssbm.BalanceAmount ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@DepositRest ",SqlDbType.Float);
parm.Value = ssbm.DepositRest ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@UseDeposit ",SqlDbType.Float);
parm.Value = ssbm.UseDeposit ;
cmd.Parameters.Add(parm);
parm = new SqlParameter( "@Result ",SqlDbType.VarChar,20);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException ee)
{
throw new Exception(ee.Message);
}
finally
{
cn.Close();
cn.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
}
if( parm.Value.ToString() == "ok " )
{
return true;
}
else
{
return false;
}
存储过程是这样写的.
CREATE PROCEDURE [dbo].[Add_SaleSettleBill]
@CompanyIdvarchar(20),
@BillNo varchar(20),
@CustIdvarchar(20),
@Sales varchar(20),
@doMan varchar(20),
@PayBillNo varchar(20),
@ifDuct bit,
@VoiceNo varchar(20),
@RealAmount float ,
@FeeAmount float ,
@InstFeeAmount float ,
@BalanceAmountfloat ,
@DepositRest float ,
@UseDeposit float ,
@Resultvarchar(20)output
AS
declare@tempValuevarchar(20)
declare @parentIdvarchar(20)
begin tran
select @parentId = parentId from BS_Customer where companyId = companyId and CustId =@CustId
select @tempValue = count(*) from BS_SaleSettleBill where companyId = @companyId and billNo = @billNo
IF @tempValue = 0
BEGIN
Insert into BS_SaleSettleBill
(CompanyId,BillNo,CustId,OwerCustId,Sales,doMan,PayBillNo,ifDuct,VoiceNo,RealAmount,FeeAmount,InstFeeAmount,BalanceAmount,DepositRest,UseDeposit)
values
(@CompanyId,@BillNo,@CustId,@parentId,@Sales,@doMan,@PayBillNo,@ifDuct,@VoiceNo,@RealAmount,@FeeAmount,@InstFeeAmount,@BalanceAmount,@DepositRest,@UseDeposit)
END
ELSE
BEGIN
UpDate BS_SaleSettleBill set CustId = @CustId,OwerCustId =@parentId,Sales = @Sales,doMan = @doMan ,PayBillNo =@PayBillNo,ifDuct =@ifDuct,
VoiceNo = @VoiceNo,UseDeposit =@UseDeposit where CompanyId =@CompanyId and BillNo = @BillNo
END
IF @@ERROR = 0
BEGIN
COMMIT
SET @Result = 'ok '
RETURN
END
ELSE
BEGIN
ROLLBACK
SET @Result = 'error '
RETURN
END
GO
--------------------下面是以前的代码
//string _ifDuct = "0 ";
//
//if( ssbm.ifDuct == true )
//{
//_ifDuct = "1 ";
//}
//
//ssbm.OwerCustId = this.GetHQId(ssbm.CompanyId,ssbm.CustId);
//
//string executeString = string.Format( "Insert into BS_SaleSettleBill(CompanyId,BillNo,CustId,OwerCustId,Sales,doMan,PayBillNo,ifDuct,VoiceNo,RealAmount,FeeAmount,InstFeeAmount,BalanceAmount,DepositRest,UseDeposit)values( " +
// " '{0} ', '{1} ', '{2} ', '{3} ', '{4} ', '{5} ', '{6} ', '{7} ', '{8} ', '{9} ', '{10} ', '{11} ', '{12} ', '{13} ', '{14} ') ",
//ssbm.CompanyId,ssbm.BillNo,ssbm.CustId,ssbm.OwerCustId,ssbm.Sales,ssbm.doMan,ssbm.PayBillNo,_ifDuct,ssbm.VoiceNo,ssbm.RealAmount,
//ssbm.FeeAmount,ssbm.InstFeeAmount,ssbm.BalanceAmount,ssbm.DepositRest,ssbm.UseDeposit);
//
//bool isSuccess = IsExistSaleBalanceBillRecord(ssbm.CompanyId,ssbm.BillNo);
//
//if( isSuccess == true )
//{
//isSuccess = UpDateSaleBalanceBillRecord(ssbm);
//}
//else
//{
//isSuccess = ExecuteNonQuery(executeString);
//}
//
//return isSuccess;
/// <summary>
/// 判断销售结算单是否已经存在
/// </summary>
/// <param name= "companyId "> </param>
/// <param name= "billNo "> </param>
/// <returns> </returns>
public bool IsExistSaleBalanceBillRecord(string companyId,string billNo)
{
string executeString = string.Format( " select count(*) from BS_SaleSettleBill where companyId = '{0} ' and billNo = '{1} ' ",companyId,billNo);
return ExecuteResult(executeString);
}
/// <summary>
/// 更新销售结算单
/// </summary>
/// <param name= "ssbm "> </param>
/// <returns> </returns>
private bool UpDateSaleBalanceBillRecord(zhuanjia.Model.BS_SaleSettleBill ssbm)
{
string _ifDuct = "0 ";
if( ssbm.ifDuct == true )
{
_ifDuct = "1 ";
}
string executeString = string.Format( "UpDate BS_SaleSettleBill set CustId = '{2} ',OwerCustId = '{3} ',Sales = '{4} ',doMan = '{5} ' ,PayBillNo = '{6} ',ifDuct = '{7} ', " +
" VoiceNo = '{8} ',UseDeposit = '{9} ' where CompanyId = '{0} ' and BillNo = '{1} ' ",
ssbm.CompanyId,ssbm.BillNo,ssbm.CustId,ssbm.OwerCustId,ssbm.Sales,ssbm.doMan,ssbm.PayBillNo,_ifDuct,ssbm.VoiceNo
,ssbm.UseDeposit);
return ExecuteNonQuery(executeString);
}
请问这是什么问题呢?
现在把以前好多的执行方法的都放到存储过程里去了.
[解决办法]
你肯定是哪里忘记关连接了
肯定的,我也遇到过
[解决办法]
我刚看了下代码,没找到哪出错，请高手指教吧
[解决办法]
单步调试运行的时候那步报错?
[解决办法]
看看连接有关没
要不就单步调试一下吧.
[解决办法]
可能是有连接未关闭，如果不是就调试下
[解决办法]
if( cn.State == ConnectionState.Closed)
{
cn.Open();
}
把这段代码删掉试试
[解决办法]
你是怎么运行的
试试设置下默认连接池大小吧
[解决办法]
先找找有那个地方连接没有关闭,资源没有释放掉,
再把连接池的最大值和最小值改一下