12.3 Updating Data Using Datasets

So far in this chapter, you have seen how to update a database and how to add transactions to ensure data integrity. All of that is fine as far as it goes, but nothing you've done so far to update the database takes advantage of the DataSet object, which you will remember is the keystone of ADO.NET.

If you are using the DataSet object to retrieve data and pass it from tier to tier within your application, you would also like to manipulate that data within the dataset and push the changes back to the database. To make this more sophisticated model of data updating work, you will need to take advantage of the advanced capabilities of the DataSet and the DataAdapter classes, and you'll need to understand how they in turn use the Command and Connection objects to mediate between the dataset and the database itself.

12.3.1 The Dataset and the Data Adapter

As explained in Chapter 11, the DataSet object interacts with the database through a DataAdapter object. Until now, you've created the data adapter by passing in a command string and a connection string to the DataAdapter object's constructor and then calling the Fill method. It turns out that Fill interacts with the database by creating a command object on your behalf and assigning that command object to the SelectCommand property of the DataAdapter object.

Each SqlDataAdapter object has four command properties (SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand), each of which takes an object of type SqlCommand. Thus far, you've been using the Fill method to create a SelectCommand object (that is, a command object assigned to the SelectCommand property) by employing the command string parameter you've passed in to the DataAdapter object's constructor. For example, in Example 11-2 you wrote:

// get records from the Bugs table
string commandString = 
   "Select BugID, Description from Bugs";

// create the dataset command object 
// and the DataSet object
SqlDataAdapter dataAdapter = 
   new SqlDataAdapter(
   commandString, connectionString);

DataSet dataSet = new DataSet(  );

// fill the dataset object
dataAdapter.Fill(dataSet,"Bugs");

You could just as easily have explicitly set the SelectCommand property of the DataAdapter by writing:

// define the SQL Select command
string commandString = "Select BugID, Description from Bugs";

// make a data adapter (default constructor)
SqlDataAdapter dataAdapter = new SqlDataAdapter(  );

// make a connection object (pass in connection string)
SqlConnection conn = new SqlConnection(connectionString);

// make a command object, passing in command string and connection object
SqlCommand cmd = new SqlCommand(commandString, conn); 

// assign the new command object to the SelectCommand property
dataAdapter.SelectCommand = cmd;

In VB .NET, it would be:

' define the SQL Select command
Dim commandString As String = "Select BugID, Description from Bugs"

' make a data adapter (default constructor)
Dim dataAdapter As New SqlDataAdapter(  )

' make a connection object (pass in connection string)
Dim conn As New SqlConnection(connectionString)

' make a command object, passing in command string and connection object
Dim cmd As New SqlCommand(commandString, conn)

' assign the new command object to the SelectCommand property
dataAdapter.SelectCommand = cmd

To update the database with the changes you'll make to your dataset, you'll need to explicitly set the other three properties: UpdateCommand, DeleteCommand, and InsertCommand. You will fill these three properties with either SQL statements, or, more commonly, the names of stored procedures. When the data adapter is told to update the database, it will examine the changes to the dataset and call the appropriate command objects to update, delete, or insert records. Often, a single request to a dataset to update the database will cause each of these commands to be called repeatedly, once for each modified row.

12.3.2 Steps for Updating the Database

The steps to updating a database using a dataset are:

The example program described in the next section will walk you through each of these steps and examine their implementation and implications in some detail.

12.3.2.1 Creating and displaying a dataset

As you have done in many previous examples, you start by retrieving data from the database using a stored procedure, and displaying that data in a grid, as shown in Figure 12-5.

Figure 12-5. Displaying bug and bug history information

This data grid is created once again by calling the CreateBugDataSet method:

private DataSet CreateBugDataSet(  )
{

   // connection string to connect to the Bugs Database
   string connectionString = 
      "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs";

   // Create connection object, initialize with 
   // connection string. Open it.
   System.Data.SqlClient.SqlConnection connection = 
      new System.Data.SqlClient.SqlConnection(connectionString);
   connection.Open(  );

   // Create a SqlCommand object and assign the connection
   System.Data.SqlClient.SqlCommand command = 
      new System.Data.SqlClient.SqlCommand(  );
   command.Connection=connection;
   command.CommandText="spBugsWithIDs";
   command.CommandType=CommandType.StoredProcedure;

   // create a data adapter and assign the command object
   // and add the table mapping for bugs
   SqlDataAdapter dataAdapter = new SqlDataAdapter(  );
   dataAdapter.SelectCommand=command;
   dataAdapter.TableMappings.Add("Table","BugInfo");

   // Create the dataset and use the data adapter to fill it
   DataSet dataSet = new DataSet(  );
   dataAdapter.Fill(dataSet);
   return dataSet;
}

The VB.NET equivalent is:

Private Function CreateBugDataSet(  ) As DataSet
   ' connection string to connect to the Bugs Database
   Dim connectionString As String = _
      "server=YourDB; uid=sa; pwd=YourPassword; " & _
      "database=ProgASPDotNetBugs"

   ' Create connection object, initialize with 
   ' connection string. Open it.
   Dim myConnection As _ 
        New System.Data.SqlClient.SqlConnection(connectionString)
   myConnection.Open(  )

   ' Create a SqlCommand object and assign the connection
   Dim myCommand As New System.Data.SqlClient.SqlCommand(  )
   myCommand.Connection = myConnection
   myCommand.CommandText = "spBugsWithIDs"
   myCommand.CommandType = CommandType.StoredProcedure

   ' create a data adapter and assign the command object
   ' and add the table mapping for bugs
   Dim dataAdapter As New SqlDataAdapter(  )
   dataAdapter.SelectCommand = myCommand
   dataAdapter.TableMappings.Add("Table", "BugInfo")

   ' Create the dataset and use the data adapter to fill it
   Dim myDataSet As New DataSet(  )
   dataAdapter.Fill(myDataSet)
   Return myDataSet
End Function

The only change to the previous example is that this time CreateBugDataSet calls a new stored procedure, spBugsWithIDs.

The source code for the spBugsWithIDs stored procedure itself is shown in Example 12-8. There are two important things to note in this stored procedure. The first is that the data displayed in the grid is once again drawn from a number of different tables. The Description field is from the Bugs table. The Response field (used to populate the Most Recent Action column on the grid) is taken from the last BugHistory record for each Bug. The Owner is drawn from the People table based on the Owner value in the latest BugHistory record (described in the sidebar "Finding the Last BugHistory").

Example 12-8. The spBugsWithIDs stored procedure

CREATE PROCEDURE spBugsWithIDs  AS
select b.BugID, h.BugHistoryID, b.Description, b.Version, h.Response, 
o.FullName as owner, h.owner as ownerID,
b.Product as ProductID, p.ProductDescription, 
b.Reporter as ReporterID, r.FullName as reporter, 
h.status as statusID, s.StatusDescription, 
h.severity as severityID, sev.SeverityDescription, h.DateStamp 
from  
(select bugID, max(bugHistoryID) as maxHistoryID from BugHistory group by bugID) t 
join bugs b on b.bugid = t.bugid 
join BugHistory h on h.bugHistoryID = t.maxHistoryID 
join lkProduct p on b.Product = p.ProductID  
join People r on b.Reporter = r.PersonID  
join People o on h.Owner = o.PersonID 
join lkStatus s on s.statusid = h.status 
join lkSeverity sev on sev.SeverityID = h.severity
GO

Finding the Last BugHistory Your goal is to get information about the latest entry in the BugHistory table for each bug. You know that each entry in the BugHistory table has its own BugHistoryID, but how do you match the highest BugHistoryID with each bug? You do that with the group by clause in SQL. You can find the maximum entry for each BugID per BugID with this query: select bugID, max(bugHistoryID) as maxHistoryID from BugHistory group by bugID You save the results of this query into a temporary table (t), and then join the other tables on t to get the data you need from the appropriate records. Thus, in the stored procedure shown above, you get the Description, Version, etc. for the appropriate records that match the BugID and bugHistoryID from the temporary table (t).

The second important thing to note about this stored procedure is that it not only retrieves the values to be displayed, it also carefully retrieves the IDs of the fields as they appear in Bugs and BugHistory. That is, not only do you retrieve the severity description (High, Medium, Low) to display in the grid, but you also retrieve the corresponding severity ID values (5, 4, 3) as they are stored in the underlying records. This is important because in this example you will update these records, and you'll need the IDs to appear in the table you have created in the dataset. If the user indicates he wants to change the severity from High to Medium, your update will actually change the value from 5 to 4.

Once a command object that can invoke the new stored procedure is created, as shown in the previous C# code fragment, a new data adapter is created and the SelectCommand property is manually set to that command object, as shown in the following code fragment:

SqlDataAdapter dataAdapter = new SqlDataAdapter(  );
dataAdapter.SelectCommand=command;

You then add a new TableMapping object to the TableMappings collection to map the results of the stored procedure to a table within the BugInfo dataset named BugInfo:

dataAdapter.TableMappings.Add("Table","BugInfo");

It is critical to understand that to the dataset BugInfo appears as a single table, consisting of the fields and values returned by the stored procedure. The dataset, in this example, is oblivious to the underlying data structure of multiple interrelated tables.

Finally, a new dataset is created and filled using the data adapter you've crafted:

DataSet dataSet = new DataSet(  );
dataAdapter.Fill(dataSet);

12.3.2.2 Persisting the dataset

Later in this example, you will modify this dataset in response to the user pressing a button. When the user presses the button, however, there is a round trip to the server, and web pages are stateless. Thus, just as you are ready to update the dataset, it is gone (poof!), disappearing in a puff of stateless smoke.

You have a number of options for dealing with this problem. First, you can recreate the dataset by reissuing the query. This is not a great solution, not least because, after you update the dataset, you'll want to write it back to the database—and you must hold on to the dataset at that point in order to know what changes to write. In short, you can't recreate the dataset; you need to persist it.

For this example, you'll persist the dataset to session state. Session state is covered in detail in Chapter 6, but writing and retrieving is dead simple. As soon as you get back the DataSet object from the CreateBugDataSet method, you save it in a session variable named BugsDataSet. To do this, you include the following code in the Page_Load method:

