ASA Programming Guide
  Developing Applications with the .NET Data Provider
    Accessing and manipulating data
      Using the AsaDataAdapter object to access and manipulate data

Inserting, updating, and deleting rows using the AsaDataAdapter object

The AsaDataAdapter retrieves the result set into a DataSet. A DataSet is a collection of tables and the relationships and constraints between those tables. The DataSet is built into the .NET Framework, and is independent of the data provider used to connect to your database.

When you use the AsaDataAdapter, you must be connected to the database to fill the DataSet and to update the database with changes made to the DataSet. However, once the DataSet is filled, you can modify the DataSet while disconnected from the database.

If you do not want to apply your changes to the database right away, you can write the DataSet, including the data and/or the schema, to an XML file using the WriteXML method. Then, you apply the changes at a later time by loading a DataSet with the ReadXML method.

For more information, see the .NET Framework documentation for WriteXML and ReadXML.

When you call the Update method to apply changes from the DataSet to the database, the AsaDataAdapter analyzes the changes that have been made and then invokes the appropriate commands, INSERT, UPDATE, or DELETE, as necessary. When you use the DataSet, you can only make changes (inserts, updates, or deletes) to data that is from a single table. You cannot update result sets that are based on joins. If another user has a lock on the row you are trying to update, an exception is thrown.

• Unique primary keys    If two users insert new rows into a table, each row must have a unique primary key. For tables with autoincrement primary keys, the values in the DataSet may become out of sync with the values in the data source.

  For information about obtaining primary key values for autoincrement primary keys, see Obtaining primary key values.

• Updates made to the same value    If two users modify the same value, your application should include logic to determine which value is correct.

• Schema changes    If a user modifies the schema of a table you have updated in the DataSet, the update will fail when you apply the changes to the database.

• Data concurrency    Concurrent applications should see a consistent set of data. The AsaDataAdapter does not place a lock on rows that it fetches, so another user can update a value in the database once you have retrieved the DataSet and are working offline.

Many of these potential problems can be avoided by using the AsaCommand, AsaDataReader, and AsaTransaction objects to apply changes to the database. The AsaTransaction object is recommended because it allows you to set the isolation level for the transaction and it places locks on the rows so that other users cannot modify them.

For more information about using transactions to apply your changes to the database, see Inserting, updating, and deleting rows using the AsaCommand object.

To simplify the process of conflict resolution, you can design your insert, update, or delete statement to be a stored procedure call. By including INSERT, UPDATE, and DELETE statements in stored procedures, you can catch the error if the operation fails. In addition to the statement, you can add error handling logic to the stored procedure so that if the operation fails the appropriate action is taken, such as recording the error to a log file, or trying the operation again.

To insert rows into a table using the AsaDataAdapter

1. Declare and initialize an AsaConnection object.

   AsaConnection   conn = new AsaConnection(
       c_connStr );

2. Open the connection.

   conn.Open();

3. Create a new AsaDataAdapter object.

   AsaDataAdapter adapter = new AsaDataAdapter();
   adapter.MissingMappingAction =
       MissingMappingAction.Passthrough;
   adapter.MissingSchemaAction =
       MissingSchemaAction.Add;

4. Create the necessary AsaCommand objects and define any necessary parameters.

   The following code creates a SELECT and an INSERT command and defines the parameters for the INSERT command.

   adapter.SelectCommand = new AsaCommand(
       "SELECT * FROM department", conn );
   adapter.InsertCommand = new AsaCommand(
       "INSERT INTO department( dept_id, dept_name )
       VALUES( ?, ? )", conn );
   adapter.InsertCommand.UpdatedRowSource =
       UpdateRowSource.None;
   AsaParameter parm = new AsaParameter();
   parm.AsaDbType = AsaDbType.Integer;
   parm.SourceColumn = "dept_id";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add(
       parm );
   parm = new AsaParameter();
   parm.AsaDbType = AsaDbType.Char;
   parm.SourceColumn = "dept_name";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parm );

5. Fill the DataTable with the results of the SELECT statement.

   DataTable dataTable = new DataTable( "department" );
   int rowCount = adapter.Fill( dataTable );

6. Insert the new rows into the DataTable and apply the changes to the database.

   DataRow row1 = dataTable.NewRow();
   row1[0] = 600;
   row1[1] = "Eastern Sales";
   dataTable.Rows.Add( row1 );
   DataRow row2 = dataTable.NewRow();
   row2[0] = 700;
   row2[1] = "Western Sales";
   dataTable.Rows.Add( row2 );
   recordsAffected = adapter.Update( dataTable );

