.NET超级问题~ 连接池和存储过程及类调用和母版页集合问题~~~

[myidc]

最近做一个网站,遇到下面问题,2-3台机器打开后,sql里面的连接数爆满~

错误信息:
----------------------
超时时间已到。超时时间已到，但是尚未从池中获取连接。出现这种情况可能是因为所有池连接均在使用，并且达到了最大池大小。
----------------------


使用了母版页,都是使用的存储过程,没有一个sql语句.大约7-8个页面使用同一个母版页,母版页很简单,头-中-下 都是空的.左边是一个datalist 显示下面样式的信息:

-----------------------------
商标列表
第一类(50个)
第二类(90个)
....
....
共45条
-----------------------------
基础链接类:
引用下面的方法绑定到datalist上面 ,它调用一个存储过程:BrandTypeList_Count
------------------------------------------------------------------
private void BindDataLast()
{
DataSet ds = sql.RunProcedure("BrandTypeList_Count", "BrandTypeList_Count");
Dl_Brand_Type.DataSource = ds;
Dl_Brand_Type.DataBind();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
HyperLink hy = (HyperLink)Dl_Brand_Type.Items[i].FindControl("hl_title");
Label lab_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

-----------------------
CREATE PROCEDURE dbo.BrandTypeList_Count
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
SELECT Brandtype.type_names, Brandtype.type_value, COUNT(Brand.brand_id)
AS BrandCount, Brandtype.type_depict
FROM Brand INNER JOIN
Brandtype ON Brand.brand_cognizance = Brandtype.type_value
GROUP BY Brandtype.type_names, Brandtype.type_value, Brandtype.type_depict
ORDER BY Brandtype.type_value
RETURN

GO
----------------------------------------------------------

[myidc]

执行存储过程和数据库连接类


using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace SuperMan
{
/// <summary>
/// 操作数据库
/// </summary>
public class SqlHelper
{
private SqlConnection Conn = new SqlConnection();
private SqlCommand Com = null;
//protected const string DbServer = "."; // 服务器名
//protected const string DbName = "CMSB_CMS"; // 数据库名
//protected const string DbUser = "sa"; // 用户名
//protected const string DbPwd = "sa"; // 密码
//protected const string DBConnStr = @"Server=" + DbServer + "" + ";" + "Initial Catalog=" + DbName + "" + ";" + "User Id=" + DbUser + "" + ";" + "Password=" + DbPwd + ";";

//使用web。config文件的数据库配置
protected string DBConnStr = System.Configuration.ConfigurationManager.AppSettings["sql_conn"].ToString();


public SqlHelper()
{
CreatConn();
}

///<summary>
///建立数据库链接
/// </summary>
///<param name="ConStr">数据连接字符串</param>
public void CreatConn()
{
try
{
Conn.ConnectionString=DBConnStr;
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
Conn.Open();
}

}

/// <summary>
/// 打开数据库链接
/// </summary>
protected void ConnOpen()
{
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
}


/// <summary>
/// 释放数据库连接
/// </summary>
public void Dispose()
{
if (Conn!=null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
Conn.Dispose();

if (Com!=null)
{
Com.Dispose();
}
}

/// <summary>
/// 返回数据库连接字符串
/// </summary>
/// <returns></returns>
public string GetConStr()
{
return DBConnStr;
}

#region 操作储存过程

/// <summary>
/// 执行命令(带返回值）
/// </summary>
/// <param name="SqlStr"></param>
/// <returns></returns>
public object ExecuteScalar(string storedProcName, IDataParameter[] parameters)
{
object i;

try
{ ConnOpen();
SqlCommand command = new SqlCommand( storedProcName, Conn );
command.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
i = command.ExecuteScalar();


}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
Conn.Close();
Com=null;
}

return i;
}


/// <summary>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand 对象实例</returns>
public SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand( storedProcName, parameters );

command.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,
4, /* Size */
ParameterDirection.ReturnValue,
false, /* is nullable */
0, /* byte precision */
0, /* byte scale */
string.Empty,
DataRowVersion.Default,
null ));

return command;
}

[myidc]

/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
public SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, Conn );
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}

/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
int result;
ConnOpen();
SqlCommand command = BuildIntCommand( storedProcName, parameters );
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
command.Dispose();
Conn.Close();
return result;
}

/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
SqlDataReader rd;
ConnOpen();
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
rd = command.ExecuteReader();
command.Dispose();
Conn.Close();
Conn.Dispose();
return rd;

}

/// <summary>
/// 执行存储过程,返回DATASET
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName,string tableName)
{
ConnOpen();
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDA = new SqlDataAdapter(storedProcName,Conn);
sqlDA.Fill( dataSet, tableName );
sqlDA.Dispose();
Conn.Close();
Conn.Dispose();
return dataSet;
}


/// <summary>
/// 执行存储过程，以DataSet返回数据集
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
DataSet dataSet = new DataSet();
ConnOpen();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
sqlDA.Dispose();
Conn.Close();
Conn.Dispose();
return dataSet;
}

/// <summary>
/// 执行存储过程（将数集填充至dataSet并返回）
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="dataSet">DataSet名</param>
/// <param name="tableName">DataSet结果中的表名</param>
public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName )
{
ConnOpen();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
sqlDA.Dispose();
Conn.Close();
Conn.Dispose();

}

/// <summary>
/// 执行存储过程(无返回值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
public void RunComProcedure(string storedProcName, IDataParameter[] parameters)
{
ConnOpen();
SqlCommand command = BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
command.Dispose();
Conn.Close();
}

/// <summary>
/// 执行存储过程,以DataTable返回数据集
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable GetTable(string storedProcName, IDataParameter[] parameters, string tableName)
{
DataSet ds = new DataSet();
ConnOpen();
SqlDataAdapter ad = new SqlDataAdapter();
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)
return ds.Tables[tableName];
else
return null;
}
else
{
ds.Dispose();
ad.Dispose();
Conn.Close();
return null;
}
}


/// <summary>
/// 执行存储过程，返回TABLE
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public DataTable GetTableProcedure(string storedProcName, string tableName)
{
ConnOpen();
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDA = new SqlDataAdapter(storedProcName, Conn);
sqlDA.Fill(dataSet, tableName);
sqlDA.Dispose();
Conn.Close();
Conn.Dispose();
return dataSet.Tables[tableName];
}



----------------------------------

Conn.Close();都加上

<appSettings>
<add key="sql_conn" value="data source=.;uid=sa;pwd=sa;database=CMSB_CMS;pooling=true"></add>
</appSettings>


---------------------------------

设置.不知道是使用母版页的问题,还是存储过程的问题 .还是连接的问题 头大了 高手支招啊

[myidc]

没人?

[潇湘墙外]

不知道,顶!

[myidc]

大家帮忙啊!!!!

所有线程,那个存储过程最多 ,它是母版页内的datalist的

[myidc]

jijiji~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

[myidc]

用了母版页的页面 刷新一次 数据库中就多一个线程 还是执行那个存储过程 晕~~~~~~~