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

// 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');";

// sql select statement
String sqlSelect = @"Select * from idColTbl";

// Teradata connection
TdConnection myConnection = new TdConnection ("Data Source= X; userid= Y; password= Z;");

try
{
   myConnection.Open();

   // create the table
   TdCommand myCommand = new TdCommand (sqlCreateTable, myConnection);
   myCommand.ExecuteNonQuery();

   // insert 2 rows
   TdCommand myInsertCmd = new TdCommand (sqlInsert, myConnection);
   myInsertCmd.ExecuteNonQuery();

   // create a TdDataAdapter
   // and set the commands manually
   TdDataAdapter custDA = new TdDataAdapter (sqlSelect, myConnection);
   custDA.InsertCommand = new TdCommand ("insert into idColTbl values (?,?,?,?)", myConnection);

   // create the parameters
   TdParameter parameter = new TdParameter();
   parameter.SourceColumn = "Custid";
   parameter.SourceVersion = DataRowVersion.Current;
   parameter.TdType = TdType.Integer;
   custDA.InsertCommand.Parameters.Add (parameter);

   parameter = new TdParameter();
   parameter.SourceColumn = "IdCol";
   parameter.SourceVersion = DataRowVersion.Current;
   parameter.TdType = TdType.Integer;
   custDA.InsertCommand.Parameters.Add (parameter);

   parameter = new TdParameter();
   parameter.SourceColumn = "CustName";
   parameter.SourceVersion = DataRowVersion.Current;
   parameter.TdType = TdType.VarChar;
   parameter.Size = 30;
   custDA.InsertCommand.Parameters.Add (parameter);

   parameter = new TdParameter();
   parameter.SourceColumn = "State";
   parameter.SourceVersion = DataRowVersion.Current;
   parameter.TdType = TdType.Char;
   parameter.Size = 2;
   custDA.InsertCommand.Parameters.Add (parameter);

   custDA.InsertCommand.GeneratedDataBehavior = GeneratedDataBehavior.IdentityColumn;
   // Set the UpdatedRowSource property since the default sets this to None
   custDA.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

   // create a new dataset
   DataSet custDS = new DataSet();

   // populate the DataSet from the select command
   custDA.Fill (custDS, "idColTbl");

   // Code to modify data in the DataSet here.
   DataRowCollection rows = custDS.Tables ["idColTbl"].Rows;
   DataColumnCollection columns = custDS.Tables ["idColTbl"].Columns;

   // we don't set the identity column here as it will be populated by
   // the generated data retrieval feature
   DataRow newRow = custDS.Tables ["idColTbl"].NewRow();
   newRow ["Custid"] = 30;
   newRow ["Custname"] = "NewCust1";
   newRow ["State"] = "CA";

   rows.Add newRow);
   newRow = custDS.Tables ["idColTbl"].NewRow();
   newRow ["Custid"] = 40;
   newRow ["Custname"] = "NewCust2";
   newRow ["State"] = "CA";

   // add the rows to the DataTable
   rows.Add (newRow);

   // the new rows will be added to the Advanced SQL Engine and the identity columns
   // will be returned by the SQL Engine
   int rowsPacted = custDA.Update (custDS, "idColTbl");

   int i = 0;

   // print out the values to confirm the updated identity columns
   foreach (DataRow selRow in rows)
   {
      Console.Write ("Row {0}", i++);
      foreach (DataColumn selCols in columns)
      {
         Console.Write(" {0} = {1}", selCols.ColumnName, selRow[selCols]);
      }
      Console.WriteLine();
   }

   // drop the table
   TdCommand cmdDropTable = new TdCommand (sqlDropTable, myConnection);
   cmdDropTable.ExecuteNonQuery();
}
catch (TdException e)
{
   Console.WriteLine ("Error {0} - {1}", e.ErrorCode, e.Message);

   TdCommand cmdDropTable = new TdCommand (sqlDropTable, myConnection);
   cmdDropTable.ExecuteNonQuery();

   throw;
}
finally
{
   // close the connection
   myConnection.Close();
}