LANGUAGES: SQL | C#
ASP.NET VERSIONS: All
Retrieve Data in Batches
Use this SqlCommand Trick to Avoid Unnecessary Round Trips to Retrieve Data from Multiple Tables
By Brian Noyes
A common requirement in .NET database development is to retrieve data for multiple tables to either put into a data set or process through a data reader. If you get too accustomed to performing your data access based on the objects and code that the designers in Visual Studio.NET provide for you, you may be causing unnecessary round trips from the client to the server. In this article, I'll show you how to retrieve multiple sets of data in one query, and get them into the appropriate place in your dataset when your query returns.
Executing Batch Queries
The first step to avoiding round trips is to realize that the SqlCommand object is completely capable of executing multiple queries in a single round trip to the server. A common example of working with the SqlCommand class to retrieve data looks something like this:
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", connection);
What many people don't realize, however, is that the following statement is equally legal:
SqlCommand cmd = new SqlCommand(
@"SELECT * FROM Customers; SELECT * FROM Orders", connection);
When you issue a batch query like this, only one round trip is made to the server. The two queries are executed on the server, and two result sets are returned from the execution of the command. How you deal with those two result sets depends on whether you're working with a data set and data adapter, or if you're working with a data reader.
Adding Multiple Tables to a DataSet
Let's look first at populating a data set with multiple tables. To fill a data set, you must always go through a data adapter. Because a data adapter uses a command object behind the scenes to issue a SELECT command to get back the data used to fill the data set, that command object can also use a batch query to return multiple sets of data that will each result in a separate table within the data set.
Consider the code in Figure 1. As described before, when the data adapter executes the command object to fill the data set, only one round trip will be made to the server. And that round trip will return multiple sets of data. The question is, what does the adapter do with the additional sets?
SqlConnection conn = new SqlConnection(
SqlCommand cmd = new SqlCommand(
"SELECT * FROM Customers; SELECT * FROM Orders",conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
Figure 1: Executing a batch through a data adapter.
Whenever a data adapter retrieves a set of data in a Fill operation, it's going to create a table in the data set in which to place that data. By default, the adapter creates these tables with the names Table, Table1, Table2, and so on. Typically, if you're retrieving a single set of data, you override this naming convention by explicitly telling the data adapter what to name the table it creates by using an overload of the Fill method:
By doing this with an adapter that contains a single select command retrieving data from the Customers table, it will create the table in the data set with the name set to "Customers" so that you could use that to index into the Tables collection later:
DataTable customers = ds.Tables["Customers"];
So what do you do if you issue a batch query returning multiple sets of data through a single adapter? The Fill method isn't capable of taking collections of table names for each of the returned sets of data, so you're stuck with accepting the default table names. So with the code shown in Figure 1 for example, you end up with two tables in the data set, named Table and Table1, which correspond to Customers and Orders respectively. If you are working with an untyped data set, it's not a big deal to patch the table names up after executing the query, as shown here:
ds.Tables.TableName = "Customers";
ds.Tables.TableName = "Orders";
But what if you're working with a typed dataset that already contains schemas for Customers and Orders? This won't work with a typed dataset, because of what will happen when the data comes in. The data will be placed in the third and fourth tables, named Table and Table1 again, and there will be no easy way to get it into the Customers and Orders tables that pre-existed in the schema.
The solution to these problems for batched queries it to use the TableMappings collection that exists on the data adapter. You add mappings to this collection that identify the default table names in which the data will be placed, and map those to the desired table names before calling the Fill method:
Now when Fill pulls in the two result sets from the batched queries, it will put them into the appropriate table within the data set based on the table name you provide, instead of using the default table names.
Batched Queries and DataReaders
The story for batched queries and data readers is much easier to understand, although, as usual, working with data readers results in more code to get at the underlying results. When you execute a command that contains a batch query, you iterate through the first result set in the normal manner with data readers. That is by calling the Read method repeatedly to position the "cursor" (using the term loosely) to the current record on the next available row, until Read returns false, indicating that there are no more rows to process in that result set.
However, because the reader contains more than one result set (assuming multiple SELECT queries were batched when it was executed), the way you get to each subsequent result set is by calling the NextResult method on the reader. When you call this method, it will return true if there are more result sets to process, in which case you iterate through those result sets and access their contents through the reader.
Dealing with batched queries is quite easy and can save you a round trip to the server. After all, if you're going to retrieve multiple result sets anyway, you might as well get them in one round trip. The techniques discussed in this article apply equally well whether you're getting the result sets from a raw SQL text query (as shown in the code snippets above), or from a stored procedure that issues multiple SELECT statements.
Brian Noyes is a software architect with IDesign, Inc. (http://www.idesign.net), a .NET-focused architecture and design consulting firm. Brian is a Microsoft MVP in ASP.NET who specializes in designing and building data-driven distributed Windows and Web applications. Brian writes for a variety of publications and is working on a book for Addison-Wesley on building Windows Forms Data Applications with .NET 2.0. Contact him at mailto:[email protected].