Retrieve Data in Batches

DataStream

LANGUAGES: SQL | C#

ASP.NET VERSIONS:All

RetrieveData in Batches

Usethis SqlCommand Trick to Avoid Unnecessary Round Trips to Retrieve Data fromMultiple Tables

By BrianNoyes

A commonrequirement in .NET database development is to retrieve data for multipletables to either put into a data set or process through a data reader. If youget too accustomed to performing your data access based on the objects and codethat the designers in Visual Studio.NET provide for you, you may be causing unnecessaryround trips from the client to the server. In this article, I'll show you howto retrieve multiple sets of data in one query, and get them into theappropriate place in your dataset when your query returns.

ExecutingBatch Queries

Thefirst step to avoiding round trips is to realize that the SqlCommandobject is completely capable of executing multiple queries in a single roundtrip to the server. A common example of working with the SqlCommandclass to retrieve data looks something like this:

SqlCommand cmd= new SqlCommand("SELECT * FROMCustomers", connection);

Whatmany people don't realize, however, is that the following statement is equallylegal:

SqlCommand cmd= new SqlCommand(

  @"SELECT * FROM Customers; SELECT *FROM Orders", connection);

When youissue a batch query like this, only one round trip is made to the server. Thetwo queries are executed on the server, and two result sets are returned fromthe execution of the command. How you deal with those two result sets dependson whether you're working with a data set and data adapter, or if you'reworking with a data reader.

AddingMultiple Tables to a DataSet

Let'slook 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 acommand object behind the scenes to issue a SELECT command to get back the dataused to fill the data set, that command object can also use a batch query toreturn multiple sets of data that will each result in a separate table withinthe data set.

Considerthe code in Figure 1. As described before, when the data adapter executes thecommand object to fill the data set, only one round trip will be made to theserver. And that round trip will return multiple sets of data. The question is,what does the adapter do with the additional sets?

SqlConnectionconn = new SqlConnection(

  @"server=localhost;database=Northwind;trusted_connection=true");

SqlCommand cmd= new SqlCommand(

  "SELECT * FROM Customers; SELECT * FROMOrders",conn);

SqlDataAdapteradapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

adapter.Fill(ds);

Figure1: Executing abatch through a data adapter.

Whenevera data adapter retrieves a set of data in a Fill operation, it's going to create a table in the data set inwhich to place that data. By default, the adapter creates these tables with thenames Table, Table1, Table2, and so on. Typically, if you're retrieving asingle set of data, you override this naming convention by explicitly telling thedata adapter what to name the table it creates by using an overload of the Fillmethod:

adapter.Fill(ds,"Customers");

By doingthis with an adapter that contains a single select command retrieving data fromthe Customers table, it will create the table in the data set with the name setto "Customers" so that you could use that to index into the Tablescollection later:

DataTablecustomers = ds.Tables["Customers"];

So whatdo you do if you issue a batch query returning multiple sets of data through asingle adapter? The Fill method isn't capable of taking collections oftable names for each of the returned sets of data, so you're stuck withaccepting the default table names. So with the code shown in Figure 1 forexample, 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 withan untyped data set, it's not a big deal to patch the table names up afterexecuting the query, as shown here:

ds.Tables[0].TableName= "Customers";

ds.Tables[1].TableName= "Orders";

But whatif you're working with a typed dataset that already contains schemas forCustomers and Orders? This won't work with a typed dataset, because of whatwill happen when the data comes in. The data will be placed in the third andfourth tables, named Table and Table1 again, and there will be no easy way toget it into the Customers and Orders tables that pre-existed in the schema.

Thesolution to these problems for batched queries it to use the TableMappingscollection that exists on the data adapter. You add mappings to this collectionthat identify the default table names in which the data will be placed, and mapthose to the desired table names before calling the Fill method:

adapter.TableMappings.Add("Table","Customers");

adapter.TableMappings.Add("Table1","Orders");

adapter.Fill(ds);

Now whenFill pulls in the two result sets from the batched queries, it will putthem into the appropriate table within the data set based on the table name youprovide, instead of using the default table names.

BatchedQueries and DataReaders

Thestory for batched queries and data readers is much easier to understand,although, as usual, working with data readers results in more code to get atthe underlying results. When you execute a command that contains a batch query,you iterate through the first result set in the normal manner with datareaders. That is by calling the Read method repeatedly to position the"cursor" (using the term loosely) to the current record on the next availablerow, until Read returns false, indicating that there are no morerows to process in that result set.

However,because the reader contains more than one result set (assuming multiple SELECTqueries were batched when it was executed), the way you get to each subsequentresult set is by calling the NextResult method on the reader. When youcall this method, it will return true if there are more result sets toprocess, in which case you iterate through those result sets and access theircontents through the reader.

Finalize

Dealingwith 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 mightas well get them in one round trip. The techniques discussed in this articleapply equally well whether you're getting the result sets from a raw SQL textquery (as shown in the code snippets above), or from a stored procedure thatissues multiple SELECT statements.

BrianNoyes is asoftware architect with IDesign, Inc. (http://www.idesign.net),a .NET-focused architecture and design consulting firm. Brian is a MicrosoftMVP in ASP.NET who specializes in designing and building data-drivendistributed Windows and Web applications. Brian writes for a variety ofpublications and is working on a book for Addison-Wesley on building WindowsForms Data Applications with .NET 2.0. Contact him at mailto:[email protected].