问题描述
最近做一个网站,遇到下面问题,2-3台机器打开后,sql里面的连接数爆满~错误信息:----------------------超时时间已到。超时时间已到，但是尚未从池中获取连接。出现这种情况可能是因为所有池连接均在使用，并且达到了最大池大小。----------------------使用了母版页,都是使用的存储过程,没有一个sql语句.大约7-8个页面使用同一个母版页,母版页很简单,头-中-下都是空的.左边是一个datalist显示下面样式的信息:-----------------------------商标列表第一类(50个)第二类(90个)........共45条-----------------------------基础链接类:引用下面的方法绑定到datalist上面,它调用一个存储过程:BrandTypeList_Count------------------------------------------------------------------privatevoidBindDataLast(){DataSetds=sql.RunProcedure("BrandTypeList_Count","BrandTypeList_Count");Dl_Brand_Type.DataSource=ds;Dl_Brand_Type.DataBind();if(ds.Tables[0].Rows.Count>0){for(inti=0;i<ds.Tables[0].Rows.Count;i++){HyperLinkhy=(HyperLink)Dl_Brand_Type.Items[i].FindControl("hl_title");Labellab_num=(Label)Dl_Brand_Type.Items[i].FindControl("lab_num");lab_num.Text="["+ds.Tables[0].Rows[i]["BrandCount"].ToString()+"]";hy.Text=ds.Tables[0].Rows[i]["type_names"].ToString();hy.ToolTip=ds.Tables[0].Rows[i]["type_depict"].ToString();hy.NavigateUrl="~/Brand/Brand_List.aspx?TypeId="+ds.Tables[0].Rows[i]["type_value"].ToString();hy.Style.Value="font-size:9pt";}}}-------------------------------------------------------------存储过程:BrandTypeList_Count-----------------------CREATEPROCEDUREdbo.BrandTypeList_Count/*(@parameter1int=5,@parameter2datatypeOUTPUT)*/AS/*SETNOCOUNTON*/SELECTBrandtype.type_names,Brandtype.type_value,COUNT(Brand.brand_id)ASBrandCount,Brandtype.type_depictFROMBrandINNERJOINBrandtypeONBrand.brand_cognizance=Brandtype.type_valueGROUPBYBrandtype.type_names,Brandtype.type_value,Brandtype.type_depictORDERBYBrandtype.type_valueRETURNGO----------------------------------------------------------
解决方案
解决方案二：
执行存储过程和数据库连接类usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Web;namespaceSuperMan{///<summary>///操作数据库///</summary>publicclassSqlHelper{privateSqlConnectionConn=newSqlConnection();privateSqlCommandCom=null;//protectedconststringDbServer=".";//服务器名//protectedconststringDbName="CMSB_CMS";//数据库名//protectedconststringDbUser="sa";//用户名//protectedconststringDbPwd="sa";//密码//protectedconststringDBConnStr=@"Server="+DbServer+""+";"+"InitialCatalog="+DbName+""+";"+"UserId="+DbUser+""+";"+"Password="+DbPwd+";";//使用web。config文件的数据库配置protectedstringDBConnStr=System.Configuration.ConfigurationManager.AppSettings["sql_conn"].ToString();publicSqlHelper(){CreatConn();}///<summary>///建立数据库链接///</summary>///<paramname="ConStr">数据连接字符串</param>publicvoidCreatConn(){try{Conn.ConnectionString=DBConnStr;}catch(Exceptione){thrownewException(e.Message);}finally{Conn.Open();}}///<summary>///打开数据库链接///</summary>protectedvoidConnOpen(){if(Conn.State==ConnectionState.Closed){Conn.Open();}}///<summary>///释放数据库连接///</summary>publicvoidDispose(){if(Conn!=null){if(Conn.State==ConnectionState.Open){Conn.Close();}}Conn.Dispose();if(Com!=null){Com.Dispose();}}///<summary>///返回数据库连接字符串///</summary>///<returns></returns>publicstringGetConStr(){returnDBConnStr;}#region操作储存过程///<summary>///执行命令(带返回值）///</summary>///<paramname="SqlStr"></param>///<returns></returns>publicobjectExecuteScalar(stringstoredProcName,IDataParameter[]parameters){objecti;try{ConnOpen();SqlCommandcommand=newSqlCommand(storedProcName,Conn);command.CommandType=CommandType.StoredProcedure;foreach(SqlParameterparameterinparameters){command.Parameters.Add(parameter);}i=command.ExecuteScalar();}catch(Exceptione){thrownewException(e.Message);}finally{Conn.Close();Com=null;}returni;}///<summary>///创建SqlCommand对象实例(用来返回一个整数值)///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<returns>SqlCommand对象实例</returns>publicSqlCommandBuildIntCommand(stringstoredProcName,IDataParameter[]parameters){SqlCommandcommand=BuildQueryCommand(storedProcName,parameters);command.Parameters.Add(newSqlParameter("ReturnValue",SqlDbType.Int,4,/*Size*/ParameterDirection.ReturnValue,false,/*isnullable*/0,/*byteprecision*/0,/*bytescale*/string.Empty,DataRowVersion.Default,null));returncommand;}
解决方案三：
///<summary>///构建SqlCommand对象(用来返回一个结果集，而不是一个整数值)///</summary>///<paramname="connection">数据库连接</param>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<returns>SqlCommand</returns>publicSqlCommandBuildQueryCommand(stringstoredProcName,IDataParameter[]parameters){SqlCommandcommand=newSqlCommand(storedProcName,Conn);command.CommandType=CommandType.StoredProcedure;foreach(SqlParameterparameterinparameters){command.Parameters.Add(parameter);}returncommand;}///<summary>///执行存储过程，返回影响的行数///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<paramname="rowsAffected">影响的行数</param>///<returns></returns>publicintRunProcedure(stringstoredProcName,IDataParameter[]parameters,outintrowsAffected){intresult;ConnOpen();SqlCommandcommand=BuildIntCommand(storedProcName,parameters);rowsAffected=command.ExecuteNonQuery();result=(int)command.Parameters["ReturnValue"].Value;command.Dispose();Conn.Close();returnresult;}///<summary>///执行存储过程///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<returns>SqlDataReader</returns>publicSqlDataReaderRunProcedure(stringstoredProcName,IDataParameter[]parameters){SqlDataReaderrd;ConnOpen();SqlCommandcommand=BuildQueryCommand(storedProcName,parameters);command.CommandType=CommandType.StoredProcedure;rd=command.ExecuteReader();command.Dispose();Conn.Close();Conn.Dispose();returnrd;}///<summary>///执行存储过程,返回DATASET///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<paramname="tableName">DataSet结果中的表名</param>///<returns>DataSet</returns>publicDataSetRunProcedure(stringstoredProcName,stringtableName){ConnOpen();DataSetdataSet=newDataSet();SqlDataAdaptersqlDA=newSqlDataAdapter(storedProcName,Conn);sqlDA.Fill(dataSet,tableName);sqlDA.Dispose();Conn.Close();Conn.Dispose();returndataSet;}///<summary>///执行存储过程，以DataSet返回数据集///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<paramname="tableName">DataSet结果中的表名</param>///<returns>DataSet</returns>publicDataSetRunProcedure(stringstoredProcName,IDataParameter[]parameters,stringtableName){DataSetdataSet=newDataSet();ConnOpen();SqlDataAdaptersqlDA=newSqlDataAdapter();sqlDA.SelectCommand=BuildQueryCommand(storedProcName,parameters);sqlDA.Fill(dataSet,tableName);sqlDA.Dispose();Conn.Close();Conn.Dispose();returndataSet;}///<summary>///执行存储过程（将数集填充至dataSet并返回）///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<paramname="dataSet">DataSet名</param>///<paramname="tableName">DataSet结果中的表名</param>publicvoidRunProcedure(stringstoredProcName,IDataParameter[]parameters,DataSetdataSet,stringtableName){ConnOpen();SqlDataAdaptersqlDA=newSqlDataAdapter();sqlDA.SelectCommand=BuildIntCommand(storedProcName,parameters);sqlDA.Fill(dataSet,tableName);sqlDA.Dispose();Conn.Close();Conn.Dispose();}///<summary>///执行存储过程(无返回值)///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>publicvoidRunComProcedure(stringstoredProcName,IDataParameter[]parameters){ConnOpen();SqlCommandcommand=BuildQueryCommand(storedProcName,parameters);command.CommandType=CommandType.StoredProcedure;command.ExecuteNonQuery();command.Dispose();Conn.Close();}///<summary>///执行存储过程,以DataTable返回数据集///</summary>///<paramname="storedProcName"></param>///<paramname="parameters"></param>///<paramname="tableName"></param>///<returns></returns>publicDataTableGetTable(stringstoredProcName,IDataParameter[]parameters,stringtableName){DataSetds=newDataSet();ConnOpen();SqlDataAdapterad=newSqlDataAdapter();ad.SelectCommand=BuildQueryCommand(storedProcName,parameters);Conn.Close();if(ad!=null){ad.Fill(ds,tableName);ad.Dispose();Conn.Close();if(ds.Tables[tableName].DefaultView.Count>0)returnds.Tables[tableName];elsereturnnull;}else{ds.Dispose();ad.Dispose();Conn.Close();returnnull;}}///<summary>///执行存储过程，返回TABLE///</summary>///<paramname="storedProcName">存储过程名</param>///<paramname="parameters">存储过程参数</param>///<paramname="tableName">DataSet结果中的表名</param>///<returns>DataSet</returns>publicDataTableGetTableProcedure(stringstoredProcName,stringtableName){ConnOpen();DataSetdataSet=newDataSet();SqlDataAdaptersqlDA=newSqlDataAdapter(storedProcName,Conn);sqlDA.Fill(dataSet,tableName);sqlDA.Dispose();Conn.Close();Conn.Dispose();returndataSet.Tables[tableName];}----------------------------------Conn.Close();都加上<appSettings><addkey="sql_conn"value="datasource=.;uid=sa;pwd=sa;database=CMSB_CMS;pooling=true"></add></appSettings>---------------------------------设置.不知道是使用母版页的问题,还是存储过程的问题.还是连接的问题头大了高手支招啊
解决方案四：
没人?
解决方案五：
不知道,顶!
解决方案六：
大家帮忙啊!!!!所有线程,那个存储过程最多,它是母版页内的datalist的
解决方案七：
jijiji~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
解决方案八：
用了母版页的页面刷新一次数据库中就多一个线程还是执行那个存储过程晕~~~~~~~