DataSet ds = CreateBugDataSet(  );
Session["BugsDataSet"] = ds;

The VB.NET equivalent is:

Dim ds As DataSet = CreateBugDataSet
Session("BugsDataSet") = ds

In the handler for the Update DataSet button, you'll retrieve the dataset from session state. Remember that session state stores objects, and so you will have to cast the dataset back to its correct type:

DataSet ds = (DataSet) Session["BugsDataSet"];
DataTable bugTable = ds.Tables["BugInfo"];

In VB.NET, use:

ds = CType(Session("BugsDataSet"), DataSet)
Dim bugTable As DataTable = ds.Tables("BugInfo")

Hey! Presto! You have the same dataset after your round trip. Even better, because Session objects are stored on the server, the dataset did not make the round trip.

12.3.2.3 Updating the records in the dataset

There are many ways to allow the user to indicate how he wants to modify the data. This example ignores all user interface issues (which are covered in Chapter 10 and Chapter 13) and focuses on interacting with the data. To keep things simple, you'll have only two buttons: Update DataSet and Update Database.

The event handler for the first button, Update DataSet, implements hard-coded changes to the data in the dataset and then draws a second grid showing the changes. This will have no effect on the underlying database. If you close the web page after updating and displaying these changes, the database tables will be unaffected. The second button, Update Database, writes the changes to the dataset back to the database.

The user interface is bare bones. As shown in Figure 12-5, the web page opens by displaying data from the Bugs and BugHistory tables with two rather ugly buttons above the data grid.

In a real application, the user may indicate changes to the dataset in any number of ways. You might provide buttons and links to allow the user to interact with the data directly in the data grid. Or you might provide a form for adding and changing the data. For this application, as indicated earlier, you'll just hardwire a number of changes that will mimic the changes that might be requested by a user. You provide only a single button, Update DataSet, as described in the following section.

12.3.2.4 Updating the dataset

When a user clicks on the Update DataSet button, the btnUpdateDataSet_Click event handler is called. In the previous step, you stashed the dataset away in session state; you retrieve it now, using the code:

DataSet ds = (DataSet) Session["BugsDataSet"];

In VB.NET, it is:

Dim ds As DataSet = CType(Session("BugsDataSet"), DataSet)

With the dataset in hand, you can extract the table you created earlier named BugInfo:

DataTable bugTable = ds.Tables["BugInfo"];

In VB.NET, it is:

Dim bugTable As DataTable = ds.Tables("BugInfo")

You are now ready to edit, insert, and delete values. The DataRow class has an Item property that returns the data stored in a specified column. Because this is implemented as the indexer in C#, you can access the value for a particular field in a given row by providing the row offset and the field name. For example, the following line of code will change the Response value in the first row (remember that in C# and VB.NET arrays are zero-indexed) to the value This is a test:

bugTable.Rows[0]["Response"] = "This is a test";

In VB.NET, Item is the default property of the DataRow class. Hence, the VB.NET code is similar to the C# code:

bugTable.Rows(0)("Response") = "This is a test"

You can delete a row by calling the Delete method on the row itself:

bugTable.Rows[1].Delete(  );

You add a new row using exactly the same syntax you saw for creating new data rows by hand in Chapter 11:

DataRow newRow = bugTable.NewRow(  );
newRow["Description"] = "New bug test";
newRow["Response"] = "Created new bug";
newRow["Owner"] = "Jesse Liberty";
newRow["OwnerID"] = 1;
newRow["ProductID"] = 2; 
newRow["ProductDescription"] = "PIM - My Personal Infomation Manager";
newRow["Version"] = "0.01";
newRow["ReporterID"] = 3;
newRow["Reporter"] = "John Galt";
newRow["StatusID"] = 1;
newRow["StatusDescription"] = "open";
newRow["SeverityID"] = 2;
newRow["SeverityDescription"] = "High";
newRow["DateStamp"] = "07-27-2005";
bugTable.Rows.Add(newRow);

In VB.NET, the code looks like:

Dim newRow As DataRow = bugTable.NewRow(  )
newRow("Description") = "New bug test"
newRow("Response") = "Created new bug"
newRow("Owner") = "Jesse Liberty"
newRow("OwnerID") = 1
newRow("ProductID") = 2
newRow("ProductDescription") = "PIM - My Personal Infomation Manager"
newRow("Version") = "0.01"
newRow("ReporterID") = 3
newRow("Reporter") = "John Galt"
newRow("StatusID") = 1
newRow("StatusDescription") = "open"
newRow("SeverityID") = 2
newRow("SeverityDescription") = "High"
newRow("DateStamp") = "07-27-2005"
bugTable.Rows.Add(newRow)

Keep in mind that you're filling the BugInfo table in the dataset that was created by calling the spBugsWithIDs stored procedure. You must add a field for every field in the resulting set returned by that sproc.

It is up to you, as the programmer, to ensure the data integrity of the hand-created rows. For example, nothing stops you from adding a SeverityID of 4 (normally Low) with a SeverityDescription of High, except that if you do you will display a value to the user that will not correspond to the value with which you'll update the database!

Once you've made all the changes to the dataset, you will bind a second grid (DataGrid2) to the BugInfo table and make that grid visible, so that the user can see the new values, as shown in Figure 12-6.

Figure 12-6. DataGrids showing change to DataSet

Notice in Figure 12-6 that the first record has been updated with a new Most Recent Action value. This new value reflects the change to the Response field:

bugTable.Rows[0]["Response"] = "This is a test";

In VB.NET, it is:

bugTable.Rows(0)("Response") = "This is a test"

The Most Recent Action value is highlighted in the image of Figure 12-6 to make it easier for you to locate the change.

BugID 2, which was the second record (bugTable.Rows[1]), appears to have been deleted. In fact, it has only been marked for deletion, but the data grid is smart enough not to display records marked for deletion.

A new record has been added, as shown on the final line in the grid. Notice that there is no BugID. (Looking back at the example, you will note that you did not provide a BugID.) The BugID field is an identity column, which will be provided by the database when you write this data back to the database.

The absence of a BugID illustrates quite clearly that while you've updated the dataset, you have not yet written these changes back to the database. You can prove this to yourself by examining the tables in the database directly, as shown in Figure 12-7.

Figure 12-7. Bug and history table after dataset update, but before database update

12.3.2.5 Updating in the database

When the user clicks on the second button, Update Database, the btnUpdateDataBase_Click event handler is invoked. Your goal in this method is to update the database with the changes in the dataset.

The dataset keeps track of the changes to its data. You can update the database with all the changes just by calling the Update method on the DataAdapter, passing in a reference to the DataSet object and the name of the table you want to update.

That said, there is a bit of preparation work. For the update to work, you first need to provide command objects to the InsertCommand, UpdateCommand, and DeleteCommand properties of the data adapter. We'll examine each of these preparatory steps in the following sections.

12.3.2.5.1 The delete command

As indicated earlier, you must begin by creating the appropriate stored procedures. Example 12-9 shows the spDeleteBugFromDataSet stored procedure for deleting bug records. If the user deletes a record from the grid, he intends to delete all record of that bug. Because of referential integrity, you must first remove all records from that bug within BugHistory, and then you may remove the record from the Bugs table.

Example 12-9. The stored procedure to delete bugs

CREATE PROCEDURE spDeleteBugFromDataSet 
@BugID int 
AS 
DELETE FROM BugHistory WHERE BugID = @BugID 
DELETE FROM Bugs WHERE BugID = @BugID 
GO

Notice that you will pass in a single parameter that will be used to identify the record to delete. With this stored procedure, you are ready to create the Command object you will assign to the DataAdapters DeleteCommand property.

You begin by creating a new SqlCommand object:

SqlCommand deleteCmd = 
   new SqlCommand("spDeleteBugFromDataSet",connection);
deleteCmd.CommandType=CommandType.StoredProcedure;

In VB.NET, it is:

Dim deleteCmd As New SqlCommand("spDeleteBugFromDataSet", myConnection)
deleteCmd.CommandType = CommandType.StoredProcedure

This SqlCommand object is just like every command object you've created to date. You will name it deleteCmd to make it easy to identify, but it is just a garden-variety SqlCommand object, just like all the others you've used so far to invoke stored procedures.

You'll add a single parameters BugID. This is an input parameter, but this time rather than assigning a value to it, you must set two new properties of the Parameter object, SourceColumn and SourceVersion. The SourceColumn property identifies the column within the table in the dataset that this parameter will get its value from. That is, when you invoke the stored procedure, the parameter (@BugID) will draw its value from this column in the record to be deleted. The column you want, of course, is BugID:

param.SourceColumn="bugID";

The second property of the parameter is the SourceVersion, which must be set to one of the DataRowVersion enumerated values (Current, Default, Original, or Proposed).

The Default value is used only when you wish to use a default value, which does not apply to this example.

The Original value is the value the field had when the dataset was created. The original value is compared to the value in the database when the update is performed to see if the database has been changed by another process. This is covered later in Section 12-4, Multiuser Updates.

The Current value holds the changes to the column you've made since the dataset was created. That is, as you update columns, the Current value holds the changes you've made, while the Original value has the value as you originally obtained it from the database.

In the case of the BugID, you'll tell the Param to use the Original value (though of course since you've not changed the value, you can use the Current value as well):

param.SourceVersion=DataRowVersion.Original;

You are now ready to assign the command object to the DeleteCommand property of the data adapter:

dataAdapter.DeleteCommand=deleteCmd;

12.3.2.5.2 The update command

The stored procedure for updating the database is somewhat more complicated than the procedure for deleting records. This time, you want to pass in parameters for each of the fields that may be changed. You will also pass in the BugID and BugHistory ID to uniquely identify the bug you wish to alter. The complete stored procedure is shown in Example 12-10.

Example 12-10. The stored procedure for updating a bug

CREATE PROCEDURE spUpdateBugFromDataSet
@ProductID int,
@Description varChar(8000),
@Response varChar(8000),
@Reporter int,
@Owner int,
@Status int,
@Severity int,
@bugID int,
@BugHistoryID int
as
Update Bugs 
set 
       Product = @productID,
       [Description] = @Description,
       Reporter = @Reporter
       where bugID = @BugID

