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.

Doing Database Operations In Batches

Batch Row Changes

The term "batch row changes" or "batch row updates" can be a bit misleading; it simply refers to the ability to change multiple properties (including column values) of a DataRow object before committing those changes. This can be extremely useful in several scenarios. For example, because of certain constraints on a given table, you might need to make two or more changes to a row before the row is valid, yet the first change results in an exception because you are in violation of the constraint. Let's say you're working with the Employees table and you want to store either a location to the employee's picture (such as a URL) or the actual binary data that comprises the picture. Since these are two drastically different data types (string and binary, respectively) you could create a column for each possibility and set up a constraint that states that one of the columns must have a value and the other must be null—your standard XOR situation. Now let's say that you add a record with the picture data column set and the picture location column null, but later want to reverse that. Because of the aforementioned constraint, this would be extremely difficult.

As another practical example of needing to process multiple row changes in parallel, or batch, suppose you had a wizard-like interface that allowed the user to modify different columns of a row through a series of dialogs. As the user might cancel the operation at any point, you would need the ability to reverse the changes the user did make to the DataRow.

Both of these scenarios can be handled very simply by using the Data Row class's BeginEdit, EndEdit, and CancelEdit methods.

When the BeginEdit method is called, all events are temporarily suspended for the row so that any changes made do not trigger validation rules (constraints). Calling EndEdit then commits these changes and brings back into play any events and constraints defined for the table/row. If you decide that you do not want to commit the changes, you can simply call the CancelEdit method and any changes made to the row since the BeginEdit method was called will be lost. Using the picture example from above, the code to change the values could now be written as follows:

// row is a DataRow having a column for picture data,
// which is currently set and a column for picture location,
// which needs to be set - all without breaking the constraint
// that one of these columns needs to be null at all times
// Start the edit process - turning off constraints
row->BeginEdit();
// Make changes row->Item[S"PictureData"] = DBNull::Value;
row->Item[S"PictureLocation"] = S"http://somelocation.com";
// Commit the changes
row->EndEdit();

Batch Table Updates

Much as the DataRow class allows you to suspend validation rules in order to perform certain operations in batch, the DataTable also provides the same capability at the table level, using the BeginLoadData and EndLoad Data method pair. In fact, because we are talking about the table level, index maintenance is also suspended, providing a much faster means of loading already validated data into a DataTable. In fact, my own benchmarking—using some of the performance and benchmarking techniques discussed in Chapter 10—indicate up to a 30% increase in speed simply by calling BeginLoadData, loading the rows via LoadDataRow, and then calling EndLoadData when finished!

As an example of a realistic situation where you'd want to use these methods, let's say you were loading a large amount of data into a Data Table from another data source—such as a text file. Listing 61 shows the partial listing of the rockets.txt file included on the book's CD for the BatchTableLoad demo application.

Listing 61 Partial listing of sample comma-delimited text file (rockets.txt)

"Yao","Ming",13.5,"Dynasty"
"Steve","Francis",21.0,"Franchise"
"Cuttino","Mobley",17.5,"Cat"
...

Now let's see how we could read this data and batch load it into a DataTable.

void BatchLoad()
{
#pragma push_macro("new")
#undef new
  try
  {
    // Define a DataTable
    DataSet* dataset = new DataSet();
    DataTable* table = new DataTable(S"Players");
    dataset->Tables->Add(table);
    // Define the table's columns
    table->Columns->Add(S"FirstName", __typeof(String));
    table->Columns->Add(S"LastName", __typeof(String));
    table->Columns->Add(S"PointsPerGame", __typeof(Double));
    table->Columns->Add(S"Nickname", __typeof(String));
    // Open the csv file and read entire file into a String object
    StreamReader* reader = new StreamReader(S"rockets.txt");
    String* rocketStats = reader->ReadToEnd();
    reader->Close();
    String* pattern = S"\"(?<fname>[^\"]+)\","
                      S"\"(?<lname>[^\"]+)\","
                      S"(?<ppg>[^,]+),"
                      S"\"(?<nickname>[^\"]+)\"";
    Regex* rx = new Regex(pattern);
    // Disable notifications, constraints, and index maintenance
    table->BeginLoadData();
    // for all the matches
    for (Match* match = rx->Match(rocketStats);
         match->Success;
         match = match->NextMatch())
    {
      Object* columns[] = new Object*[table->Columns->Count];
        GroupCollection* groups = match->Groups;
      columns[0] = groups->Item[S"fname"]->Value;
      columns[1] = groups->Item[S"lname"]->Value;
      columns[2] = groups->Item[S"ppg"]->Value;
      columns[3] = groups->Item[S"nickname"]->Value;
      // Load all fields with one call
      table->LoadDataRow(columns, true);
    }

    // Re-enable notifications, constraints, and index maintenance
    table->EndLoadData();
  }
  catch(Exception* e)
  {
    Console::WriteLine(e->Message);
  }
#pragma pop_macro("new")
}

