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.