Deleting Rows from a Table Using the AseDataAdapter Object

Use the AseDataAdapter object to delete rows from a table.

1. Declare and initialize an AseConnection object:

   For C#:

   AseConnection conn = new AseConnection( c_connStr );

   For Visual Basic .NET:

   Dim conn As New AseConnection( _
      c_connStr )

2. Open the connection:

   For C#:

   conn.Open();

   For Visual Basic .NET:

   conn.Open()

3. Create an AseDataAdapter object:

   For C#:

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

   For Visual Basic .NET:

   Dim adapter As New AseDataAdapter()
   adapter.MissingMappingAction = _
      MissingMappingAction.Passthrough
   adapter.MissingSchemaAction = _
      MissingSchemaAction.AddWithKey

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

   The following code creates a Select and a Delete command and defines the parameters for the Delete command:

   For C#:

   adapter.SelectCommand = new AseCommand(
      "SELECT * FROM publishers WHERE pub_id > '9900'",
      conn );
   adapter.DeleteCommand = new AseCommand(
      "DELETE FROM publishers WHERE pub_id = @pub_id", 
      conn );
   adapter.DeleteCommand.UpdatedRowSource =
      UpdateRowSource.None;
   AseParameter parm = new AseParameter("@pub_id", 
      AseDbType.Char, 4);
   parm.SourceColumn = "pub_id";
   parm.SourceVersion = DataRowVersion.Original;
   adapter.DeleteCommand.Parameters.Add( parm );

   For Visual Basic .NET:

   adapter.SelectCommand = New AseCommand( _
      "SELECT * FROM publishers WHERE pub_id > '9900'", _
      conn )
   adapter.DeleteCommand = New AseCommand( _
      "DELETE FROM publishers WHERE pub_id = @pub_id", conn )
   adapter.DeleteCommand.UpdatedRowSource = _
      UpdateRowSource.None
   Dim parm As New AseParameter("@pub_id", _
      AseDbType.Char, 4)
   parm.SourceColumn = "pub_id"
   parm.SourceVersion = DataRowVersion.Original
   adapter.DeleteCommand.Parameters.Add( parm )

5. Fill the DataTable with the results of the Select statement:

   For C#:

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

   For Visual Basic .NET:

   Dim dataTable As New DataTable( "publishers" )
   Dim rowCount As Integer = adapter.Fill( dataTable )

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

   For C#:

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

   For Visual Basic .NET:

   Dim row as DataRow
   For Each row in dataTable.Rows
      row.Delete()
   Next
   Dim recordsAffected As Integer =_
      	adapter.Update( dataTable )

7. Bind the results to the grid on the window:

   For C#:

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

   For Visual Basic .NET:

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

8. Close the connection:

   For C#:

   conn.Close();

   For Visual Basic .NET:

   conn.Close()