Inserting a New Row with an Auto-Incremented Primary Key Using the AseDataAdapter Object

Use the AseDataAdapter object to insert a new row with an auto-incremented primary key.

1. Create a new AseDataAdapter:

   For C#:

   AseConnection conn = new AseConnection(
      c_connStr );
   conn.Open();
   DataSet dataSet = new DataSet();
   AseDataAdapter adapter = new AseDataAdapter();
   adapter.MissingMappingAction =
      MissingMappingAction.Passthrough;
   adapter.MissingSchemaAction =
      MissingSchemaAction.AddWithKey;

   For Visual Basic .NET:

   Dim conn As New AseConnection( _
      c_connStr )
   conn.Open()
   Dim dataSet As New DataSet()
   Dim adapter As New AseDataAdapter()
   adapter.MissingMappingAction = _
      MissingMappingAction.Passthrough
   adapter.MissingSchemaAction = _
      MissingSchemaAction.AddWithKey

2. Fill the data and schema of the DataSet. In the following code, the SelectCommand is called by the AseDataAdapter.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:

   For C#:

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

   For Visual Basic .NET:

   adapter.SelectCommand = New AseCommand( _
      "select * from adodotnet_primarykey", conn )

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

   For C#:

   adapter.InsertCommand = new AseCommand(
      "sp_adodotnet_primarykey", conn );
   adapter.InsertCommand.CommandType =
      CommandType.StoredProcedure;
   adapter.InsertCommand.UpdatedRowSource =
      UpdateRowSource.OutputParameters;
   AseParameter parmId = new AseParameter(
      "@p_id", AseDbType.Integer);
   parmId.Direction = ParameterDirection.Output;
   parmId.SourceColumn = "id";
   parmId.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parmId );
   AseParameter parmName = new AseParameter(
      "@p_name", AseDbType.Char);
   parmName.Direction = ParameterDirection.Input;
   parmName.SourceColumn = "name"; 
   parmName.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parmName );

   For Visual Basic .NET:

   adapter.InsertCommand = new AseCommand( _
      "sp_adodotnet_primarykey", conn )
   adapter.InsertCommand.CommandType = _
      CommandType.StoredProcedure
   adapter.InsertCommand.UpdatedRowSource = _
      UpdateRowSource.OutputParameters
   Dim parmId As New AseParameter( _
      "@p_id", AseDbType.Integer)
   parmId.Direction = ParameterDirection.Output
   parmId.SourceColumn = "id"
   parmId.SourceVersion = DataRowVersion.Current
   adapter.InsertCommand.Parameters.Add( parmId )
   Dim parmName As New AseParameter( _
      "@p_name", AseDbType.Char)
   parmName.Direction = ParameterDirection.Input
   parmName.SourceColumn = "name"
   parmName.SourceVersion = DataRowVersion.Current
   adapter.InsertCommand.Parameters.Add( parmName )

4. Fill the DataSet:

   For C#:

   adapter.Fill( dataSet );

   For Visual Basic .NET:

   adapter.Fill( dataSet )

5. Insert the new rows into the DataSet:

   For C#:

   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 );

   For Visual Basic .NET:

   Dim row As DataRow = 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:

   For C#:

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

   For Visual Basic .NET:

   adapter.Update( dataSet )
   dataGrid.DataSource = dataSet.Tables(0)

   When you add new rows to the DataTable and call the Update method, the AseDataAdapter 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:

   For C#:

   conn.Close();

   For Visual Basic .NET:

   conn.Close()