如果存储过程中有多个选择语句，如何使用 SqlDataReader [英] How to use SqlDataReader if I'm having more than one select statements in a stored procedure

问题描述

我在 Microsoft SQL Server 2005 的存储过程中编写了三个 select 语句.两个 select 语句都返回多个记录，并且 select 语句的表列表是不同的.一个从主表中选择记录，另一个从子表中选择记录.在 C# 代码中，我想获取所有这些记录并将所有数据放在一个对象中.我正在使用 SqlDataReader.有没有可能，或者我应该做点别的.

I have coded three select statements in stored procedure in Microsoft SQL Server 2005. Both select statements return multiple number of records and table list for select statements is different. One select records from a master table and the other from a child table. In C# code I want to get all these records and put all the data in one object. I am using SqlDataReader. Is it possible with it or should i do something else.

推荐答案

您使用 数据读取器上的 NextResult 方法来导航查询的多个结果.

You use the NextResult method on the datareader to navigate with multiple results from a query.

要遍历所有数据，您可以执行以下操作:

To loop through all data you would do something like this:

var moreResults = true;
while (moreResults)
{
    while (reader.Read())
    {
    ...
    }
    moreResults = reader.NextResult();
}

因此，以它为背景，并假设主结果集在前，可以像这样填充主对象和细节对象:

So with that as a background, and assuming the master resultset comes first, populating master and detail objects could be done like this:

首先，建立一个主记录字典:

First, build up a dictionary of the Master records:

var masters = new Dictionary<int, Master>();

var idOrdinal = reader.GetOrdinal("id");
while (reader.Read())
{
    var id = reader.GetInt32(idOrdinal);
    masters.Add(id, new Master{Id=id, ....});
}

接下来，转到详细记录并将其添加到相应的主记录中:

Next, move on to detail records and add those to their corresponding master:

reader.NextResult();

var masterIdOrdinal = reader.GetOrdinal("masterId");
while (reader.Read())
{
    var masterId = reader.GetInt32(masterIdOrdinal);

    var master = masters[masterId];
    master.Details.Add(new Detail{....});
}

您显然应该用数据中的内容替换列名，并提供 Master 和 Detail 对象的完整初始化.如果详细结果集按主 ID 排序，则可以优化最后一个循环以仅从字典中查找每个主一次.如果结果集很小，收益不会那么大.

You should obviously replace column names with what you have in your data as well as supply the full initialization of Master and Detail objects. If the detail resultset is sorted on master id, the last loop could be optimized to only lookup each master once from the dictionary. If the resultsets are small though, the gain would not be that huge.

这篇关于如果存储过程中有多个选择语句，如何使用 SqlDataReader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！