Obtaining primary key values

If the table you are updating has an auto-incremented primary key 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 AseDataAdapter, this technique can be used to fill the columns in the DataSet with the primary key values generated by the data source. If you use this technique with the AseCommand 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,” which is a NUMERIC(8) that contains an auto-incremented value; the name column is CHAR(40).

These examples call the following stored procedure to retrieve the auto-incremented primary key value from the database:

create procedure sp_adodotnet_primarykey
@p_name char(40),
@p_id int output
as 
begin 
   insert into adodotnet_primarykey(name)
      VALUES(@p_name)
   select @p_id = @@identity
END

Inserting a new row with an auto-incremented primary key using the AseCommand object

Connect to the database:

For C#:

AseConnection conn = new AseConnection( c_connStr );
conn.Open();

For Visual Basic .NET:

Dim conn As New AseConnection( _
   c_connStr )
conn.Open()

Create a new AseCommand 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:

For C#:

AseCommand      cmd = conn.CreateCommand();
cmd.CommandText = "sp_adodotnet_primarykey";
cmd.CommandType = CommandType.StoredProcedure;
AseParameter parmId = new AseParameter(
   “@p_id“, AseDbType.Integer);
parmId.Direction = ParameterDirection.Output;
cmd.Parameters.Add( parmId );
AseParameter parmName = new AseParameter(
   “@p_name”, AseDbType.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;

For Visual Basic .NET:

Dim cmd As AseCommand = conn.CreateCommand()
cmd.CommandText = "sp_adodotnet_primarykey"
cmd.CommandType = CommandType.StoredProcedure
Dim parmId As New AseParameter("@p_id", _ 	   AseDbType.Integer)
parmId.Direction = ParameterDirection.Output
cmd.Parameters.Add( parmId )
Dim parmName As New AseParameter("@p_name", _
   AseDbType.Char)
parmName.Direction = ParameterDirection.Input
cmd.Parameters.Add(parmName )

parmName.Value = "R & D --- Command"
cmd.ExecuteNonQuery()
Dim id1 As Integer = parmId.Value
parmName.Value = "Marketing --- Command"
cmd.ExecuteNonQuery()
Dim id2 As Integer = parmId.Value
parmName.Value = "Sales --- Command"
cmd.ExecuteNonQuery()
Dim id3 As Integer = parmId.Value
parmName.Value = "Shipping --- Command"
cmd.ExecuteNonQuery()
dim id4 As Integer = parmId.Value

Bind the results to the grid on the window, and apply the changes to the database:

For C#:

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

For Visual Basic .NET:

cmd.CommandText = "select * from " + _
   "adodotnet_primarykey"
cmd.CommandType = CommandType.Text
Dim dr As AseDataReader = cmd.ExecuteReader()
dataGrid.DataSource = dr

Close the connection:

For C#:
```
conn.Close();
```
For Visual Basic .NET:
```
conn.Close()
```

Inserting a new row with an auto-incremented primary key using the AseDataAdapter object

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

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

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 )

Fill the DataSet:

For C#:

adapter.Fill( dataSet );

For Visual Basic .NET:

adapter.Fill( dataSet )

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 )

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.
Close the connection to the database:

For C#:
```
conn.Close();
```
For Visual Basic .NET:
```
conn.Close()
```