DataReader 处理多个结果集—NextResult的用法-技术开发专区

DataReader 处理多个结果集—NextResult的用法

作者：ITPUB论坛编辑： nancy 2008-07-01 00:00

【IT168技术文档】

仔细查看您的数据库代码，看是否存在多次进入数据库的请求路径。每个这样的往返都会降低应用程序可以提供的每秒请求数量。通过在一个数据库请求中返回多个结果集，可以节省与数据库进行通信所需的总时间长度。同时因为减少了数据库服务器管理请求的工作，还会使得系统伸缩性更强。
简单示例如下：

一、返回多个数据集的存储过程

CREATE PROC Proc ---Multiple Resultsets
AS
SELECT * FROM Users
SELECT * FROM Users WHERE State = 'CA'
GO

二、取多个数据集的代码

String ConnString  = "User ID=sa;password=sa;Initial Catalog=pubs;Data Source=myServer";
SqlConnection Connection = new SqlConnection(myConnString);
SqlCommand Command = new SqlCommand();
SqlDataReader reader ;
Command.CommandType = CommandType.StoredProcedure;
Command.Connection = Connection;
Command.CommandText = "Proc";
int RecordCount=0;
try
{
Connection.Open();
reader = command.ExecuteReader();
int RecordCount=0;
// read the data from that resultset
    while (reader.Read())
{
RecordCount = RecordCount + 1;
}
Response.Write("Total number of Users:" + RecordCount.ToString());
// read the next resultset
    reader.NextResult();
RecordCount = 0;
// read the data from that second resultset
    while (reader.Read())
{
RecordCount = RecordCount + 1;
}
Response.Write("Total number of Users from California:" + RecordCount.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Connection.Close();
}

关注我们