如果我在存储过程中有多个select语句，如何使用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.

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

2 个解决方案

#1

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

您可以在datareader上使用NextResult方法来导航查询中的多个结果。

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:

首先,建立一个Master记录的字典:

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{....});
}

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.

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

#2

...one select records from master table and other from child table .in c# code i want to get all this record and put all this data in one object...

...从主表中选择一个记录,从子表中选择其他记录。在c#代码中我希望得到所有这些记录并将所有这些数据放在一个对象中...

Peter's solution works to solve the basic problem of retrieving multiple results with a single DataReader. However, If you want to save your data to an object which replicates the relationship between the Master-Details tables, you should be using a DataSet instead.

Peter的解决方案可以解决使用单个DataReader检索多个结果的基本问题。但是,如果要将数据保存到复制主 - 详细信息表之间关系的对象,则应使用数据集。

DataSets can contain multiple DataTables and provide full support for inherent relationships between the tables by allowing creation of DataRelations between the tables. Then you can get related records for each scenario by calling GetChildRows() or GetParentRows() from the Master or Details tables respectively.

DataSet可以包含多个DataTable,并通过允许在表之间创建DataRelations,为表之间的固有关系提供完全支持。然后,您可以通过分别从Master或Details表中调用GetChildRows()或GetParentRows()来获取每个场景的相关记录。

There are probably many samples online that illustrate how to do this. Here's one discussion thread from my Group where I have listed the steps and provided some code to demonstrate the procedure.

网上可能有很多样本说明了如何做到这一点。这是我集团的一个讨论主题,我列出了这些步骤,并提供了一些代码来演示程序。

#1