C#使用SqlDataReader读取数据后怎么关闭Connection连接的完美解决方案
今天朋友的一个网站在并发量比较高的时候老是提示这个
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.
而且回收一下应用程序池就好了。但这不是长久之计，
我分析了一下感觉有可能是Sql连接没有关闭的问题
于是开始检查代码，还好他使用的是SqlHelper文件，这样就方便多了，
在这里我看到这样一个方法
代码如下
[C#] 纯文本查看 复制代码 /// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// 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
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
看上去是根据参数将查询结果生成一个SqlDataReader 返回给前台调用
大家有没有注意到这里的SqlConnection conn = new SqlConnection(connectionString);
这一句并没有显式的进行关闭conn
只有出现错误时才会关闭，这样时间一长，数据量并发一大，是肯定会出问题的。因为打开的conn 并没有关闭。
其实这里很简单在cmd.ExecuteReader();多加一个参数就OK了。
修改后的代码如下
[C#] 纯文本查看 复制代码 /// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// 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
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
最主要是加上这一句CommandBehavior.CloseConnection
意思是：
[C#] 纯文本查看 复制代码 在执行该命令时，如果关闭关联的 DataReader 对象，则关联的 Connection 对象也将关闭。
大家来看看CommandBehavior这个枚举吧
[C#] 纯文本查看 复制代码 #region 程序集 System.Data.dll, v2.0.50727
// C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll
#endregion
using System;
namespace System.Data
{
// 摘要:
// 提供对查询结果和查询对数据库的影响的说明。
[Flags]
public enum CommandBehavior
{
// 摘要:
// 此查询可能返回多个结果集。执行查询可能会影响数据库状态。Default 不设置 System.Data.CommandBehavior 标志，因此调用
// ExecuteReader(CommandBehavior.Default) 在功能上等效于调用 ExecuteReader()。
Default = 0,
//
// 摘要:
// 查询返回一个结果集。
SingleResult = 1,
//
// 摘要:
// 查询仅返回列信息。当使用 System.Data.CommandBehavior.SchemaOnly 时，用于 SQL Server 的 .NET
// Framework 数据提供程序将在要执行的语句前加上 SET FMTONLY ON。
SchemaOnly = 2,
//
// 摘要:
// 此查询返回列和主键信息。
KeyInfo = 4,
//
// 摘要:
// 查询应返回一行。执行查询可能会影响数据库的状态。一些 .NET Framework 数据提供程序可能（但不要求）使用此信息来优化命令的性能。用 System.Data.OleDb.OleDbCommand
// 对象的 System.Data.OleDb.OleDbCommand.ExecuteReader() 方法指定 System.Data.CommandBehavior.SingleRow
// 时，用于 OLE DB 的 .NET Framework 数据提供程序使用 OLE DB IRow 接口（如果可用）执行绑定。否则，它使用 IRowset
// 接口。如果您的 SQL 语句应该只返回一行，则指定 System.Data.CommandBehavior.SingleRow 还可以提高应用程序性能。在执行返回多个结果集的查询时，可以指定
// SingleRow。在这种情况下，仍返回多个结果集，但每个结果集只有一行。
SingleRow = 8,
//
// 摘要:
// 提供一种方法，以便 DataReader 处理包含带有大二进制值的列的行。SequentialAccess 不是加载整行，而是使 DataReader
// 将数据作为流来加载。然后可以使用 GetBytes 或 GetChars 方法来指定开始读取操作的字节位置以及正在返回的数据的有限的缓冲区大小。
SequentialAccess = 16,
//
// 摘要:
// 在执行该命令时，如果关闭关联的 DataReader 对象，则关联的 Connection 对象也将关闭。
CloseConnection = 32,
}
}
CommandBehavior.CloseConnection用于关闭数据库连接，这是肯定的，但它会不会一起把SqlDataReader也一起关闭了呢。也就是说，用了CommandBehavior.CloseConnection，是不是就不用再手动关闭SqlDataReader了。
我们中以使用SqlHelper,然后在前台网页里面进行测试
[C#] 纯文本查看 复制代码 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conr"].ToString());
conn.Open();
SqlCommand cmd = new SqlCommand("存储过程名称", conn);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
repeater1.DataSource = sdr;
repeater1.DataBind();
Response.Write(sdr.IsClosed.ToString()+"<br/>");
Response.Write(conn.State.ToString());
输出的结果是
True
Closed
说明SqlConnection和SqlDataReader都已经被关闭了。
如果把CommandBehavior.CloseConnection去掉，输出的结果则是：
False
Open
由此可见，使用了CommandBehavior.CloseConnection之后，读取完数据后，系统自动关闭了SqlDataReader和SqlConnection。听说当初微软弄出CommandBehavior.CloseConnection的目的，就是为了解决数据库的关闭问题的。
好了问题解决了，这点大家以后要注意啊，我已经更新了我的SqlHelper类请大家放心使用
| 
窥视卡
雷达卡
发表于 2013-8-15 10:59:50
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡
发表于 2013-8-15 11:02:44
楼主