Batch queries, Multiple Result Sets and Parameterized Queries in ADO.NET

Requesting SQL Server for multiple results is a very common scenario while programming. Instead of asking for records with a single SELECT statement, you may find yourself want to retrieve multiple results in a single Query. For example, consider that you want to retrieve both information for a product and all it’s related orders. One solution would be to query the database for Product info with a SELECT statement:

SELECT * FROM [Product] WHERE ProductID=X

Then, make another call to get the related orders:

SELECT * FROM [Order] WHERE OrderProductID=X

This would cause you to query the database Server two times. Instead of doing so, you could send a single query having both the above SELECT statements and read the different results using pure ADO.NET. You could read the Multiple Result Sets using either an SqlDataReader or a SqlDataAdapter object. Moreover, you are always free to pass at run-time any parameters needed to your SQL queries. Let’s see how to do this.

First let’s create a simple Database named “ProductStore” with two tables, “Product” and “Order”.
The tables are related with a One-To-Many relationship (Primary Key – ProductID vs ForeignKey – OrderProductID). Run the following queries in order to create some records in both tables.

Now let’s switch to Visual Studio. Create a new Console Application project named AdoNetBatchQueries and make sure you add using statements for the System.Data and System.Data.SqlClient namespaces, in the Program.cs file. Let us start with a simple example and retrieve all the available products. We only need a single SELECT statement at the moment. Paste the following code in the Program.cs file.

This was quite simple, in fact we have seen it in another post on this blog. Now let’s create a Batch Query and read the multiple results using again an SqlDataReader object. We will request all information for the Product with ID=4 (IPhone) and all its related orders from the Order table. Call the following function in your main method.

The most important line of the above code is in line 30 where we call SqlDataReader’s NextResult function to read the next result set. By the time we move to the next result set, SqlDataReader’s Read() function is going to read the respective record. We know how many columns each Result Set has from the FieldCount property and that’s how we printed the results. In case you didn’t want to pass your parameters directly in your batch query, you could have used the SqlCommand’s Parameters collection as follow.

This is actually the recommended way to query your database server. Let’s try something more interesting now. Mind that there is a Foreign Key relationship between the two tables (“ProductID” column) and consider that you have loaded in memory, in a DataSet, both all the Product and Order records. In case you wanted to print every Product with its associated Order records you could use the DataRow.GetChildRecords(DataRelation dr) function for every Product DataRow. But first, you would have to create manually the Parent-Child relationship in your filled DataSet. Let’see how we can do this.

We have used two SqlDataAdapter objects, one to fill the Parent Table records (Product) and another to fill the child ones (Order). Notice that when we called the fill method, we passed an extra parameter (the source Table name). This way, the DataSet’s table names won’t have the default Table, Table1.. names but those we want.
Then we defined the DataRelation between the two DataTables and we added it to the DataSet’s Relations collection. This way, we were able to call the DataRow.GetChildRows(DataRelation) function for a Product record and print its orders.
That’s it, we saw how to iterate over Multiple Result Sets in ADO.NET, how to use Parameterized queries and how to retrieve related records From a DataSet. You can download the AdoNetBatchQueries project we created from here. I hope you enjoyed re post!

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!