Inserting, updating, and deleting rows using the AseDataAdapter object

The AseDataAdapter object retrieves the result set into a DataSet, which 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 Adaptive Server ADO.NET Data Provider used to connect to your database.

When you use the AseDataAdapter, it will open the connection if you are not already connected, fill the DataSet, and close the connection if you had not opened it explicitly. However, when the DataSet is filled, you can modify it 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 AseDataAdapter analyzes the changes that have been made and invokes the appropriate commands Insert, Update, or Delete, as necessary.

When you use the DataSet, you can only change (insert, update, or delete) data that is from a single table. You cannot update result sets that are based on joins.

NoteAny changes you make to the DataSet are made while you are disconnected. This means that your application does not have locks on these rows in the database. Your application must be designed to resolve any conflicts that can occur when changes from the DataSet are applied to the database if another user changes the data you are modifying before your changes are applied to the database.

Resolving conflicts when using the AseDataAdapter

Some of the conflicts that your application logic should address include:

Many of these potential problems can be avoided by using the AseCommand, AseDataReader, and AseTransaction objects to apply changes to the database. Sybase recommends the AseTransaction object, 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 AseCommand 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.

Steps Inserting rows into a table using the AseDataAdapter

  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 a new AseDataAdapter object:

    For C#:

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

    For Visual Basic .NET:

    Dim adapter As New AseDataAdapter()
    adapter.MissingMappingAction = _
       MissingMappingAction.Passthrough
    adapter.MissingSchemaAction = _
       MissingSchemaAction.Add
    
  4. Create the necessary AseCommand objects and define any necessary parameters:

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

    For C#:

    adapter.SelectCommand = new AseCommand(
       "SELECT * FROM publishers", conn );
    adapter.InsertCommand = new AseCommand(
       "INSERT INTO publishers( pub_id, pub_name, city, state) " +
       "VALUES( @pub_id, @pub_name, @city, @state  )", conn);
    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
    AseParameter parm = new AseParameter("@pub_id", AseDbType.Char, 4);
    parm.SourceColumn = "pub_id";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.InsertCommand.Parameters.Add( parm );
    parm = new AseParameter("@pub_name", AseDbType.VarChar, 40);
    parm.SourceColumn = "pub_name";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.InsertCommand.Parameters.Add( parm );
    parm = new AseParameter("@city", AseDbType.VarChar, 20);
    parm.SourceColumn = "city";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.InsertCommand.Parameters.Add( parm );
    parm = new AseParameter("@state", AseDbType.Char, 2);
    parm.SourceColumn = "state";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.InsertCommand.Parameters.Add( parm );
    

    For Visual Basic .NET:

    adapter.SelectCommand = New AseCommand( _
       "SELECT * FROM publishers", conn )
    adapter.InsertCommand = New AseCommand( _
       "INSERT INTO publishers( pub_id, pub_name,  city, state) " + _
       " VALUES( @pub_id, @pub_name, @city, @state  )", conn)
    adapter.InsertCommand.UpdatedRowSource = _
       UpdateRowSource.None
    Dim parm As New AseParameter("@pub_id", AseDbType.Char, 4)
    parm.SourceColumn = "pub_id"
    parm.SourceVersion = DataRowVersion.Current
    adapter.InsertCommand.Parameters.Add( parm )
    parm = New AseParameter("@pub_name", AseDbType.VarChar, 40)
    parm.SourceColumn = "pub_name"
    parm.SourceVersion = DataRowVersion.Current
    adapter.InsertCommand.Parameters.Add( parm )
    parm = New AseParameter("@city", AseDbType.VarChar, 20)
    parm.SourceColumn = "city"
    parm.SourceVersion = DataRowVersion.Current
    adapter.InsertCommand.Parameters.Add( parm )
    parm = New AseParameter("@state", AseDbType.Char, 2)
    parm.SourceColumn = "state"
    parm.SourceVersion = DataRowVersion.Current
    adapter.InsertCommand.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. Insert the new rows into the DataTable and apply the changes to the database:

    For C#:

    DataRow row1 = dataTable.NewRow();
    row1[0] = "9901";
    row1[1] = "New Publisher";
    row1[2] = "Concord";
    row1[3] = "MA";
    dataTable.Rows.Add( row1 );
    DataRow row2 = dataTable.NewRow();
    row2[0] = "9902";
    row2[1] = "My Publisher";
    row2[2] = "Dublin";
    row2[3] = "CA";
    dataTable.Rows.Add( row2 );
    int recordsAffected = adapter.Update( dataTable );
    

    For Visual Basic .NET:

    Dim row1 As DataRow = dataTable.NewRow()
    row1(0) = "9901"
    row1(1) = "New Publisher"
    row1(2) = "Concord"
    row1(3) = "MA"
    dataTable.Rows.Add( row1 )
    Dim row2 As DataRow = dataTable.NewRow()
    row2(0) = "9902"
    row2(1) = "My Publisher"
    row2(2) = "Dublin"
    row2(3) = "CA"
    dataTable.Rows.Add( row2 )
    Dim recordsAffected As Integer =_
       adapter.Update( dataTable )
    
  7. Display the results of the updates:

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

