Using the CommandBuilder Object to Generate Updating Logic

OleDbCommandBuilder Object Reference

Because this chapter introduces the ADO.NET CommandBuilder object, I think it will be helpful to review the properties and methods of the OleDbCommandBuilder object.

One aspect of the CommandBuilder object is particularly worth noting. The OleDbCommandBuilder and SqlCommandBuilder objects are not derived from the same base class. In fact, there is no base CommandBuilder class in the initial release of the ADO.NET object model.

Writing code to fetch the necessary metadata from the back end and convert this data into updating logic is not a simple task. If it were, the CommandBuilder object would be unnecessary. Because the code required to create a CommandBuilder class for a .NET data provider is far from trivial and because of the performance penalty associated with using CommandBuilder objects at run time, I wouldn't be surprised to see third-party .NET data providers that do not include a CommandBuilder class.

Properties of the OleDbCommandBuilder Object

Table 10-1 lists the properties of the OleDbCommandBuilder object.

Table 10-1 Properties of the OleDbCommandBuilder Object

Property	Data Type	Description
DataAdapter	DataAdapter	Returns the DataAdapter for which the CommandBuilder is generating updating logic
QuotePrefix	String	Contains the prefix the CommandBuilder will use when delimiting column and table names
QuoteSuffix	String	Contains the suffix the CommandBuilder will use when delimiting column and table names

DataAdapter

The CommandBuilder object's DataAdapter property allows you to examine or change the DataAdapter with which the CommandBuilder object is associated. You can also set this property in the CommandBuilder object's constructor.

QuotePrefix and QuoteSuffix

The CommandBuilder object's QuotePrefix and QuoteSuffix properties contain the strings that the CommandBuilder object will use to delimit the table and column names in the queries that it generates. By default, these properties contain empty strings.

Methods of the OleDbCommandBuilder Object

Table 10-2 lists the methods of the OleDbCommandBuilder object.

Table 10-2 Methods of the OleDbCommandBuilder Object

Method	Description
DeriveParameters	Retrieves parameter information for a Command that calls a stored procedure
GetDeleteCommand	Returns the Command that contains the logic for the DataAdapter object's DeleteCommand
GetInsertCommand	Returns the Command that contains the logic for the DataAdapter object's InsertCommand
GetUpdateCommand	Returns the Command that contains the logic for the DataAdapter object's UpdateCommand
RefreshSchema	Tells the CommandBuilder that it will need to regenerate its updating logic

DeriveParameters

The CommandBuilder object can do more than just generate updating logic for DataAdapter objects. You can also use a CommandBuilder to fetch parameter information for stored procedures. The following code snippet uses the CommandBuilder object's DeriveParameters method to retrieve and display parameter information for a stored procedure call:

Visual Basic .NET

Dim strConn As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Dim cmd As New OleDbCommand("CustOrdersOrders", cn) cmd.CommandType = CommandType.StoredProcedure Dim cb As New OleDbCommandBuilder() cn.Open() cb.DeriveParameters(cmd) cn.Close() Dim param As OleDbParameter For Each param In cmd.Parameters     Console.WriteLine(param.ParameterName)     Console.WriteLine(vbTab & param.Direction.ToString)     Console.WriteLine(vbTab & param.OleDbType.ToString)     Console.WriteLine() Next param

Visual C# .NET

string strConn; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); OleDbCommand cmd = new OleDbCommand("CustOrdersOrders", cn); cmd.CommandType = CommandType.StoredProcedure; OleDbCommandBuilder cb = new OleDbCommandBuilder(); cn.Open(); cb.DeriveParameters(cmd); cn.Close(); foreach (OleDbParameter param in cmd.Parameters) {     Console.WriteLine(param.ParameterName);     Console.WriteLine("\t" + param.Direction.ToString());     Console.WriteLine("\t" + param.OleDbType.ToString());     Console.WriteLine(); }

If you're trying to build the parameters collection for a command that calls a stored procedure but you're unsure of what value to set for the Size, Precision, and Size properties, you might want to use this type of code once at design time.

	In order for you to use the DeriveParameters method, the Connection for the supplied Command object must be open and available.

GetDeleteCommand, GetInsertCommand, and GetUpdateCommand

The CommandBuilder object's GetUpdateCommand, GetInsertCommand, and GetDeleteCommand methods let you examine the logic that the CommandBuilder generated.

These methods can also prove helpful at design time. You can create a CommandBuilder in code in a small sample application and then use these methods to display the CommandText and parameter information that the CommandBuilder generated. You can then use that same updating logic using the same query and parameter information in your code.

RefreshSchema

If you're changing the structure of your DataAdapter object's query in your application, you'll probably need to use the CommandBuilder object's RefreshSchema method.

The DataAdapter object does not fire an event when the CommandText property of its SelectCommand changes. Once the CommandBuilder object has generated your updating logic, as far as it knows, its work is done. If you've changed the structure of the DataAdapter object's query so that the CommandBuilder needs to regenerate the updating logic, you can call the RefreshSchema method of the CommandBuilder object.

Calling the RefreshSchema method does not force the CommandBuilder to regenerate its updating logic immediately. It simply sets a flag within the CommandBuilder to indicate that the current logic is no longer accurate. The CommandBuilder will regenerate the updating logic when you call the DataAdapter object's Update method or when you call one of the CommandBuilder object's Get<Update/Insert/Delete>Command methods.