As previously mentioned in the Overview, the TdCommandBuilder is responsible for generating a SQL statement for each DataRow in the DataTable that was modified, added, or removed. However, an application is able to override the command that is used to carry out the action.

An application has the option of defining a TdCommand so that it can control the DELETE, INSERT, and/or UPDATE SQL command that will be executed by the TdDataAdapter. A TdCommand must be created for each type of action that will be performed on the DataTable. The following properties are used to set the TdCommand that will be used for each of the actions:

If a TdCommand has been created for a specific action the TdDataAdapter will execute this command. The application is responsible for setting the TdCommand.CommandText property to the SQL statement that will be executed.

The setup of the parameters is also the responsibility of the application. The property TdParameter.SourceColumn is used to associate a TdParameter to a column of the DataTable.

Note
It is important that the TdParameter.Precision, TdParameter.Scale and/or TdParameter.Size properties of parameters be defined when they are applicable to the TdParameter.TdType. The provider requires that these properties be set to their maximum value (based upon the data in the column) when the provider uses Iterated Requests to send the commands to Teradata. The Data Provider will derive TdParameter properties from the DataRows that have been modified when an application does not explicitly set the properties. In some scenarios the Data Provider will send Multi-Statement Request instead of Iterated Request because the application did not set the TdParameter properties. For example The Data Provider will send Multi-Statement Requests to the Advanced SQL Engine when the application does not set Precision and Scale for a parameter of type Decimal (TdType.Decimal).

Note

It is important that the TdParameter.Precision, TdParameter.Scale and/or TdParameter.Size properties of parameters be defined when they are applicable to the TdParameter.TdType. The provider requires that these properties be set to their maximum value (based upon the data in the column) when the provider uses Iterated Requests to send the commands to Teradata.

The Data Provider will derive TdParameter properties from the DataRows that have been modified when an application does not explicitly set the properties. In some scenarios the Data Provider will send Multi-Statement Request instead of Iterated Request because the application did not set the TdParameter properties. For example The Data Provider will send Multi-Statement Requests to the Advanced SQL Engine when the application does not set Precision and Scale for a parameter of type Decimal (TdType.Decimal).

The following example shows the steps that are performed when specifying a custom insert command. The schema of the table used in this example is:

CREATE TABLE ExampleTable ( c1 INTEGER NOT NULL PRIMARY KEY, c2 DECIMAL(8, 2), c2 TIMESTAMP(6))