StepsUpdating rows using the AseDataAdapter object

  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 a new AseDataAdapter object:

    For C#:

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

    For Visual Basic .NET:

    Dim adapter As New AseDataAdapter()
    adapter.MissingMappingAction = _
       MissingMappingAction.Passthrough
    adapter.MissingSchemaAction = _
       MissingSchemaAction.Add
    
  4. Create an AseCommand object and define its parameters.

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

    For C#:

    adapter.SelectCommand = new AseCommand(
       "SELECT * FROM publishers WHERE pub_id > '9900'",    conn );
    adapter.UpdateCommand = new AseCommand(
       "UPDATE publishers SET pub_name = @pub_name, " +
       "city = @city, state = @state " +
       "WHERE pub_id = @pub_id", conn );
    adapter.UpdateCommand.UpdatedRowSource =
       UpdateRowSource.None;
    AseParameter parm = new AseParameter("@pub_id", 
       AseDbType.Char, 4);
    parm.SourceColumn = "pub_id";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.UpdateCommand.Parameters.Add( parm );
    parm = new AseParameter("@pub_name",
       AseDbType.VarChar, 40);
    parm.SourceColumn = "pub_name";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.UpdateCommand.Parameters.Add( parm );
    parm = new AseParameter("@city",
       AseDbType.VarChar, 20);
    parm.SourceColumn = "city";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.UpdateCommand.Parameters.Add( parm );
    parm = new AseParameter("@state",
       AseDbType.Char, 2);
    parm.SourceColumn = "state";
    parm.SourceVersion = DataRowVersion.Current;
    adapter.UpdateCommand.Parameters.Add( parm );
    

    For Visual Basic .NET:

    adapter.SelectCommand = New AseCommand( _
      "SELECT * FROM publishers WHERE pub_id > '9900'",_
      conn )
    adapter.UpdateCommand = New AseCommand( _
      "UPDATE publishers SET pub_name = @pub_name, " + _
      "city = @city, state = @state " + _
      "WHERE pub_id = @pub_id", conn )
    adapter.UpdateCommand.UpdatedRowSource = _
      UpdateRowSource.None
    Dim parm As New AseParameter("@pub_id", _
      AseDbType.Char, 4)
    parm.SourceColumn = "pub_id"
    parm.SourceVersion = DataRowVersion.Current
    adapter.UpdateCommand.Parameters.Add( parm )
    parm = New AseParameter("@pub_name", _
      AseDbType.VarChar, 40)
    parm.SourceColumn = "pub_name"
    parm.SourceVersion = DataRowVersion.Current
    adapter.UpdateCommand.Parameters.Add( parm )
    parm = New AseParameter("@city", _
      AseDbType.VarChar, 20)
    parm.SourceColumn = "city"
    parm.SourceVersion = DataRowVersion.Current
    adapter.UpdateCommand.Parameters.Add( parm )
    parm = New AseParameter("@state", _
      AseDbType.Char, 2)
    parm.SourceColumn = "state"
    parm.SourceVersion = DataRowVersion.Current
    adapter.UpdateCommand.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. Update the DataTable with the updated values for the rows, and apply the changes to the database:

    For C#:

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

    For Visual Basic .NET:

    Dim row as DataRow
    For Each row in dataTable.Rows
       row(1) = row(1) + "_Updated"
    Next
    Dim recordsAffected As Integer = _
    adapter.Update( dataTable )
    
  7. Bind the results to the grid on the window:

    For C#:

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

    For Visual Basic .NET:

    dataTable.Clear()
    adapter.SelectCommand.CommandText = _
       "SELECT * FROM publishers";
    rowCount = adapter.Fill( dataTable )
    dataGrid.DataSource = dataTable
    
  8. Close the connection:

    For C#:

    conn.Close();
    

    For Visual Basic .NET:

    conn.Close()
    

StepsDeleting rows from a table using the AseDataAdapter object

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