CHAPTER 22 ADO.NET PART II: THE DISCONNECTED LAYER
set of parameter objects. Thus, if you were to take the long road, you could implement
ConfigureAdapter() to create three new SqlCommand objects manually, each of which contains a set of
SqlParameter objects. At this point, you could set each object to the UpdateCommand, DeleteCommand, and
InsertCommand properties of the adapter.
Visual Studio provides several designer tools to take care of this mundane and tedious code on your
behalf. These designers differ a bit based on which API you use (e.g., Windows Forms, WPF, or ASP.NET),
but their overall functionality is similar. You’ll see examples of using these designers throughout this
book, including some Windows Forms designers later in this chapter.
You won’t need to author the numerous code statements to configure a data adapter fully at this
time; instead, you can take a massive shortcut by implementing ConfigureAdapter() like this:
private void ConfigureAdapter(out SqlDataAdapter dAdapt)
{
// Create the adapter and set up the SelectCommand.
dAdapt = new SqlDataAdapter("Select * From Inventory", cnString);
// Obtain the remaining command objects dynamically at runtime
// using the SqlCommandBuilder.
SqlCommandBuilder builder = new SqlCommandBuilder(dAdapt);
}
To simplify the construction of data adapter objects, each of the Microsoft-supplied ADO.NET data
providers provides a command builder type. The SqlCommandBuilder automatically generates the values
contained within the SqlDataAdapter’s InsertCommand, UpdateCommand, and DeleteCommand properties,
based on the initial SelectCommand. The benefit here is that you do not need to build all the SqlCommand
and SqlParameter types by hand.
Here’s an obvious question at this point: how is a command builder able to build these SQL
command objects on the fly? The short answer is metadata. When you call the Update() method of a data
adapter at runtime, the related command builder will read the database’s schema data to autogenerate
the underlying insert, delete, and update command objects.
Obviously, doing so requires additional round trips to the remote database; this means it will hurt
performance if you use the SqlCommandBuilder numerous times in a single application. Here, you
minimize the negative effect by calling your ConfigureAdapter() method at the time the
InventoryDALDisLayer object is constructed, retaining the configured SqlDataAdapter for use throughout
the object’s lifetime.
In the previous code snippet, you did not use the command builder object (SqlCommandBuilder, in
this case) beyond passing in the data adapter object as a constructor parameter. As odd as this might
seem, this is all you must do (at a minimum). Under the hood, this type configures the data adapter with
the remaining command objects.
While you might love the idea of getting something for nothing, you should understand that
command builders come with some critical restrictions. Specifically, a command builder is only able to
autogenerate SQL commands for use by a data adapter if all of the following conditions are true:
•
The SQL SELECT command interacts with only a single table (e.g., no joins).
•
The single table has been attributed with a primary key.
•
The table must have a column or columns representing the primary key that you
include in your SQL SELECT statement.
Based on the way you constructed your AutoLot database, these restrictions pose no problem.
However, in a more industrial-strength database, you will need to consider whether this type is at all
893