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.

Any 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:

Unique primary keys – when two users insert new rows into a table, each row must have a unique primary key. For tables with auto-increment 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 – when two users modify the same value, your application should include logic to determine which value is correct.
Schema changes – when a user modifies the schema of a table you have updated in the DataSet, the update fails when you apply the changes to the database.
Data concurrency – when concurrent applications can see a consistent set of data. The AseDataAdapter does not place a lock on rows that it fetches, so a second user can update a value in the database when you have retrieved the DataSet and are working offline.

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.

Inserting rows into a table using the AseDataAdapter

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 )

Open the connection:

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

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

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 )

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 )

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 )

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

Close the connection:

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

Updating rows using the AseDataAdapter object

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 )

Open the connection:

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

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

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 )

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 )

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 )

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

Close the connection:

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

Deleting rows from a table using the AseDataAdapter object

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 )

Open the connection:

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

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

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 )

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 )

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 )

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

Close the connection:

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