This chapter is from the book
This chapter is from the book
This chapter is from the book
Working in Batch
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.
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.
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 DataRow 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 EndLoadData 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 DataTable from another data source—such as a text file. Listing 6-1 shows the partial listing of the rockets.txt file included on the book's CD for the BatchTableLoad demo application.
Listing 6-1 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")
}