How to use SqlDataReader if there are several select statements in a stored procedure

Question

I encoded three select statements in a stored procedure in Microsoft SQL Server 2005. Both select statements return multiple records and the list of tables for select statements is different. One selects records from the main table, and the other from the 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 this possible, or should I do something else.

Answer 1

You use the NextResult method in the datareader to navigate with multiple query results.

To iterate over all the data, you should do something like this:

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

Thus, in this case, as a background and provided that the main set of results will be the first, filling in the main and detailed objects can be performed as follows:

First create a master record dictionary:

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

Then go to the detailed records and add them to the appropriate wizard:

 reader.NextResult(); var masterIdOrdinal = reader.GetOrdinal("masterId"); while (reader.Read()) { var masterId = reader.GetInt32(masterIdOrdinal); var master = masters[masterId]; master.Details.Add(new Detail{....}); } 

Obviously, you should replace the column names with what you have in your data, and provide full initialization of the Master and Detail objects. If the result set of details is sorted by the main identifier, the last cycle can only be optimized in order to search for only one master from the dictionary. If the end points are small, then the gain will not be so large.

Answer 2

... one selection of records from the main table and others from the child.in C # code table I want to get all this record and put all this data in one object ...

Peter's solution helps solve the main problem of getting multiple results using one DataReader . However, if you want to store your data in an object that replicates the relationship between the Master-Details tables, you should use a DataSet instead.

DataSets can contain multiple DataTable and provide full support for the inherent relationship between tables, allowing you to create DataRelation between tables. You can then get related records for each script by calling GetChildRows() or GetParentRows() from the Master or Details tables, respectively.

There are probably many examples online that illustrate how to do this. Here is one discussion thread from my group, where I listed the steps and provided the code to demonstrate the procedure.