Navigation:  Advantage .NET Data Provider > AdsCommandBuilder Class >


Advantage .NET Data Provider

Previous pageReturn to chapter overviewNext page

Full name: Advantage.Data.Provider.AdsCommandBuilder





The AdsCommandBuilder provides a mechanism to automatically generate the SQL statements required to reconcile changes made to a DataSet with the underlying database for an AdsDataAdapter. The AdsCommandBuilder can be used for commands that result in live cursors (see Live versus Static Cursors.) In general, a live cursor is created by an SQL statement that is a single table SELECT without expressions in the select list.

Any statements that you do not set directly in the AdsDataAdapter will be generated by the AdsCommandBuilder after setting it. The AdsCommandBuilder registers itself as a listener for RowUpdating events whenever you set the DataAdapter property. You can only associate one AdsDataAdapter or AdsCommandBuilder object with each other at one time.

To generate INSERT, UPDATE, or DELETE statements, the AdsCommandBuilder uses the AdsDataAdapter.SelectCommand property to retrieve a required set of metadata automatically. If you change the SelectCommand after the metadata is retrieved (for example, after the first update), you should call the RefreshSchema method to update the metadata.

The SelectCommand should return at least one primary key or unique column. If none are present, an InvalidOperation exception is generated, and the commands are not generated. It is possible to bypass this default behavior and allow the commands to be generated without a primary key by setting the AdsCommandBuilder.RequirePrimaryKey property to False.

The AdsCommandBuilder also uses the AdsCommand.Connection, AdsCommand.CommandTimeout, and AdsCommand.Transaction properties referenced by the AdsDataAdapter.SelectCommand. The user should call AdsCommandBuilder.RefreshSchema if any of these properties are modified, or if the SelectCommand itself is replaced. Otherwise the AdsDataAdapter.InsertCommand, AdsDataAdapter.UpdateCommand, and AdsDataAdapter.DeleteCommand properties retain their previous values.

Note Using AdsCommandBuilder is less efficient (from a CPU perspective) than setting the update, insert, and delete commands excplicitly. It may require one or more extra round trips to the server to derive the information necessary for creating the SQL statements automatically. If processing speed is the most important factor in your application, you may want to use AdsCommandBuilder during development to extract the statements and parameter information and then use that to write the code to set that information explicitly.


AdsConnection conn = new AdsConnection( "data source = c:\\data;" );




AdsDataAdapter da = new AdsDataAdapter( "select * from departments",

conn );

AdsCommandBuilder cb = new AdsCommandBuilder( da );

DataSet ds = new DataSet();


da.Fill( ds, "test" );


// display the commands

Console.WriteLine( cb.GetInsertCommand().CommandText );

Console.WriteLine( cb.GetDeleteCommand().CommandText );

Console.WriteLine( cb.GetUpdateCommand().CommandText );


// add a row

DataRow newrow = ds.Tables["test"].NewRow();

newrow["Department Code"] = 125;

newrow["Department Name"] = "Virtual Reality";

newrow["Budget"] = (Decimal)5500000;

newrow["contact"] = 104;

ds.Tables["test"].Rows.Add( newrow );


// update an existing row

ds.Tables["test"].Rows[0]["Department Name"] = "Demolition";


// delete a row



// Send the update(s) to the server

da.Update( ds, "test" );