ASA Programming Guide
  Developing Applications with the .NET Data Provider
    Accessing and manipulating data

Obtaining primary key values

If the table you are updating has an autoincremented primary key, uses UUIDs, or if the primary key comes from a primary key pool, you can use a stored procedure to obtain values generated by the data source.

When using the AsaDataAdapter, this technique can be used to fill the columns in the DataSet with the primary key values generated by the data source. If you want to use this technique with the AsaCommand object, you can either get the key columns from the parameters or reopen the DataReader.

Examples 

The following examples use a table called adodotnet_primarykey that contains two columns, id and name. The primary key for the table is id. It is an INTEGER and contains an autoincremented value. The name column is CHAR(40).

These examples call the following stored procedure to retrieve the autoincremented primary key value from the database.

CREATE PROCEDURE sp_adodotnet_primarykey( out p_id int, in p_name char(40) )
BEGIN
    INSERT INTO adodotnet_primarykey( name ) VALUES(
      p_name );
    SELECT @@IDENTITY INTO p_id;
END

To insert a new row with an autoincremented primary key using the AsaCommand object

1. Connect to the database.

   AsaConnection   conn = OpenConnection();

2. Create a new AsaCommand object to insert new rows into the DataTable. In the following code, the line int id1 = ( int ) parmId.Value; verifies the primary key value of the row.

   AsaCommand      cmd = conn.CreateCommand();
   cmd.CommandText = "sp_adodotnet_primarykey";
   cmd.CommandType = CommandType.StoredProcedure;
   AsaParameter parmId = new AsaParameter();
   parmId.AsaDbType = AsaDbType.Integer;
   parmId.Direction = ParameterDirection.Output;
   cmd.Parameters.Add( parmId );
   AsaParameter parmName = new AsaParameter();
   parmName.AsaDbType = AsaDbType.Char;
   parmName.Direction = ParameterDirection.Input;
   cmd.Parameters.Add( parmName );
   parmName.Value = "R & D --- Command";
   cmd.ExecuteNonQuery();
   int id1 = ( int ) parmId.Value;
   parmName.Value = "Marketing --- Command";
   cmd.ExecuteNonQuery();
   int id2 = ( int ) parmId.Value;
   parmName.Value = "Sales --- Command";
   cmd.ExecuteNonQuery();
   int id3 = ( int ) parmId.Value;
   parmName.Value = "Shipping --- Command";
   cmd.ExecuteNonQuery();
   int id4 = ( int ) parmId.Value;

3. Bind the results to the grid on the screen and apply the changes to the database.

   cmd.CommandText = "select * from " +
       adodotnet_primarykey";
   cmd.CommandType = CommandType.Text;
   AsaDataReader dr = cmd.ExecuteReader();
   dataGrid.DataSource = dr;

4. Close the connection.

   conn.Close();

To insert a new row with an autoincremented primary key using the AsaDataAdapter object

1. Create a new AsaDataAdapter.

   DataSet         dataSet = new DataSet();
   AsaConnection   conn = OpenConnection();
   AsaDataAdapter  adapter = new AsaDataAdapter();
   adapter.MissingMappingAction =
       MissingMappingAction.Passthrough;
   adapter.MissingSchemaAction =
       MissingSchemaAction.AddWithKey;

2. Fill the data and schema of the DataSet. The SelectCommand is called by the AsaDataAdapter.Fill method to do this. You can also create the DataSet manually without using the Fill method and SelectCommand if you do not need the existing records.

   adapter.SelectCommand = new AsaCommand( "select * from  + adodotnet_primarykey", conn );

3. Create a new AsaCommand to obtain the primary key values from the database.

   adapter.InsertCommand = new AsaCommand(
       "sp_adodotnet_primarykey", conn );
   adapter.InsertCommand.CommandType =
       CommandType.StoredProcedure;
   adapter.InsertCommand.UpdatedRowSource =
       UpdateRowSource.OutputParameters;
   AsaParameter parmId = new AsaParameter();
   parmId.AsaDbType = AsaDbType.Integer;
   parmId.Direction = ParameterDirection.Output;
   parmId.SourceColumn = "id";
   parmId.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parmId );
   AsaParameter parmName = new AsaParameter();
   parmName.AsaDbType = AsaDbType.Char;
   parmName.Direction = ParameterDirection.Input;
   parmName.SourceColumn = "name";
   parmName.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parmName );

4. Fill the DataSet.

   adapter.Fill( dataSet );

5. Insert the new rows into the DataSet.

   DataRow row = dataSet.Tables[0].NewRow();
   row[0] = -1;
   row[1] = "R & D --- Adapter";
   dataSet.Tables[0].Rows.Add( row );
   row = dataSet.Tables[0].NewRow();
   row[0] = -2;
   row[1] = "Marketing --- Adapter";
   dataSet.Tables[0].Rows.Add( row );
   row = dataSet.Tables[0].NewRow();
   row[0] = -3;
   row[1] = "Sales --- Adapter";
   dataSet.Tables[0].Rows.Add( row );
   row = dataSet.Tables[0].NewRow();
   row[0] = -4;
   row[1] = "Shipping --- Adapter";
   dataSet.Tables[0].Rows.Add( row );

6. Apply the changes in the DataSet to the database. When the Update() method is called, the primary key values are changed to the values obtained from the database.

   adapter.Update( dataSet );
   dataGrid.DataSource = dataSet.Tables[0];

   When you add new rows to the DataTable and call the Update method, the AsaDataAdapter calls the InsertCommand and maps the output parameters to the key columns for each new row. The Update method is called only once, but the InsertCommand is called by the Update method as many times as necessary for each new row being added.

7. Close the connection to the database.

   conn.Close();