Setting Up the TdDataAdapter

The following are guidelines that will enable the Data Provider to efficiently process batches:

• An application should attempt to prevent rows from being added to, delete from, or modified in a DataTable that causes the the Advanced SQL Engine to generate an error when the data of the row is used to update the corresponding SQL table. When the Data Provider is unable to use Statement Independence to process a batch it will have to remove the row from the batch and then re-submit the batch to the SQL Engine. This significantly increases the overhead.
• The following recommended property settings will enable the Data Provider to specify that the SQL Engine is to use Parameter Arrays to process the batch:
  • DataAdapter.ContinueUpdateOnError property to true. This enables the Data Provider to process all the batches. The rows in the DataTable that updated the corresponding SQL table successfullywill have have their DataRow.RowState property set to DataRowState.Unchanged. The RowState of the rows that failed to update the corresponding table will be set to the initial state that was set when the DataTable row was inserted, deleted, or modified: DataRowState.Added, DataRowState.Deleted, DataRowState.Modified.
  • TdDataAdapter.UpdateBatchSize to a value of 5 or greater so that Parameter Arrays are used.
  • TdDataAdapter.KeepCommandSequence to false. This will enable the Data Provider to organize the rows into groups that are to be INSERTED, used to UPDATE an existing row in a Table, or used to DELETE a row in a Table.
• Starting an Explicit Transaction When Batch Updating
  • It is recommened that the ANSI Session Mode (TdConnectionStringBuilder.SessionMode) be used when transaction processing is to be used to perform batch updating.
  • When the Session Mode is set to Teradata unexpected behaviors can occur while processing a batch. Refer to Behavior When Batch Updating with Different Settings for more information.

Verifying Updates

After TdDataAdapter.Update returns, the application will have to check the rows in the DataTable to verify that the corresponding SQL table was updated. The following need to be performed by the application:

1. Check for Errors

   Check the DataRow.RowError property. If the row was successfully processed this property will be an empty string. When this occurs, verify that the DataRow.RowState property is set to DataRow.Unchanged.

   When the SQL Engine generates an error while processing a row, the DataRow.Error property will contain the error message. The DataRow.RowState will not have been changed. These rows need to be fixed and the TdDataAdapter.Update called again so that the corresponding SQL table can be updated with these rows.

2. Explicit Transactions Started

   The RowStatus and RowError properties will not show that the rows in the DataTable were rolled back when TdTransaction.Rollback is called to rollback the changes that were applied by TdDataAdapter.Update. For example, if rows in the DataTable were successfully applied to the corresponding SQL table, the RowState will be set to DataRowState.Unchanged. The state will not revert back to the original value when TdTransaction.Rollback is called.

   Refer to Behavior When Batch Processing with Different Settings

Other Items that Affect Batch Processing

The following items can affect the behavior of DataAdapter.Update:

• Session Mode (TdConnectionStringBuilder.SessionMode).

  There is a difference in the Transaction Processing when the Session Mode is to Teradata versus ANSI.

• DML statement that is being processed.

  Batches that contains INSERT statements will be processed by the SQL Engine using Statement Independence. Using this feature enables the Data Provider to more efficiently process the batch. The SQL Engine cannot process DELETE and UPDATE statements using Statement Independence. Processing of batches that contain these statement are less efficient.

• TdDataAdapter.ContinueUpdateOnError

  Indicates whether processing of batches will continue when an error is encountered.

• TdDataAdapter.UpdateBatchSize

  The Data Provider will use the size to determine whether to use Parameter Arrays (Iterated Request) or a multi-statement request to send the rows of the DataTable to the SQL Engine. The Data Provider will use Parameter Arrays to process a batch if the value is 5 or greater.

• Explicit Transaction (TdTransaction)

  The transaction processing is slightly different depending upon the type of DML that is being processed and the session mode. See Detailed Behavior of TdDataAdapter.Update for more information.

See Also

Working with Batch Size

Continue Processing When Error Occur in Batch

TdTransaction

How Statement Independence Affects Batch Update

Behavior When Batch Processing with Different Settings