7. Display the results of the updates.

   dataTable.Clear();
   rowCount = adapter.Fill( dataTable );
   dataGrid.DataSource = dataTable;

8. Close the connection.

   conn.Close();

To update rows using the AsaDataAdapter object

1. Declare and initialize an AsaConnection object.

   AsaConnection conn = new AsaConnection( c_connStr );

2. Open the connection.

   conn.Open();

3. Create a new AsaDataAdapter object.

   AsaDataAdapter adapter = new AsaDataAdapter();
   adapter.MissingMappingAction =
       MissingMappingAction.Passthrough;
   adapter.MissingSchemaAction =
       MissingSchemaAction.Add;

4. Create an AsaCommand object and define its parameters.

   The following code creates a SELECT and an UPDATE command and defines the parameters for the UPDATE command.

   adapter.SelectCommand = new AsaCommand(
       "SELECT * FROM department WHERE dept_id > 500",
       conn );
   adapter.UpdateCommand = new AsaCommand(
       "UPDATE department SET dept_name = ?
       WHERE dept_id = ?", conn );
   adapter.UpdateCommand.UpdatedRowSource =
       UpdateRowSource.None;
   AsaParameter parm = new AsaParameter();
   parm.AsaDbType = AsaDbType.Char;
   parm.SourceColumn = "dept_name";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.UpdateCommand.Parameters.Add( parm );
   parm = new AsaParameter();
   parm.AsaDbType = AsaDbType.Integer;
   parm.SourceColumn = "dept_id";
   parm.SourceVersion = DataRowVersion.Original;
   adapter.UpdateCommand.Parameters.Add( parm );

5. Fill the DataTable with the results of the SELECT statement.

   DataTable dataTable = new DataTable( "department" );
   int rowCount = adapter.Fill( dataTable );

6. Update the DataTable with the updated values for the rows and apply the changes to the database.

   foreach ( DataRow row in dataTable.Rows )
   {
   row[1] = ( string ) row[1] + "_Updated";
   }
   recordsAffected = adapter.Update( dataTable );

7. Bind the results to the grid on the screen.

   dataTable.Clear();
   adapter.SelectCommand.CommandText =
       "SELECT * FROM department";
   rowCount = adapter.Fill( dataTable );
   dataGrid.DataSource = dataTable;

8. Close the connection.

   conn.Close();

To delete rows from a table using the AsaDataAdapter object

1. Declare and initialize an AsaConnection object.

   AsaConnection conn = new AsaConnection( c_connStr );

2. Open the connection.

   conn.Open();

3. Create an AsaDataAdapter object.

   AsaDataAdapter adapter = new AsaDataAdapter();
   adapter.MissingMappingAction =
       MissingMappingAction.Passthrough;
   adapter.MissingSchemaAction =
       MissingSchemaAction.AddWithKey;

4. Create the required AsaCommand objects and define any necessary parameters.

   The following code creates a SELECT and a DELETE command and defines the parameters for the DELETE command.

   adapter.SelectCommand = new AsaCommand(
       "SELECT * FROM department WHERE dept_id > 500",
       conn );
   adapter.DeleteCommand = new AsaCommand(
       "DELETE FROM department WHERE dept_id = ?",
       conn );
   adapter.DeleteCommand.UpdatedRowSource =
       UpdateRowSource.None;
   AsaParameter parm = new AsaParameter();
   parm.AsaDbType = AsaDbType.Integer;
   parm.SourceColumn = "dept_id";
   parm.SourceVersion = DataRowVersion.Original;
   adapter.DeleteCommand.Parameters.Add( parm );

5. Fill the DataTable with the results of the SELECT statement.

   DataTable dataTable = new DataTable( "department" );
   int rowCount = adapter.Fill( dataTable );

6. Modify the DataTable and apply the changes to the database.

   for each ( DataRow in dataTable.Rows )
   {
       row.Delete();
   }
   recordsAffected = adapter.Update( dataTable )

7. Bind the results to the grid on the screen.

   dataTable.Clear();
   rowCount = adapter.Fill( dataTable );
   dataGrid.DataSource = dataTable;

8. Close the connection.

   conn.Close();