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.
INSERT INTO Product VALUES (1,'Laptop',1050.0, 105) INSERT INTO Product VALUES (2,'Netbook',540.0, 53) INSERT INTO Product VALUES (3,'Server',2000.0, 27) INSERT INTO Product VALUES (4,'IPhone',720.0, 309) INSERT INTO Product VALUES (5,'Tablet',830.0, 33) INSERT INTO [Order] VALUES (1,'2013-12-01',1,'Chris',1) INSERT INTO [Order] VALUES (2,'2012-06-30',2,'Xouan',3) INSERT INTO [Order] VALUES (3,'2013-04-19',3,'Helen',1) INSERT INTO [Order] VALUES (4,'2013-12-01',4,'Joy',4) INSERT INTO [Order] VALUES (5,'2013-07-13',4,'Maria',5) INSERT INTO [Order] VALUES (6,'2012-10-01',5,'Alex',2) INSERT INTO [Order] VALUES (7,'2013-11-03',5,'Nicol',7) INSERT INTO [Order] VALUES (8,'2012-06-01',1,'Smith',1) INSERT INTO [Order] VALUES (9,'2012-02-20',2,'Kristen',3)
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.
namespace AdoNetBatchQueries
{
class Program
{
public const string connString = "Data Source=.; Initial Catalog=ProductStore;Integrated Security = SSPI;";
static void Main(string[] args)
{
QuerySingleResultSet();
}
protected static void QuerySingleResultSet()
{
string selectStament = "SELECT * FROM Product";
SqlConnection conn = new SqlConnection(connString);
// Create the SELECT command, open the connection
SqlCommand cmd = new SqlCommand(selectStament, conn);
conn.Open();
// Create a DataReader object and read the results..
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine("Product ID: {0}\tName: {1}\tPrice: {2}\tUnits in Stock: {3}",
dr["ProductID"], dr["ProductName"], dr["ProductPrice"], dr["ProductUnitsInStock"]);
}
}
}
}
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.
protected static void QueryMultipleResultSets()
{
// The Batch Query
string selectStament = "SELECT * FROM Product WHERE ProductID=4; " +
"SELECT * FROM [Order] WHERE OrderProductID=4";
SqlConnection conn = new SqlConnection(connString);
// Create the SELECT command, open the connection
SqlCommand cmd = new SqlCommand(selectStament, conn);
conn.Open();
// Create a DataReader object and read the multiple result sets..
// using the NextResult function
using (SqlDataReader dr = cmd.ExecuteReader())
{
int resultSet = 0;
do
{
Console.WriteLine("Result Set: {0}", ++resultSet);
while (dr.Read())
{
for(int i=0; i<dr.FieldCount; i++) {
Console.Write(dr[i] + " ");
}
Console.WriteLine();
}
Console.WriteLine();
Console.WriteLine();
} while (dr.NextResult());
}
}
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.
protected static void QueryMultipleResultSetsParameterized()
{
// The Batch Query
string selectStament = "SELECT * FROM Product WHERE ProductID=@ProductID; " +
"SELECT * FROM [Order] WHERE OrderProductID=@OrderProductID";
SqlConnection conn = new SqlConnection(connString);
// Create the SELECT command, open the connection
SqlCommand cmd = new SqlCommand(selectStament, conn);
cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = 4;
cmd.Parameters.Add("@OrderProductID", SqlDbType.Int).Value = 4;
conn.Open();
// Create a DataReader object and read the multiple result sets..
// using the NextResult function
using (SqlDataReader dr = cmd.ExecuteReader())
{
int resultSet = 0;
do
{
Console.WriteLine("Result Set: {0}", ++resultSet);
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write(dr[i] + " ");
}
Console.WriteLine();
}
Console.WriteLine();
Console.WriteLine();
} while (dr.NextResult());
}
}
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.
protected static void QueryRelatedRecords()
{
string parentStatement = "SELECT * FROM Product";
string childStatement = "SELECT * FROM [Order]";
// Create the DataSet to fill and an SqlDataAdapter
DataSet ds = new DataSet();
// Fill Parent Table in the DataSet
SqlDataAdapter da = new SqlDataAdapter(parentStatement, connString);
da.Fill(ds, "Product");
// Fill Child Table in the DataSet
da = new SqlDataAdapter(childStatement, connString);
da.Fill(ds, "Order");
// Define the Relationship between the two Tables in the DataSet
DataRelation dr = new DataRelation("Product_Order",
ds.Tables["Product"].Columns["ProductID"],
ds.Tables["Order"].Columns["OrderProductID"]);
ds.Relations.Add(dr);
// Use the GetChildRows() to Get child (order) recodrs for a product
for (int i = 0; i < ds.Tables["Product"].Rows.Count;i++ )
{
DataRow productRow = ds.Tables["Product"].Rows[i];
Console.WriteLine("Product {0}, {1}, {2}", productRow[0], productRow[1], productRow[2]);
Console.WriteLine("Orders:");
foreach (DataRow childOrderRow in productRow.GetChildRows(dr))
{
Console.WriteLine("Order {0}, Date: {1}, Customer: {2}, Quantity {3}",
childOrderRow[0], childOrderRow[1], childOrderRow["OrderCustomer"], childOrderRow["OrderQuantity"]);
}
Console.WriteLine("---------------------------------------------------------------------------------");
}
}
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!
Categories: ADO.NET
chsakell's Blog 
Leave a Reply