C#	Copy Code
public static void AdapterExample2(TdCommand cmd) { cmd.CommandText = "select c1, c2, c3 from ExampleTable"; TdDataAdapter da = new TdDataAdapter(cmd); // Provider specific types will be used in the data table da.ReturnProviderSpecificTypes = true; TdCommandBuilder cb = new TdCommandBuilder(da); DataTable dt = new DataTable("ExampleTable"); // Filling the data table with data retrieved from the select statement da.Fill(dt); // Creating the command that will be used to INSERT TdCommand insertCmd = cmd.Connection.CreateCommand(); insertCmd.CommandText = "INSERT INTO ExampleTable (c1, c2 ,c3) values (?, ?, ?)"; // Creating the parameters that corresponding to columns in the DataTable. // The columns of the DataTable will have the same name as that of the columns // of the Teradata table. TdParameter p1 = new TdParameter(); p1.SourceColumn = "c1"; p1.TdType = TdType.Integer; p1.IsNullable = false; // Specifying that the current data be used for this parameter p1.SourceVersion = DataRowVersion.Current; insertCmd.Parameters.Add(p1); TdParameter p2 = new TdParameter(); p2.SourceColumn = "c2"; p2.TdType = TdType.Decimal; // Must specify Precision and Scale for Decimal Data Type. p2.Precision = 8; p2.Scale = 2; p2.IsNullable = true; p2.SourceVersion = DataRowVersion.Current; insertCmd.Parameters.Add(p2); TdParameter p3 = new TdParameter(); p3.SourceColumn = "c3"; p3.TdType = TdType.Timestamp; // Must specify number of sub-seconds for Timestamp Data Type. p3.Scale = 6; p3.IsNullable = true; p3.SourceVersion = DataRowVersion.Current; insertCmd.Parameters.Add(p3); // Setting the DataAdapter InsertCommand property to the command that will be used to INSERT data. da.InsertCommand = insertCmd; // Creating the command that will be used to UPDATE TdCommand updateCmd = cmd.Connection.CreateCommand(); updateCmd.CommandText = "UPDATE ExampleTable SET c3 = ? where c1 = ?"; // Parameter associated with column that has been updated. // Only the data contained in "c3" will be modified p3 = new TdParameter(); p3.SourceColumn = "c3"; p3.TdType = TdType.Timestamp; p3.IsNullable = true; p3.SourceVersion = DataRowVersion.Current; updateCmd.Parameters.Add(p3); // Parameter that will be used in the WHERE clause of UPDATE. // "c1" is the primary key and will be used in the WHERE clause. p1 = new TdParameter(); p1.SourceColumn = "c1"; p1.TdType = TdType.Integer; p1.IsNullable = false; // Specifying that the original data be used for this parameter. // This is being specified even though the data has not be changed. p1.SourceVersion = DataRowVersion.Original; updateCmd.Parameters.Add(p1); // Setting UpdateCommand property to the command that will be used to UPDATE data da.UpdateCommand = updateCmd; // Adding rows to the data table DataRow dr = dt.NewRow(); dr["c1"] = 100; dr["c2"] = new TdDecimal(321.32); dr["c3"] = new TdTimestamp(2008, 10, 15, 12, 00, 00); dt.Rows.Add(dr); dr = dt.NewRow(); dr["c1"] = 101; dr["c2"] = new TdDecimal(543.21); dr["c3"] = new TdTimestamp(2008, 12, 31, 23, 01, 19, 900000, 6); dt.Rows.Add(dr); // Modifying column "c3" of row 1 dr = dt.Row[0]; dr["c3"] = new TdTimestamp(2010, 01, 31, 23, 54, 10, 100000, 6); // Adapter will determine how many statements will be batched da.UpdateBatchSize = 0; // Sending the updates to Teradata da.Update(dt); }

Copy Code

public static void AdapterExample2(TdCommand cmd)
{
    cmd.CommandText = "select c1, c2, c3 from ExampleTable";

    TdDataAdapter da = new TdDataAdapter(cmd);

    // Provider specific types will be used in the data table
    da.ReturnProviderSpecificTypes = true;

    TdCommandBuilder cb = new TdCommandBuilder(da);

    DataTable dt = new DataTable("ExampleTable");
    // Filling the data table with data retrieved from the select statement
    da.Fill(dt);

    // Creating the command that will be used to INSERT
    TdCommand insertCmd = cmd.Connection.CreateCommand();
    insertCmd.CommandText = "INSERT INTO ExampleTable (c1, c2 ,c3) values (?, ?, ?)";

    // Creating the parameters that corresponding to columns in the DataTable.
    // The columns of the DataTable will have the same name as that of the columns
    // of the Teradata table.

    TdParameter p1 = new TdParameter();
    p1.SourceColumn = "c1";
    p1.TdType = TdType.Integer;
    p1.IsNullable = false;
    // Specifying that the current data be used for this parameter
    p1.SourceVersion = DataRowVersion.Current;
    insertCmd.Parameters.Add(p1);

    TdParameter p2 = new TdParameter();
    p2.SourceColumn = "c2";
    p2.TdType = TdType.Decimal;
    // Must specify Precision and Scale for Decimal Data Type.
    p2.Precision = 8;
    p2.Scale = 2;
    p2.IsNullable = true;
    p2.SourceVersion = DataRowVersion.Current;
    insertCmd.Parameters.Add(p2);

    TdParameter p3 = new TdParameter();
    p3.SourceColumn = "c3";
    p3.TdType = TdType.Timestamp;
    // Must specify number of sub-seconds for Timestamp Data Type.
    p3.Scale = 6;
    p3.IsNullable = true;
    p3.SourceVersion = DataRowVersion.Current;
    insertCmd.Parameters.Add(p3);

    // Setting the DataAdapter InsertCommand property to the command that will be used to INSERT data.
    da.InsertCommand = insertCmd;


    // Creating the command that will be used to UPDATE
    TdCommand updateCmd = cmd.Connection.CreateCommand();
    updateCmd.CommandText = "UPDATE ExampleTable SET c3 = ? where c1 = ?";

    // Parameter associated with column that has been updated.
    // Only the data contained in "c3" will be modified
    p3 = new TdParameter();
    p3.SourceColumn = "c3";
    p3.TdType = TdType.Timestamp;
    p3.IsNullable = true;
    p3.SourceVersion = DataRowVersion.Current;
    updateCmd.Parameters.Add(p3);

    // Parameter that will be used in the WHERE clause of UPDATE.
    // "c1" is the primary key and will be used in the WHERE clause.
    p1 = new TdParameter();
    p1.SourceColumn = "c1";
    p1.TdType = TdType.Integer;
    p1.IsNullable = false;
    // Specifying that the original data be used for this parameter.
    // This is being specified even though the data has not be changed.
    p1.SourceVersion = DataRowVersion.Original;
    updateCmd.Parameters.Add(p1);

    // Setting UpdateCommand property to the command that will be used to UPDATE data
    da.UpdateCommand = updateCmd;


    // Adding rows to the data table
    DataRow dr = dt.NewRow();
    dr["c1"] = 100;
    dr["c2"] = new TdDecimal(321.32);
    dr["c3"] = new TdTimestamp(2008, 10, 15, 12, 00, 00);
    dt.Rows.Add(dr);

    dr = dt.NewRow();
    dr["c1"] = 101;
    dr["c2"] = new TdDecimal(543.21);
    dr["c3"] = new TdTimestamp(2008, 12, 31, 23, 01, 19, 900000, 6);
    dt.Rows.Add(dr);

    // Modifying column "c3" of row 1
    dr = dt.Row[0];
    dr["c3"] = new TdTimestamp(2010, 01, 31, 23, 54, 10, 100000, 6);


    // Adapter will determine how many statements will be batched
    da.UpdateBatchSize = 0;

    // Sending the updates to Teradata
    da.Update(dt);
}

See Also