Once the DataTable is constructed and its schema defined, the text file is read using the StreamReader class covered in Chapter 3. Specifically, the ReadToEnd method is called so that the entire file is read into a String object. At that point, a regular expression is used, in which each Match object represents a row in the text file and each named Group object within the Match object represents a column of that row. (Regular expressions, matches and groups are covered in Chapter 2.)

Now that we have the data in a format that can be easily enumerated, the function calls BeginLoadData to disable notifications, constraints, and index maintenance while the rows are loaded into the DataTable. This loading is done via a call to the LoadDataRow method. As you can see, the first parameter to this method is an array of objects, each of which represents a column. The second parameter is a Boolean value that indicates if the DataTable::AcceptChanges should be called after the table is loaded.

There are also a couple of other things you should note here. First, if a column in the DataRow is auto-generated, or if you want the default value used, you should simply set the object corresponding to that column to System::Object::Empty. However, keep in mind that the new row will only be appended to the DataTable if the key field does not already exist. If you're attempting to add a row with a duplicate primary key, the first row will be overwritten with the second. Therefore, while I didn't use a primary key in this example in order to keep things simple (and since I've already shown how to use primary keys in previous examples), you should take care to avoid duplicate keys. Finally, when the data has been loaded, a call to EndLoadData re-enables the DataTable object's notifications, constraints, and index maintenance.

While the batch loading of records is the most common use for the BeginLoadData and EndLoadData methods, they are also used to handle another common problem—that of swapping the primary keys of two rows. In fact, I've seen numerous times on various newsgroups that someone has attempted to use the DataRow class's BeginEdit and EndEdit methods to accomplish this to no avail.

// Disable constraint checking for both rows
row1->BeginEdit();
row2->BeginEdit();

Int32 temp = *dynamic_cast<__box int*>(row1->Item[S"ID"]);
row1->Item[S"ID"] = row2->Item[S"ID"];
row2->Item[S"ID"] = __box(temp);
row1->EndEdit();    // ERROR! - WILL FAIL HERE!
row2->EndEdit();

As the comment indicates, this technique will fail. The calls to the BeginEdit method for the two DataRow objects result in the disabling of constraints for the respective rows, as we want. However, once EndEdit is called for the first DataRow, the primary key value for that first row conflicts with the primary key value for the not-yet-committed second DataRow object. Once again, the BeginLoadData and EndLoadData methods save the day, as illustrated in the following example:

void SwapPrimaryKeyValues()
{
#pragma push_macro("new")
#undef new
  try
  {
    DataSet* dataset = new DataSet();
    DataTable* table = new DataTable(S"Players");
    dataset->Tables->Add(table);
    table->Columns->Add(S"ID", __typeof(Int32));
    table->Columns->Add(S"Name", __typeof(String));
    DataColumn* primaryKeys[] = new DataColumn*[1];
    primaryKeys[0] = table->Columns->Item[0];
    table->PrimaryKey = primaryKeys;
    // Create row #1
    DataRow* row1 = table->NewRow();
    row1->Item[S"ID"] = __box(1);
    row1->Item[S"Name"] = S"Foo";
    table->Rows->Add(row1);
    // Create row #2
    DataRow* row2 = table->NewRow();
    row2->Item[S"ID"] = __box(2);
    row2->Item[S"Name"] = S"Bar";
    table->Rows->Add(row2);
    // Turn off notifications, constraints, and
    // index maintenance.
    table->BeginLoadData();
    // Pull the primary key switch-a-roo while nobody's
    // watching.
    Int32 temp = *dynamic_cast<__box int*>(row1->Item[S"ID"]);
    row1->Item[S"ID"] = row2->Item[S"ID"];
    row2->Item[S"ID"] = __box(temp);
    // Turn back on notifications, constraints, and
    // index maintenance.
    table->EndLoadData();
  }
  catch(Exception* e)
  {
    Console::WriteLine(e->Message);
  }
#pragma pop_macro("new")
}

[032117352X.jpg]

Extending MFC Applications with the .NET Framework
By Tom Archer and Nishant Sivakumar
ISBN: 032117352X
Addison-Wesley



About the Author

Tom Archer - MSFT

I am a Program Manager and Content Strategist for the Microsoft MSDN Online team managing the Windows Vista and Visual C++ developer centers. Before being employed at Microsoft, I was awarded MVP status for the Visual C++ product. A 20+ year veteran of programming with various languages - C++, C, Assembler, RPG III/400, PL/I, etc. - I've also written many technical books (Inside C#, Extending MFC Applications with the .NET Framework, Visual C++.NET Bible, etc.) and 100+ online articles.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • IBM Worklight is a mobile application development platform that lets you extend your business to mobile devices. It is designed to provide an open, comprehensive platform to build, run and manage HTML5, hybrid and native mobile apps.

  • In this webinar, IDC featured speaker Steve Conway, Vice President of High Performance Computing, will present an update on the global x86 HPC cluster market. The presentation will include IDC's five-year forecast for the medium- to large-scale technical computing and data analysis emerging markets by systems, processors and application middleware. Cray's featured speaker, John Lee, Vice President of Cray Cluster Advanced Technology Systems, will present the new Cray® CS400™ cluster series based on …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds