CHAPTER 22 ADO.NET PART II: THE DISCONNECTED LAYER
Adding Disconnection Functionality to AutoLotDAL.dll
To illustrate the process of using a data adapter to push changes in a DataTable back to the database for
processing, you will now update the AutoLotDAL.dll assembly created back in Chapter 21 to include a
new namespace (named AutoLotDisconnectedLayer). This namespace contains a new class,
InventoryDALDisLayer, that uses a data adapter to interact with a DataTable.
A good way to begin is by copying the entire AutoLotDAL project folder you created in Chapter 21 to a
new location on your hard drive and rename this folder to AutoLotDAL (Version Two). Now use Visual
Studio to activate the File O pen Project/Solution... menu option, and then open the AutoLotDAL.sln
file in your AutoLotDAL (Version Two) folder.
Defining the Initial Class Type
Insert a new class named InventoryDALDisLayer using the Project Add Class menu option. Next,
ensure you have a public class type in your new code file. Change the name of the namespace wrapping
this class to AutoLotDisconnectedLayer and import the System.Data and System.Data.SqlClient
namespaces.
Unlike the connection-centric InventoryDAL type, this new class doesn’t need to provide custom
open/close methods because the data adapter handles the details automatically.
Begin by adding a custom constructor that sets a private string variable representing the
connection string. Also, define a private SqlDataAdapter member variable, which you configure by
calling a (yet to be created) helper method called ConfigureAdapter(), which takes a SqlDataAdapter
output parameter.
namespace AutoLotDisconnectedLayer
{
public class InventoryDALDisLayer
{
// Field data.
private string cnString = string.Empty;
private SqlDataAdapter dAdapt = null;
public InventoryDALDisLayer(string connectionString)
{
cnString = connectionString;
}
}
// Configure the SqlDataAdapter.
ConfigureAdapter(out dAdapt);
}
Configuring the Data Adapter Using the SqlCommandBuilder
When you use a data adapter to modify tables in a DataSet, the first order of business is to assign the
UpdateCommand, DeleteCommand, and InsertCommand properties with valid command objects (until you do
so, these properties return null references).
Configuring the command objects manually for the InsertCommand, UpdateCommand, and
DeleteCommand properties can entail a significant amount of code, especially if you use parameterized
queries. Recall from Chapter 21 that a parameterized query allows you to build a SQL statement using a
892