Developing Applications 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 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. Then, you apply the changes at a later time by loading a DataSet with the ReadXml method.
For more information, see the. 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.
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. 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.