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”.
adonet-batch_01
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)

adonet-batch_02
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"]);
                }
            }
        }
    }

adonet-batch_03
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());
            }
        }

adonet-batch_04
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("---------------------------------------------------------------------------------");
            }
        }

adonet-batch_05
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.
adonet-batch_06
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!

Advertisements


Categories: ADO.NET

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chara Plessa

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!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

Sriramjithendra Nidumolu

Personal Notes of Sriramjithendra

%d bloggers like this: