Summary

In this chapter, you learned how to modify the rows in a DataSet and then push those changes to the database via a DataAdapter.

You saw how to add restrictions to a DataTable and its DataColumn objects. This allows you to model the same restrictions placed on the database tables and columns in your DataTable and DataColumn objects. By adding restrictions up front, you prevent bad data from being added to your DataSet to begin with, and this helps reduce the errors when attempting to push changes in your DataSet to the database.

Each row in a DataTable is stored in a DataRow object, and you saw how to find, filter, and sort the DataRow objects in a DataTable using the Find() method of a DataTable. You also learned how to filter and sort the DataRow objects in a DataTable using the Select() method.

You saw the steps required to add, modify, and remove DataRow objects from a DataTable and then push those changes to the database. To do this you must first set up your DataAdapter with Command objects containing appropriate SQL INSERT, UPDATE, and DELETE statements. You store these Command objects in your DataAdapter object's InsertCommand, UpdateCommand, and DeleteCommand properties. You push changes from your DataSet to the database using the Update() method of your DataAdapter. When you add, modify, or remove DataRow objects from your DataSet and then call the Update() method of your DataAdapter, the appropriate InsertCommand, UpdateCommand, or DeleteCommand is run to push your changes to the database.

Concurrency determines how multiple users' modifications to the same row are handled. With optimistic concurrency, you can modify a row in a database table only if no one else has modified that same row since you loaded it into your DataSet. This is typically the best type of concurrency to use because you don't want to overwrite someone else's changes. With "last one wins" concurrency, you can always modify a row-and your changes overwrite anyone else's changes. You typically want to avoid using "last one wins" concurrency.

You can get a DataAdapter object to call stored procedures to add, modify, and remove rows from the database. These procedures are called instead of the INSERT, UPDATE, and DELETE statements you've seen how to set in a DataAdapter object's InsertCommand, UpdateCommand, and DeleteCommand properties.

Supplying your own INSERT, UPDATE, and DELETE statements or stored procedures to push changes from your DataSet to the database means you have to write a lot of code. You can avoid writing this code by using a CommandBuilder object, which can automatically generate single-table INSERT, UPDATE, and DELETE commands that push the changes you make to a DataSet object to the database. These commands are then set in the InsertCommand, UpdateCommand, and DeleteCommand properties of your DataAdapter object. When you then make changes to your DataSet and call the Update() method of your DataAdapter, the automatically generated command is run to push the changes to the database.

You also saw how to handle update failures and use transactions with a DataSet, and in the final section of this chapter you saw how to update the rows stored in a strongly typed data set.

In Chapter 12, you'll learn how to navigate and modify related data.