DataSet and DataAdapter Binding

DataSet and DataAdapter Binding

One of the most powerful features of ADO.NET and managed data providers is that any managed provider that implements a DataAdapter is fully, and automatically, compatible with the DataSet. In this next section, you will see how to configure a DataAdapter in such a way that you can push changes made in the DataSet to the original data source in a seamless, integrated, and almost invisible fashion.

DataSet Review

As you probably know by now, a DataSet is an in-memory representation of a table and column structure. Tables within a DataSet can be related to each other through relationships that establish parent/child relationships, and tables in a DataSet support the notion of primary and foreign keys. They are essentially a miniature, in-memory database. You have no doubt seen the DataSet used throughout this book and you will see it again. Rather than devote a single chapter to the DataSet, what we've done is simply use the DataSet whenever it was appropriate to accomplish a tasksomething that you will more than likely do for your own projects.

Associating a DataSet with a DataAdapter

As you have seen, the DataAdapter has the capability to store four different database commands: SelectCommand, UpdateCommand, DeleteCommand, and InsertCommand. On their own they are just simple commands that don't do anything more than a standard database command. However, when placed on the DataAdapter class, they can perform some very powerful tasks. When a DataAdapter has these four commands configured, it can examine a DataSet for recent changes and commit them to the database.

The DataAdapter essentially looks at all the rows in the DataSet. For each row that has been inserted, the InsertCommand will be invoked. For each row that has been updated, the UpdateCommand will be invoked. As you've probably guessed, for each row that has been deleted, the DeleteCommand will be invoked.

Sample: Hooking Up a DataSet to a Live Data Source

This next example illustrates how to create the four commands required by a DataAdapter and how to make changes on the DataSet that will be propagated to the original data source via the adapter. The code in Listing 27.4 shows the sample that illustrates this ability to link a DataSet with a DataAdapter.

Listing 27.4. A Demonstration of Linking a DataSet and DataAdapter Using SQL Server and the Northwind Database

 using System; using System.Data; using System.Data.SqlClient; namespace DataAdapterBinding { class Class1 {   [STAThread]   static void Main(string[] args)   {     SqlConnection conn = new SqlConnection(       "server=localhost; User ID=sa; Password=password; Initial Catalog=Northwind;");     conn.Open();     SqlCommand selectCommand = conn.CreateCommand();     selectCommand.CommandText =       "SELECT CustomerID, CompanyName, ContactName, ContactTitle FROM Customers";     selectCommand.CommandType = CommandType.Text;     // update command     SqlCommand updateCommand = conn.CreateCommand();     updateCommand.CommandText =       "UPDATE Customers SET CompanyName = @CompanyName, @ContactName = ContactName, " +       "ContactTitle = @ContactTitle WHERE CustomerID = @CustomerID";     updateCommand.Parameters.Add(       new SqlParameter("@CompanyName", SqlDbType.NVarChar,40, "CompanyName"));     updateCommand.Parameters.Add(       new SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"));     updateCommand.Parameters.Add(       new SqlParameter("@ContactTitle", SqlDbType.NVarChar, 30, "ContactTitle"));     updateCommand.Parameters.Add(       new SqlParameter("@CustomerID", SqlDbType.NChar, 5, "CustomerID"));     // insert command     SqlCommand insertCommand = conn.CreateCommand();     insertCommand.CommandType = CommandType.Text;     insertCommand.CommandText =       "INSERT INTO Customers(CustomerID,CompanyName, ContactName, " +       "ContactTitle) VALUES(@CustomerID, @CompanyName, @ContactName, @ContactTitle)";     insertCommand.Parameters.Add(       new SqlParameter("@CompanyName", SqlDbType.NVarChar,40, "CompanyName"));     insertCommand.Parameters.Add(       new SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"));     insertCommand.Parameters.Add(       new SqlParameter("@ContactTitle", SqlDbType.NVarChar, 30, "ContactTitle"));     insertCommand.Parameters.Add(       new SqlParameter("@CustomerID", SqlDbType.NChar, 5, "CustomerID"));     // delete command     SqlCommand deleteCommand = conn.CreateCommand();     deleteCommand.CommandText = "DELETE Customers WHERE CustomerID = @CustomerID";     deleteCommand.Parameters.Add(     new SqlParameter("@CustomerID", SqlDbType.NChar, 5, "CustomerID"));     SqlDataAdapter da = new SqlDataAdapter( selectCommand );     da.UpdateCommand = updateCommand;     da.InsertCommand = insertCommand;     da.DeleteCommand = deleteCommand;     DataSet ds = new DataSet();     da.Fill(ds);     // this will cause an update     ds.Tables[0].Rows[0]["CompanyName"] = "Modified";     // this will cause an insert     DataRow newrow = ds.Tables[0].NewRow();     newrow["CustomerID"] = "KEVCO";     newrow["CompanyName"] = "KevCorp Inc Limited";     newrow["ContactName"] = "Kevin Hoffman";     newrow["ContactTitle"] = "Owner";     ds.Tables[0].Rows.Add( newrow );     // this will cause a delete     ds.Tables[0].Rows[5].Delete();     // you must call this before trying to perform inserts and deletes     ds.AcceptChanges();     da.Update(ds);     conn.Close();     Console.WriteLine("DB changes committed.");     Console.ReadLine();   }  } } 

Just to make sure that the changes that we expect to see in the Customers table are actually there, open up Enterprise Manager and look at the table. If everything went well, the first row has been modified and a new row has been created, and one of the customers is missing. Figure 27.2 shows this screenshot from SQL Enterprise Manager.

Figure 27.2. Customers table after being modified by a DataSet/DataAdapter combination.