Update BugHistory 
Set
       bugID = @BugID, 
       status = @Status, 
       severity = @Severity, 
       response = @Response, 
       owner = @Owner
where BugHistoryID = @bugHistoryID and bugID = @bugID
GO

Once again you create a command object, this time to hold the Update command stored procedure:

SqlCommand updateCmd = 
   new SqlCommand("spUpdateBugFromDataSet",connection);
updateCmd.CommandType=CommandType.StoredProcedure;

In VB.NET, it is:

Dim updateCmd As New SqlCommand("spUpdateBugFromDataSet", myConnection)
updateCmd.CommandType = CommandType.StoredProcedure

You'll add a SqlParameter object for each parameter to the stored procedure:

param = updateCmd.Parameters.Add("@ProductID",SqlDbType.Int); 
param.Direction = ParameterDirection.Input;
param.SourceColumn="ProductID";
param.SourceVersion=DataRowVersion.Current;

The ProductID parameter is like the BugID parameter, except that now you use the enumerated value DataRowVersion.Current for the SourceVersion property. You will use Current for any value that may have been changed in the dataset; this instructs the data adapter to update the dataset with the value current in the dataset, rather than with the value that may reside back in the database.

When you create the parameters for the Reporter, Owner, Status, and Severity fields, you must be careful to use the ReporterID, OwnerID, StatusID, and SeverityID SourceColumns, respectively. Remember that while you are displaying the full names of the reporter and owner, and the text value of the status and severity, the records you are updating in the Bugs and BugHistory tables use the ID.

12.3.2.5.3 The insert command

The final command you'll need to implement is the insert command. You start, once again, by creating the necessary stored procedure, as shown in Example 12-11.

Example 12-11. The stored procedure spInsertBugFromDataSet

CREATE PROCEDURE spInsertBugFromDataSet
@ProductID int,
@Version varChar(50),
@Description varChar(8000),
@Response varChar(8000),
@Reporter int,
@Owner int,
@Status int,
@Severity int
as
declare @bugID int
Insert into Bugs values (@ProductID, @Version, @Description, @Reporter)
select @bugID = @@identity
Insert into BugHistory 
(bugID, status, severity, response, owner)
values 
( @bugID,
       @status,        -- status
       @Severity,
       @response,
       @owner
)
GO

You must remember to insert into the Bugs table before inserting into the BugHistory table because referential integrity constraints require that the BugID must exist in Bugs before it can be inserted into BugHistory.

Note that you do not pass in either the BugID nor the BugHistoryID as parameters; these are created by the database itself. The BugHistory table requires the BugID generated by adding a record to Bugs; you obtain this value from @@identity.

It is this stored procedure that will be called to insert the record you created by hand in the btnUpdateDataSet_Click event procedure. Once again, you must create a command object, this time for the InsertCommand property of the DataAdapter object:

param = insertCmd.Parameters.Add("@ProductID",SqlDbType.Int);

Once again, you create all the parameters and set their values. You then assign the command object to the DataAdapter object's InsertCommand property:

dataAdapter.InsertCommand=insertCmd;

12.3.2.6 Adding transaction support

It is possible for one or another of the updates to fail, and if they do not all fail, it can be difficult to return the database to a valid state. You will, therefore, add transaction support. You start, as last time, by obtaining a reference to a SqlTransaction object by calling BeginTransaction on the connection object:

SqlTransaction transaction;
connection.Open(  );
transaction = connection.BeginTransaction(  );

In VB.NET, it is:

Dim transaction As SqlTransaction
myConnection.Open(  )
transaction = myConnection.BeginTransaction(  )

With all three Command properties set, you can add the transaction to each command's Transaction property:

dataAdapter.UpdateCommand.Transaction = transaction;
dataAdapter.DeleteCommand.Transaction = transaction;
dataAdapter.InsertCommand.Transaction = transaction;

12.3.2.7 Calling the Update method

You are now ready to call the Update method of the SqlDataAdapter object, which you will do from within a try block. The Update method will return the number of rows that are updated, which you will use to fill in the text of a label at the bottom of the data grid. The code is as follows:

try
{
   int rowsUpdated = dataAdapter.Update(ds,"BugInfo");
   transaction.Commit(  );
   CountUpdatedRows.Visible=true;
   CountUpdatedRows.Text = 
     rowsUpdated.ToString(  ) + " rows Updated.";
}
catch
{
   transaction.Rollback(  );
}

In VB.NET, it is as follows:

Try
   Dim rowsUpdated As Int32
   rowsUpdated = CType(dataAdapter.Update(ds, "BugInfo"), Int32)
   transaction.Commit(  )
   ' transaction.Rollback(  )
   CountUpdatedRows.Visible = True
   CountUpdatedRows.Text = rowsUpdated.ToString(  ) + " rows Updated."

Catch
   transaction.Rollback(  )

End Try

If no exception is thrown, you commit the transactions; otherwise, you roll them back.

You can then rebind to the data grid, which will remain unchanged. The label is now visible, however, showing the number of rows that were updated, as shown in Figure 12-8. (The label is highlighted in the figure to make it easy to find.)

Figure 12-8. After updating the database

If you examine the Bugs and BugHistory tables, you should now see that the data has been updated, as shown in Figure 12-9.

