How to use SqlDataReader if I'm having more than one select statements in a stored procedure

Question

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.

Answer 1

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

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.

Answer 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...

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.

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.

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.