In the preceding sections you have learned how to connect to a data source. In addition, you have learned how to communicate with that source for data retrieval, manipulation, and schema queries. Armed with that knowledge, you can begin writing applications that interact with and manipulate data. A wide variety of tools are available to you for doing this. In later chapters, you will see how to work with data on specific platforms such as Windows Forms and ASP.NET. This section illustrates tools that are common to all platforms and are central to ADO.NET: the DataSet and the DataAdapter.
Introduction to the DataSet
The DataSet is an extremely powerful class. Essentially this class is an in-memory database. It can contain tables made up of multiple columns of varying data types. Each table can contain multiple rows, and those rows can be related to each other through foreign keys as well as complex relationships that enforce parent/child data constraints. DataTables can assign new, unique, numeric identifiers to rows as they are added to the table. DataSets can also persist their data in various formats such as XML and even a newly added binary format for ADO.NET 2.0.
When a DataSet contains data, you can use various methods to search the contents of the tables contained within it. You can even treat a DataSet as if it was an XML document and perform XPath queries against it.
As shown in Figure 17.1, the DataSet is extremely powerful and flexible and really does have many of the features of a server-hosted database, but contained in a compact, flexible, object-oriented form.
Figure 17.1. The DataSet hierarchy.
The code in Listing 17.6 illustrates a few basic tasks that can be accomplished with the DataSet, including dynamically creating a data structure, adding and deleting rows, and querying information contained in a DataTable.
Listing 17.6. Working with a DataSet
The preceding code creates a new DataSet with a single table. The Customers table consists of three columns: a unique identifier that autoincrements, a user name, and an email address. There is also a unique constraint placed on the email address column so that users in this table cannot share the same email address. Finally, the code utilizes the Select method to retrieve a list of users that have a given email address.
When the code is executed, the following line is output to the console:
Customer Jane Doe(2) matched search with e-mail firstname.lastname@example.org
Using a DataAdapter
The DataAdapter class functions very much like an electrical plug. An electrical plug connects an appliance to a power source. The DataAdapter connects a DataSet (or DataTable) to a data source. This "plug" has four prongs, one for each type of connection that can take place:
The best way to see how a DataAdapter works is to look at an example in action. The code in Listing 17.7 illustrates the creation of a DataAdapter as well as several commands. These commands are then executed when the DataSet invokes the Update() method. The sample in Listing 17.7 shows how users can be updated, deleted, inserted, and queried in a DataSet, and then have all of those changes persisted to a data source by the DataAdapter.
To follow along with this sample, you should have a SQL Express (or SQL Server; you'll have to modify the data source property) database called "TestDB." In that database, there is a table called Users with the following columns: FirstName, LastName, UserName, and UserID. You can discern the data type and size of these columns from the stored procedure being invoked in the code in Listing 17.7.
Listing 17.7. Using a DataAdapter to Connect a DataSet to a Data Source
As you can see from Figure 17.2, the second user (originally called "SecondUser") has been removed from my database. The first user was modified so that the first name was set to "Kevin_Modified". Finally, you can also see that a new user was created. All of this was accomplished by invoking a single Update() method on the DataAdapter.
Figure 17.2. View of data after being modified by the SqlDataAdapter.
These modifications were made possible because each time you modify anything in a DataSet, you modify the RowState of the row. The RowState indicates whether the row is supposed to be deleted, brand new, or needs to be updated. When the Update() method is invoked, the DataAdapter will iterate through all of the rows that have been inserted, deleted, or modified, and will invoke the corresponding InsertCommand, UpdateCommand, or DeleteCommand, using that row's columns to feed the parameters for the command.