Doing Database Operations In Batches

From Chapter 6: Disconnected Data via ADO.NET and DataSets of the book Extending MFC Applications with the .NET Framework.

This article is a follow-up to “Basic Database Operations with ADO.NET.”

There are times when it’s advantageous to perform certain operations in batch. For example, multiple read operations can be performed with a single Fill method, thereby causing the generation of more than one local DataTable while incurring the cost of only one round trip between the client and the server. In addition to that, this section will also illustrate how to turn off DataTable constraints so that multiple changes can be made to a DataRow in batch and then either all accepted or canceled with a single method call. Finally, we’ll also look at a way to turn index maintenance off to improve the performance associated with loading large amounts of data into a DataTable.

Quick Note on This Section’s Examples

This section’s code snippets are all freestanding functions that can be plugged directly into your own test applications.They make the sole assumption that the DataSet and CommandBuilder objects have all been properly contructed. For example, in an SDI applicatino you might declare each of these as member variables of the view and instantiate them (as illustrated in the section entitled “Contructing and Filling DataSet Objects”) in the view’s OnInitialUpdate function. This way, you can follow along, trying the various code snippets without having to see the same connection code repeasted over and over in each code example.

Creating Multiple DataTables in a DataSet

The principal benefits of using disconnected data are to prevent sustained client connections to the data source and to minimize round trips between client application(s) and the data server. To that end, there are times when it’s advisable to batch your reads or queries against the data source so that you can fill more than one DataTable with a single round trip. Take the following example, where I’m creating a DataSet object with two DataTable objects—one filled with records from the Employees table and one filled with records from the Products table.

  void MultipleRoundTrips()
  {
  #pragma push_macro("new")
  #undef new
    try
    {
      SqlConnection* conn =
        new SqlConnection(S"Server=localhost;"
                          S"Database=Northwind;"
                          S"Integrated Security=true;");
    // Construct the employees data adapter
    SqlDataAdapter* employeesAdapter =
    new SqlDataAdapter(S"SELECT * FROM Employees", conn);
    // Construct the products data adapter
    SqlDataAdapter* productsAdapter =
      new SqlDataAdapter(S"SELECT * FROM Products", conn);
    conn->Open();
    DataSet* dataset = new DataSet();
    // Get all the employees - FIRST TRIP TO SERVER
    employeesAdapter->Fill(dataset, S"AllEmployees");
    // Get all the products - SECOND TRIP TO SERVER
    productsAdapter->Fill(dataset, S"AllProducts");
    conn->Close();    // No longer needed
    DataTableCollection* tables = dataset->Tables;
    DataTable* table;
    for (int i = 0; i < tables->Count; i++)
    {
      table = tables->Item[i];
      Console::WriteLine(String::Format(S"{0} table has {1} rows",
                         table->TableName,
                         __box(table->Rows->Count)));
    }
  }
  catch(Exception* e)
  {
    Console::WriteLine(e->Message);
  }
#pragma pop_macro("new")
}

As the comments indicate, two round trips are being made here—one for each DataTable. In situations like this—where you have all the information you need to specify your SELECT statement—it’s more efficient to specify a SELECT statement in the data adapter’s constructor that will cause each table to be retrieved in single round trip. Simply delimiting each SELECT statement with a semicolon accomplishes this:

String* allEmployees = S"SELECT * FROM Employees";
String* allProducts = S"SELECT * FROM Products";
String* select = String::Format(S"{0};{1}", allEmployees,
allProducts);
SqlDataAdapter* adapter = new SqlDataAdapter(select, conn);

At this point, you might be wondering how to name the DataTable when using this technique. After all, the Fill method takes the name of a DataTable, but now we’re reading two tables. The following function answers that question:

void BatchRead()
{
#pragma push_macro("new")
#undef new
  try
  {
    SqlConnection* conn =
      new SqlConnection(S"Server=localhost;"
                        S"Database=Northwind;"
                        S"Integrated Security=true;");
    String* allEmployees = S"SELECT * FROM Employees";
    String* allProducts = S"SELECT * FROM Products";
    String* select = String::Format(S"{0};{1}",
                                    allEmployees,
                                    allProducts);
    // Read two tables at once to reduce round trips
    SqlDataAdapter* adapter = new SqlDataAdapter(select, conn);
    conn->Open();
    DataSet* dataset = new DataSet();
    // Fill dataset. Don't bother naming the table because
    // the second table has to be renamed anyway.
    adapter->Fill(dataset);
    conn->Close();    // No longer needed
    DataTableCollection* tables = dataset->Tables;
    DataTable* table;
    for (int i = 0; i < tables->Count; i++)
    {
      table = tables->Item[i];
      Console::WriteLine(table->TableName);
    }

    tables->Item[0]->TableName = S"AllEmployees";
    tables->Item[1]->TableName = S"AllProducts";>
    for (int i = 0; i < tables->Count; i++)
    {
      table = tables->Item[i];
      Console::WriteLine(String::Format(S"{0} table has {1} rows",
                         table->TableName,
                         __box(table->Rows->Count)));
    }
  }
  catch(Exception* e)
  {
    Console::WriteLine(e->Message);
  }
#pragma pop_macro("new")
}

This function produces the following output:

Table
Table1
AllEmployees table has 11 rows
AllProducts table has 77 rows

As mentioned earlier in the chapter, if you call the Fill method and do not specify a name for the DataTable that will be generated, it defaults to Table. If more than one DataTable is created, they are named Table1, Table2, and so on, as you can see in the first two lines of the output. However, what happens if you do specify a name? In this case, the first table gets the specified name and the remaining tables are simply named the same thing with a sequential numeric value (starting with 1) affixed to the end. Therefore, in order to have symbolic DataTable names, you’ll need to set the DataTable::TableName property manually after a read that returns more than one table. As the last two lines of the output indicate, the two DataTable objects were renamed, and their row counts reflect the fact that we did indeed perform two separate queries with one round trip.

More by Author

Must Read