Working with the Data

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

using System; using System.Data; using System.Collections.Generic; using System.Text; namespace DataSet1 { class Program { static void Main(string[] args) { // create an empty data set with new structure   DataSet ds = new DataSet();   DataTable customers = new DataTable("Customers");   customers.Columns.Add(new DataColumn("CustomerId", typeof(int)));   customers.Columns[0].AutoIncrement = true;   customers.Columns[0].AutoIncrementSeed = 1;   customers.PrimaryKey = new DataColumn[] { customers.Columns[0] };   customers.Columns.Add(new DataColumn("CustomerName", typeof(string)));   customers.Columns.Add(new DataColumn("Email", typeof(string)));   customers.Constraints.Add(new UniqueConstraint(customers.Columns["Email"]));   ds.Tables.Add(customers);   DataRow newCustomer = customers.NewRow();   newCustomer["CustomerName"] = "John Doe";   newCustomer["Email"] = "[e-mail]";   customers.Rows.Add(newCustomer);   newCustomer = customers.NewRow();   newCustomer["CustomerName"] = "Jane Doe";   newCustomer["Email"] = "[e-mail]";   customers.Rows.Add(newCustomer);   // perform a search on the customers table   DataRow[] custMatch = customers.Select("Email = '[e-mail]'");   foreach (DataRow customer in custMatch)   {   Console.WriteLine(       string.Format("Customer {0}({1}) matched search with e-mail {2}",       customer["CustomerName"], customer["CustomerId"], customer["Email"]));   }   Console.ReadLine();   } } } 

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 [e-mail] 

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:

• InsertCommand This command is executed when an item in an associated DataTable is ready to be inserted into the data source.

• DeleteCommand This command is executed when an item in an associated DataTable is ready to be deleted from the data source.

• UpdateCommand This command is executed to commit pending changes to an item in a DataTable.

• SelectCommand This command is executed to populate the DataTable or DataSet with the information retrieved from the data source.

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

using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace DataAdapter { class Program { static void Main(string[] args) {   SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();   scb.DataSource = @".\SQLExpress";   scb.InitialCatalog = "TestDB";   scb.IntegratedSecurity = true;   SqlConnection conn = new SqlConnection(scb.ConnectionString);   conn.Open();   SqlCommand selectCommand = conn.CreateCommand();   selectCommand.CommandText = "SELECT * FROM Users";   SqlCommand updateCommand = conn.CreateCommand();   updateCommand.CommandText =    "UPDATE Users SET UserName=@UserName, FirstName=@FirstName " +    ",LastName=@LastName WHERE UserID = @UserID";   updateCommand.Parameters.Add(     new SqlParameter("@UserName", SqlDbType.VarChar, 50, "UserName"));   updateCommand.Parameters.Add(     new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));   updateCommand.Parameters.Add(     new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));   updateCommand.Parameters.Add(     new SqlParameter("@UserId", SqlDbType.Int, 4, "UserId"));   SqlCommand deleteCommand = conn.CreateCommand();   deleteCommand.CommandText = "DELETE Users WHERE UserID = @UserID";   deleteCommand.Parameters.Add(new SqlParameter("@UserId",     SqlDbType.Int, 4, "UserId"));   SqlCommand insertCommand = conn.CreateCommand();   insertCommand.CommandText =     "INSERT INTO Users(UserName, FirstName, LastName) " +     "VALUES(@UserName, @FirstName, @LastName)";   insertCommand.Parameters.Add(     new SqlParameter("@UserName", SqlDbType.VarChar, 50, "UserName"));   insertCommand.Parameters.Add(     new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));   insertCommand.Parameters.Add(     new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));   insertCommand.Parameters.Add(     new SqlParameter("@UserId", SqlDbType.Int, 4, "UserId"));   DataSet ds = new DataSet();   SqlDataAdapter sqlDa = new SqlDataAdapter(selectCommand);   sqlDa.UpdateCommand = updateCommand;   sqlDa.DeleteCommand = deleteCommand;   sqlDa.InsertCommand = insertCommand;   sqlDa.Fill(ds); // update an existing row   DataTable users = ds.Tables[0];   users.Rows[0]["FirstName"] = "Kevin_Modified"; // delete an existing row   users.Rows[1].Delete(); // create a new row   DataRow newUser = users.NewRow();   newUser["UserName"] = "added";   newUser["FirstName"] = "Brand";   newUser["LastName"] = "New";   users.Rows.Add(newUser); // call Update to invoke the Update command, // the Delete command, and the Insert command // on the associated datasource via the dataadapter   sqlDa.Update(ds);   Console.WriteLine("Update Complete");   Console.ReadLine(); } } } 

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.