TdCommand Support Example

To enable this feature, TdCommand contains a new property that may be set to one of the defined values of GeneratedDataBehavior - IdentityColumn, AllColumns or None. The TdCommand property name is also named GeneratedDataBehavior.

The following code illustrates a simple program that enables generated data behavior, returning all column values from a multi-statement insert command. First a table with identity columns is created. The insert command is created and initialized with the insert statement. The insert command GeneratedDataBehavior property is set to AllColumns, which returns all column values from the insert command.

A TdDataReader is populated with the results of the command execution (ExecuteReader). Validation of the returned information is printed out by reading the results from the TdDataReader. After reading the results the TdDataReader is closed and the TdConnection is closed.

This application was executed once with the GeneratedDataBehavior set to AllColumns and once with GeneratedDataBehavior set to IdentityColumn.

C#	Copy Code
// sql create table statement String sqlCreateTable = @"create set table idColTbl " + @"(Custid integer, IdCol integer generated always as identity, " + @"CustName varchar(30), State char(2))"; // sql drop table statement String sqlDropTable = "Drop table idColTbl"; // sql insert statement String sqlInsert = @"Insert into idColTbl values (10,,'Customer1','OH');" + @"Insert into idColTbl values (20,,'Customer2','KY');"; TdConnection myConnection = new TdConnection ("Data Source= X; userid= Y; password= Z;"); myConnection.Open(); // create a new command to create the table and execute TdCommand myCommand = new TdCommand (sqlCreateTable, myConnection); myCommand.ExecuteNonQuery(); // create a new insert command and use the data reader to retrieve the results TdCommand myInsertCmd = new TdCommand (sqlInsert, myConnection); // set the generated data behavior myInsertCmd.GeneratedDataBehavior = GeneratedDataBehavior.IdentityColumn; TdDataReader reader = myInsertCmd.ExecuteReader(); Int32 currentRow = 1; // validate the results do { while (reader.Read()) { for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++) { Console.WriteLine ("Field [{0,4}] [{1,10}] = {2}\r\n", currentRow, reader.GetName (columnIndex), reader.GetValue(columnIndex)); } currentRow++; } } while (true == reader.NextResult()); // close the reader reader.Close (); // drop the test table TdCommand cmdDropTable = new TdCommand (sqlDropTable, myConnection); cmdDropTable.ExecuteNonQuery(); // close the connection myConnection.Close();

Copy Code

   // sql create table statement
   String sqlCreateTable = @"create set table idColTbl " +
      @"(Custid integer, IdCol integer generated always as identity, " +
      @"CustName varchar(30), State char(2))";

   // sql drop table statement
   String sqlDropTable = "Drop table idColTbl";

   // sql insert statement
   String sqlInsert =
      @"Insert into idColTbl values (10,,'Customer1','OH');" +
      @"Insert into idColTbl values (20,,'Customer2','KY');";

   TdConnection myConnection = new TdConnection ("Data Source= X; userid= Y; password= Z;");
   myConnection.Open();

   // create a new command to create the table and execute
   TdCommand myCommand = new TdCommand (sqlCreateTable, myConnection);
   myCommand.ExecuteNonQuery();

   // create a new insert command and use the data reader to retrieve the results
   TdCommand myInsertCmd = new TdCommand (sqlInsert, myConnection);

   // set the generated data behavior
   myInsertCmd.GeneratedDataBehavior = GeneratedDataBehavior.IdentityColumn;

   TdDataReader reader = myInsertCmd.ExecuteReader();
   Int32 currentRow = 1;

   // validate the results

   do
   {

      while (reader.Read())
      {
         for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
         {
            Console.WriteLine ("Field [{0,4}] [{1,10}] = {2}\r\n",
               currentRow, reader.GetName (columnIndex),
               reader.GetValue(columnIndex));

         }
         currentRow++;
      }
   } while (true == reader.NextResult());


   // close the reader
   reader.Close ();

   // drop the test table
   TdCommand cmdDropTable = new TdCommand (sqlDropTable, myConnection);
   cmdDropTable.ExecuteNonQuery();

   // close the connection
   myConnection.Close();

The results are reproduced below. A new value has been supplied for the column idCol from Teradata for both new insert statements.

     Field [ 1] [ Custid] = 10
     Field [ 1] [ IdCol] = 1
     Field [ 1] [ CustName] = Customer1
     Field [ 1] [ State] = OH

     Field [ 2] [ Custid] = 20
     Field [ 2] [ IdCol] = 2
     Field [ 2] [ CustName] = Customer2
     Field [ 2] [ State] = KY

A new value has been supplied for the column idCol from Teradata for both new insert statements. Changing the cmdInsertStatement GeneratedDataBehavior property to IdentityColumn results in the following output. Only the identity column value is returned in TdDataReader.

     Field [ 1] [ IdCol] = 1

     Field [ 2] [ IdCol] = 2