Figure 12-9. Bug and BugHistory table after database update

Most of the methods in this example are unchanged from earlier listings. The important changes are in declaring the user interface elements and three methods: Page_Load, btnUpdateDataSet_Click, and btnUpdateDataBase_Click. These changes are shown in Example 12-12 (C#) and Example 12-13 (VB.NET).

Example 12-12. Updating the database from a dataset (C#)

protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.DataGrid DataGrid2;
protected System.Web.UI.WebControls.DataGrid HistoryGrid;
protected System.Web.UI.WebControls.Panel BugHistoryPanel;
protected System.Web.UI.WebControls.Panel DataGrid2Panel;
protected System.Web.UI.WebControls.Button btnUpdateDataSet;
protected System.Web.UI.WebControls.Button btnUpdateDataBase;
protected System.Web.UI.WebControls.Label CountUpdatedRows;

private void Page_Load(
   object sender, System.EventArgs e)
{
   if (!IsPostBack)
   {
      // hide the history panel
      UpdateBugHistory(  );
      DataGrid2Panel.Visible=false;

      // set the data source for the 
      // grid to the first table 
      DataSet ds = CreateBugDataSet(  );
      Session["BugsDataSet"] = ds;
      DataGrid1.DataSource=ds.Tables[0];
      DataGrid1.DataBind(  );
   }      
}
// respond to the request to update
 // the dataset. This would normally be
 // replaced by a complete user interface to allow
 // the user to specify what changes to make
 private void btnUpdateDataSet_Click(object sender, System.EventArgs e)
 {
    // retrieve the dataset from session state
    DataSet ds = (DataSet) Session["BugsDataSet"];

    // extract the table of Bug and BugHistory information
    DataTable bugTable = ds.Tables["BugInfo"];

    // change one field in row 0
    bugTable.Rows[0]["Response"] = "This is a test";

    // delete row 1
    bugTable.Rows[1].Delete(  );
    
    // append a new row
    DataRow newRow = bugTable.NewRow(  );
    newRow["Description"] = "New bug test";
    newRow["Response"] = "Created new bug";
    newRow["Owner"] = "Jesse Liberty";
    newRow["OwnerID"] = 1;
    newRow["ProductID"] = 2; 
    newRow["ProductDescription"] = 
       "PIM - My Personal Infomation Manager";
    newRow["Version"] = "0.01";
    newRow["ReporterID"] = 3;
    newRow["Reporter"] = "John Galt";
    newRow["StatusID"] = 1;
    newRow["StatusDescription"] = "open";
    newRow["SeverityID"] = 2;
    newRow["SeverityDescription"] = "High";
    newRow["DateStamp"] = "07-27-2005";
    bugTable.Rows.Add(newRow);

    // update two fields in row 2 - note we update the id
    // for writing back to the db. We are responsible
    // for ensuring that the id matches the description
    bugTable.Rows[2]["SeverityID"] = 5;
    bugTable.Rows[2]["SeverityDescription"] = "Trivial";

    // bind the DataSet to the second data grid
    // and make it visible
    DataGrid2.DataSource = ds.Tables["BugInfo"];
    DataGrid2.DataBind(  );
    DataGrid2Panel.Visible=true;
    Session["BugsDataSet"] = ds;
}

private void btnUpdateDataBase_Click(object sender, System.EventArgs e)
{
   DataSet ds = (DataSet) Session["BugsDataSet"];
   SqlDataAdapter dataAdapter = new SqlDataAdapter(  );

   string connectionString = 
      "server=YourServer; uid=sa; 
              pwd=YourPassword; database=ProgASPDotNetBugs";

   // Create connection object, initialize with 
   // connection string. Open it.
   System.Data.SqlClient.SqlConnection connection = 
      new System.Data.SqlClient.SqlConnection(connectionString);

   SqlTransaction transaction;
   connection.Open(  );
   transaction = connection.BeginTransaction(  );

   // *** create the update command object
   SqlCommand updateCmd = 
      new SqlCommand("spUpdateBugFromDataSet",connection);
   updateCmd.CommandType=CommandType.StoredProcedure;

   // declare the parameter object
   System.Data.SqlClient.SqlParameter param;

   // Add new parameters, get back a reference 
   // set the parameters' direction and value
   param = 
      updateCmd.Parameters.Add("@ProductID",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="ProductID";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      updateCmd.Parameters.Add("@Description",SqlDbType.Text,8000); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="Description";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      updateCmd.Parameters.Add("@Response",SqlDbType.Text,8000); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="Response";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      updateCmd.Parameters.Add("@Reporter",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="ReporterID";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      updateCmd.Parameters.Add("@Owner",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="OwnerID";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      updateCmd.Parameters.Add("@Status",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="StatusID";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      updateCmd.Parameters.Add("@Severity",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="SeverityID";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      updateCmd.Parameters.Add("@bugID",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="bugID";
   param.SourceVersion=DataRowVersion.Original; // note Original

   param = 
      updateCmd.Parameters.Add("@BugHistoryID",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="BugHistoryID";
   param.SourceVersion=DataRowVersion.Original; // note Original

   dataAdapter.UpdateCommand=updateCmd;

   // *** the delete command
   SqlCommand deleteCmd = 
      new SqlCommand("spDeleteBugFromDataSet",connection);
   deleteCmd.CommandType=CommandType.StoredProcedure;

   param = deleteCmd.Parameters.Add("@bugID",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="bugID";
   param.SourceVersion=DataRowVersion.Original;  // note Original

   param = deleteCmd.Parameters.Add("@BugHistoryID",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="BugHistoryID";
   param.SourceVersion=DataRowVersion.Original;  // note Original

   dataAdapter.DeleteCommand=deleteCmd;

   // *** insert command
   SqlCommand insertCmd = 
      new SqlCommand("spInsertBugFromDataSet",connection);
   insertCmd.CommandType=CommandType.StoredProcedure;

   // Add new parameters, get back a reference 
   // set the parameters' direction and value
   param = insertCmd.Parameters.Add("@ProductID",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="ProductID";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      insertCmd.Parameters.Add("@Version",SqlDbType.Text,50); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="Version";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      insertCmd.Parameters.Add("@Description",SqlDbType.Text,8000); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="Description";
   param.SourceVersion=DataRowVersion.Current;

   param = 
      insertCmd.Parameters.Add("@Response",SqlDbType.Text,8000); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="Response";
   param.SourceVersion=DataRowVersion.Current;

   param = insertCmd.Parameters.Add("@Reporter",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="ReporterID";
   param.SourceVersion=DataRowVersion.Current;

   param = insertCmd.Parameters.Add("@Owner",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="OwnerID";
   param.SourceVersion=DataRowVersion.Current;

   param = insertCmd.Parameters.Add("@Status",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="StatusID";
   param.SourceVersion=DataRowVersion.Current;

   param = insertCmd.Parameters.Add("@Severity",SqlDbType.Int); 
   param.Direction = ParameterDirection.Input;
   param.SourceColumn="SeverityID";
   param.SourceVersion=DataRowVersion.Current;
   
   dataAdapter.InsertCommand=insertCmd;

   // add transaction support for each command
   dataAdapter.UpdateCommand.Transaction = transaction;
   dataAdapter.DeleteCommand.Transaction = transaction;
   dataAdapter.InsertCommand.Transaction = transaction;

   // try to update, if all succeed commit
   // otherwise roll back
   try
   {
      int rowsUpdated = dataAdapter.Update(ds,"BugInfo");
      transaction.Commit(  );
      CountUpdatedRows.Visible=true;
      CountUpdatedRows.Text = rowsUpdated.ToString(  ) + " rows Updated.";
   }
   catch
   {
      transaction.Rollback(  );
   }

   // rebind the grid to show the results
   // grid should be unchanged
   DataGrid2.DataSource = ds.Tables["BugInfo"];
   DataGrid2.DataBind(  );
}

Example 12-13. Updating the database from a dataset (VB.NET)

Private Sub Page_Load( _
    ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles MyBase.Load
    If Not IsPostBack Then

       ' hide the history panel
       UpdateBugHistory(  )
       DataGrid2Panel.Visible = False

       ' set the data source for the 
       ' grid to the first table 
       Dim ds As DataSet = CreateBugDataSet
       Session("BugsDataSet") = ds
       DataGrid1.DataSource = ds.Tables(0)
       DataGrid1.DataBind(  )
    End If
Private Sub btnUpdateDataSet_Click( _
   ByVal sender As Object, ByVal e As System.EventArgs)

   ' retrieve the dataset from session state
   Dim ds As DataSet
   ds = CType(Session("BugsDataSet"), DataSet)

   ' extract the table of Bug and BugHistory information
   Dim bugTable As DataTable = ds.Tables("BugInfo")

   ' change one field in row 0
   bugTable.Rows(0)("Response") = "This is a test"

   ' delete row 1
   bugTable.Rows(1).Delete(  )

   ' append a new row
   Dim newRow As DataRow = bugTable.NewRow(  )
   newRow("Description") = "New bug test"
   newRow("Response") = "Created new bug"
   newRow("Owner") = "Jesse Liberty"
   newRow("OwnerID") = 1
   newRow("ProductID") = 2
   newRow("ProductDescription") = _
      "PIM - My Personal Infomation Manager"
   newRow("Version") = "0.01"
   newRow("ReporterID") = 3
   newRow("Reporter") = "John Galt"
   newRow("StatusID") = 1
   newRow("StatusDescription") = "open"
   newRow("SeverityID") = 2
   newRow("SeverityDescription") = "High"
   newRow("DateStamp") = "07-27-2005"
   bugTable.Rows.Add(newRow)

   ' update two fields in row 2 - note we update the id
   ' for writing back to the db. We are responsible
   ' for ensuring that the id matches the description
   bugTable.Rows(2)("SeverityID") = 5
   bugTable.Rows(2)("SeverityDescription") = "Trivial"

   ' bind the dataset to the second data grid
   ' and make it visible
   DataGrid2.DataSource = ds.Tables("BugInfo")
   DataGrid2.DataBind(  )
   DataGrid2Panel.Visible = True
   Session("BugsDataSet") = ds
End Sub
Private Sub btnUpdateDataBase_Click( _
   ByVal sender As Object, ByVal e As System.EventArgs)

   Dim ds As DataSet = CType(Session("BugsDataSet"), DataSet)
   Dim dataAdapter As New SqlDataAdapter(  )

   Dim connectionString As String = _
      "server=YourDB; uid=sa; pwd=YourPassword; " & _
      "database=ProgASPDotNetBugs"

   ' Create connection object, initialize with 
   ' connection string. Open it.
   Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString)

   Dim transaction As SqlTransaction
   myConnection.Open(  )
   transaction = myConnection.BeginTransaction(  )

   ' *** create the update command object
   Dim updateCmd As New SqlCommand("spUpdateBugFromDataSet", myConnection)
   updateCmd.CommandType = CommandType.StoredProcedure

   ' declare the parameter object
   Dim param As System.Data.SqlClient.SqlParameter

   ' Add new parameters, get back a reference 
   ' set the parameters' direction and value
   param = updateCmd.Parameters.Add("@ProductID", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "ProductID"
   param.SourceVersion = DataRowVersion.Current

   param = updateCmd.Parameters.Add("@Description", SqlDbType.Text, 8000)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "Description"
   param.SourceVersion = DataRowVersion.Current

   param = updateCmd.Parameters.Add("@Response", SqlDbType.Text, 8000)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "Response"
   param.SourceVersion = DataRowVersion.Current

   param = updateCmd.Parameters.Add("@Reporter", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "ReporterID"
   param.SourceVersion = DataRowVersion.Current

   param = updateCmd.Parameters.Add("@Owner", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "OwnerID"
   param.SourceVersion = DataRowVersion.Current

   param = updateCmd.Parameters.Add("@Status", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "StatusID"
   param.SourceVersion = DataRowVersion.Current

   param = updateCmd.Parameters.Add("@Severity", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "SeverityID"
   param.SourceVersion = DataRowVersion.Current

   param = updateCmd.Parameters.Add("@bugID", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "bugID"
   param.SourceVersion = DataRowVersion.Original ' note Original

   param = updateCmd.Parameters.Add("@BugHistoryID", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "BugHistoryID"
   param.SourceVersion = DataRowVersion.Original ' note Original

   dataAdapter.UpdateCommand = updateCmd

   ' *** the delete command
   Dim deleteCmd As New SqlCommand("spDeleteBugFromDataSet", myConnection)
   deleteCmd.CommandType = CommandType.StoredProcedure

   param = deleteCmd.Parameters.Add("@bugID", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "bugID"
   param.SourceVersion = DataRowVersion.Original ' note Original

   param = deleteCmd.Parameters.Add("@BugHistoryID", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "BugHistoryID"
   param.SourceVersion = DataRowVersion.Original ' note Original

   dataAdapter.DeleteCommand = deleteCmd

   ' *** insert command
   Dim insertCmd As New SqlCommand("spInsertBugFromDataSet", myConnection)
   insertCmd.CommandType = CommandType.StoredProcedure

   ' Add new parameters, get back a reference 
   ' set the parameters' direction and value
   param = insertCmd.Parameters.Add("@ProductID", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "ProductID"
   param.SourceVersion = DataRowVersion.Current

   param = insertCmd.Parameters.Add("@Version", SqlDbType.Text, 50)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "Version"
   param.SourceVersion = DataRowVersion.Current

   param = insertCmd.Parameters.Add("@Description", SqlDbType.Text, 8000)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "Description"
   param.SourceVersion = DataRowVersion.Current

   param = insertCmd.Parameters.Add("@Response", SqlDbType.Text, 8000)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "Response"
   param.SourceVersion = DataRowVersion.Current

   param = insertCmd.Parameters.Add("@Reporter", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "ReporterID"
   param.SourceVersion = DataRowVersion.Current

   param = insertCmd.Parameters.Add("@Owner", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "OwnerID"
   param.SourceVersion = DataRowVersion.Current

   param = insertCmd.Parameters.Add("@Status", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "StatusID"
   param.SourceVersion = DataRowVersion.Current

   param = insertCmd.Parameters.Add("@Severity", SqlDbType.Int)
   param.Direction = ParameterDirection.Input
   param.SourceColumn = "SeverityID"
   param.SourceVersion = DataRowVersion.Current

   dataAdapter.InsertCommand = insertCmd

   ' add transaction support for each command
   dataAdapter.UpdateCommand.Transaction = transaction
   dataAdapter.DeleteCommand.Transaction = transaction
   dataAdapter.InsertCommand.Transaction = transaction

   ' try to update, if all succeed commit
   ' otherwise roll back
   Try
      Dim rowsUpdated As Int32
      rowsUpdated = CType(dataAdapter.Update(ds, "BugInfo"), Int32)
      transaction.Commit(  )
      ' transaction.Rollback(  )
      CountUpdatedRows.Visible = True
      CountUpdatedRows.Text = rowsUpdated.ToString(  ) + " rows Updated."

   Catch
      transaction.Rollback(  )

   End Try

   ' rebind the grid to show the results
   ' grid should be unchanged
   DataGrid2.DataSource = ds.Tables("BugInfo")
   DataGrid2.DataBind(  )
End Sub