Updating Data Using DataSets

So far in this chapter, you have seen how to update a database and add transactions to ensure data integrity. All of that is fine, but nothing you've done so far to update the database uses the DataSet object

If you are using the DataSet object to retrieve data and pass it from tier to tier within your application, you can also 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 need to take advantage of the advanced capabilities of the DataSet and the DataAdapter classes and understand how they in turn use the Command and Connection objects to mediate between the DataSet and the database itself.

In the next application, UpdatingDataSets, shown in Figure 20-5, you will retrieve the contents of the Bug and Bug History databases in a DataSet and display the DataSet in a grid. You will then update the DataSet, and optionally update the database from the updated DataSet.

To create this application, start a new WinForm project and add the following four controls to the form, as shown in Table 20-2.

Table 20-2. Controls for updating through the DataSet

Control	Name	Text
Button	btnUpdateDS	Update DataSet
Button	btnRefreshDS	Refresh DataSet
Button	btnUpdateDB	Update Database
DataGrid	dgBugs

Figure 20-5. DataSet update of database

The complete listing in C# is shown in Example 20-8 and in VB.NET in Example 20-9. An analysis follows.

Example 20-8. Updating with the DataSet (C#)

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
 
namespace UpdatingDataSets
{
 public class Form1 : System.Windows.Forms.Form
 {
 private System.Windows.Forms.DataGrid dgBugs;
 private System.Windows.Forms.Button btnUpdateDS;
 private System.Windows.Forms.Button btnRefreshDS;
 private System.Windows.Forms.Button btnUpdateDB;
 private DataSet bugDS;
 
 private System.ComponentModel.Container components = null;
 
 public Form1( )
 {
 InitializeComponent( );
 RefreshDataSet( );
 }
 
 protected override void Dispose( bool disposing )
 {
 if( disposing )
 {
 if (components != null) 
 {
 components.Dispose( );
 }
 }
 base.Dispose( disposing );
 }
 
 #region Windows Form Designer generated code
 #endregion
 
 [STAThread]
 static void Main( ) 
 {
 Application.Run(new Form1( ));
 }
 
 private DataSet CreateBugDataSet( )
 {
 // connection string to connect to the Bugs Database
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPW; " + 
 " database=WindForm_Bugs";
 
 // Create connection object, initialize with 
 // connection string. Open it.
 System.Data.SqlClient.SqlConnection connection = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 
 
 // 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 data set and use the data adapter to fill it
 DataSet dataSet = new DataSet( );
 dataAdapter.Fill(dataSet);
 return dataSet;
 } // close CreateBugDataSet
 
 // Update the dataset with bogus data
 private void btnUpdateDS_Click(object sender, System.EventArgs e)
 {
 DataTable bugTable = bugDS.Tables["BugInfo"];
 bugTable.Rows[0]["Response"] = "This is a test";
 bugTable.Rows[1].Delete( );
 DataRow newRow = bugTable.NewRow( );
 newRow["BugHistoryID"] = 1;
 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);
 
 } // close btnUpdateDS_Click
 
 private void btnRefreshDS_Click(object sender, System.EventArgs e)
 {
 RefreshDataSet( );
 }
 
 private void RefreshDataSet( )
 {
 bugDS = CreateBugDataSet( );
 dgBugs.DataSource = bugDS.Tables[0];
 }
 
 private void btnUpdateDB_Click(object sender, System.EventArgs e)
 {
 SqlDataAdapter dataAdapter = new SqlDataAdapter( );
 
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPW; " + 
 " database=WindForm_Bugs";
 
 // 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;
 
 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(bugDS,"BugInfo");
 transaction.Commit( );
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.");
 RefreshDataSet( );
 }
 catch (Exception ex)
 {
 MessageBox.Show("Unable to update db!" + ex.Message);
 transaction.Rollback( );
 }
 
 // rebind the grid to show the results
 // grid should be unchanged
 dgBugs.DataSource = bugDS.Tables["BugInfo"]; 
 } // close btnUpdateDB_Click
 }
}

Example 20-9. Updating with the DataSet (VB.NET)

Imports System.Data.SqlClient
Public Class Form1
 Inherits System.Windows.Forms.Form
 
 Dim bugDS As DataSet
 
#Region " Windows Form Designer generated code "
 
 Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 RefreshDataSet( )
#End Region
 
 Private Function CreateBugDataSet( ) As DataSet
 ' myConnection string to connect to the Bugs Database
 Dim connectionString As String = "server=YourServer; uid=sa; " + _
 " pwd=YourPW; database=WindForm_Bugs"
 
 ' Create myConnection object, initialize with 
 ' myConnection string. Open it.
 Dim myConnection As New _
 System.Data.SqlClient.SqlConnection(connectionString)
 myConnection.Open( )
 
 
 ' Create a SqlCommand object and assign the myConnection
 Dim command As New System.Data.SqlClient.SqlCommand( )
 command.Connection = myConnection
 command.CommandText = "spBugsWithIDs"
 command.CommandType = CommandType.StoredProcedure
 
 ' create a data adapter and assign the command object
 ' and add the table mapping for bugs
 Dim myDataAdapter As New SqlDataAdapter( )
 myDataAdapter.SelectCommand = command
 myDataAdapter.TableMappings.Add("Table", "BugInfo")
 
 ' Create the data set and use the data adapter to fill it
 Dim myDataSet As New DataSet( )
 myDataAdapter.Fill(myDataSet)
 Return myDataSet
 End Function ' close CreateBugDataSet
 
 Private Sub RefreshDataSet( )
 bugDS = CreateBugDataSet( )
 dgBugs.DataSource = bugDS.Tables(0)
 End Sub
 
 ' Update the dataset with bogus data
 Private Sub btnUpdateDS_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnUpdateDS.Click
 Dim bugTable As DataTable = bugDS.Tables("BugInfo")
 bugTable.Rows(0)("Response") = "This is a test"
 bugTable.Rows(1).Delete( )
 
 Dim newRow As DataRow = bugTable.NewRow( )
 newRow("BugHistoryID") = 1
 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)
 
 End Sub
 
 Private Sub btnRefreshDS_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnRefreshDS.Click
 RefreshDataSet( )
 End Sub
 Private Sub btnUpdateDB_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnUpdateDB.Click
 Dim myDataAdapter As New SqlDataAdapter( )
 
 Dim connectionString As String = "server=YourServer; uid=sa; " + _
 " pwd=YourPW; database=WindForm_Bugs"
 
 ' Create myConnection object, initialize with 
 ' myConnection string. Open it.
 Dim myConnection As New 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
 
 myDataAdapter.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
 
 myDataAdapter.DeleteCommand = deleteCmd
 
 ' *** insert command
 Dim insertCmd As New SqlCommand("spInsertBugFromDataSet", myConnection)
 insertCmd.CommandType = CommandType.StoredProcedure
 
 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
 
 myDataAdapter.InsertCommand = insertCmd
 
 ' add transaction support for each command
 myDataAdapter.UpdateCommand.Transaction = transaction
 myDataAdapter.DeleteCommand.Transaction = transaction
 myDataAdapter.InsertCommand.Transaction = transaction
 
 ' try to update, if all succeed commit
 ' otherwise roll back
 Try
 Dim rowsUpdated As Int16 = myDataAdapter.Update(bugDS, "BugInfo")
 transaction.Commit( )
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.")
 RefreshDataSet( )
 Catch ex As Exception
 MessageBox.Show("Unable to update db!" + ex.Message)
 transaction.Rollback( )
 End Try
 
 ' rebind the grid to show the results
 ' grid should be unchanged
 dgBugs.DataSource = bugDS.Tables("BugInfo")
 
 End Sub ' close btnUpdateDB_Click
End Class

20.3.1 The DataSet and the DataAdapter

As explained in Chapter 19, the DataSet object interacts with the database through a DataAdapter object. The job of the DataAdapter is to decouple the DataSet from the underlying database (e.g., SqlServer or Oracle). The DataSet is a standalone representation of a subset of the database, including multiple tables and their relationships. The DataAdapter knows how to fill a DataSet from a given database.

Until now, you've created the DataAdapter by passing in a command string and a connection string to the DataAdapter's constructor and then calling the Fill( ) method.

The Fill( ) method does a lot of work on your behalf. The DataAdapter has, as properties, four SqlCommand objects at its disposal: DeleteCommand, InsertCommand, SelectCommand, and UpdateCommand. The job of the SelectCommand, for example, is to manage the selection statement. When you pass a selection command string in to the constructor, the DataAdapter's SelectCommand property is initialized to a SqlCommand object using that select string.

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 DataAdapter is told to update the database, it examines the changes to the DataSet and calls the appropriate Command objects to update, delete, or insert records. Often, a single request to a DataSet to update the database causes each command to be called repeatedly, once for each modified row.

20.3.2 Steps for Updating the Database

The steps for updating a database using a DataSet are as follows:

1. Create and display a DataSet by retrieving data from the database.
2. Update the records in the DataSet. This task might include adding new records, deleting records, and updating existing records.
3. Optionally, create stored procedures in the database to manage the select, update, insert, and delete commands.
4. Create Command objects to invoke the stored procedures or to pass in SQL commands. Add parameters to the Command objects as needed.
5. Add transaction support to ensure that either all or no updates are done.
6. Call the Update method on the data adapter. The data adapter examines the changes in the DataSet and calls the appropriate Command objects, which will update the database on your behalf.

20.3.2.1 Creating and displaying a DataSet

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

This DataGrid is created again by calling the CreateBugDataSet method:

private DataSet CreateBugDataSet( )
{
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs";
 
 System.Data.SqlClient.SqlConnection connection = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 
 System.Data.SqlClient.SqlCommand command = 
 new System.Data.SqlClient.SqlCommand( );
 command.Connection=connection;
 command.CommandText="spBugsWithIDs";
 command.CommandType=CommandType.StoredProcedure;
 
 SqlDataAdapter dataAdapter = new SqlDataAdapter( );
 dataAdapter.SelectCommand=command;
 dataAdapter.TableMappings.Add("Table","BugInfo");
 
 DataSet dataSet = new DataSet( );
 dataAdapter.Fill(dataSet);
 return dataSet;
}

Private Function CreateBugDataSet( ) As DataSet
 Dim connectionString As String = _
 "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"
 
 Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString)
 myConnection.Open( )
 
 
 Dim command As New System.Data.SqlClient.SqlCommand( )
 command.Connection = myConnection
 command.CommandText = "spBugsWithIDs"
 command.CommandType = CommandType.StoredProcedure
 
 Dim myDataAdapter As New SqlDataAdapter( )
 myDataAdapter.SelectCommand = command
 myDataAdapter.TableMappings.Add("Table", "BugInfo")
 
 Dim myDataSet As New DataSet( )
 myDataAdapter.Fill(myDataSet)
 Return myDataSet
End Function

The DataSet is created with a SqlCommand object, which in turn invokes the stored procedure spBugsWithIDs, shown in Example 20-10. You should note two important things in this stored procedure. First, the data displayed in the grid is 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 Sidebar 20-3).

Example 20-10. The stored procedure spBugsWithIDs

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 find the highest BugHistoryID for each bug? Use 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

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.

Second, this stored procedure both retrieves the values to be displayed and 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, or Low) to display in the grid, but you also retrieve the corresponding severity ID values (5, 4, or 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 users indicate that they want to change the severity from High to Medium, your update will 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 code fragment, a new data adapter is created and the SelectCommand property is set manually to that Command object, as shown in the following code fragment:

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

Dim myDataAdapter As New SqlDataAdapter( )
myDataAdapter.SelectCommand = command

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");

myDataAdapter.TableMappings.Add("Table", "BugInfo")

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 DataAdapter you've crafted:

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

Dim myDataSet As New DataSet( )
myDataAdapter.Fill(myDataSet)

20.3.2.2 Updating the records in the DataSet

There are many ways to allow the user to indicate how the data should be modified. This example ignores all user interface issues and focuses on interacting with the data. To keep things simple, you'll have only three buttons: Update DataSet, Refresh DataSet, and Update Database.

The event handler for the first button, UpdateDataSet, implements hardwired changes to the data in the data. This has no effect on the underlying database. If you close the form after updating and displaying these changes, the database tables will be unaffected. The second button, Refresh DataSet, restores the DataSet to the data in the database. Finally, the third button, Update Database, writes the changes you make to the DataSet back to the database.

20.3.2.3 Updating the DataSet

When a user clicks on the Update DataSet button, the btnUpdateDS_Click event handler is called. Extract the table from the bugDS DataSet, and you are ready to modify the table:

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

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

The DataTable contains a collection of DataRows. 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# and as the default property in VB.NET, 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 C# code changes the Response value in the first row (remember that in C#, arrays are zero-indexed) to the value This is a test:

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

In VB.NET, this code is nearly identical:

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

Delete a row by calling the Delete method on the row itself:

bugTable.Rows[1].Delete( );

Add a new row by using exactly the same syntax you saw for creating new data rows by hand in Chapter 19:

DataRow newRow = bugTable.NewRow( );
newRow["BugHistoryID"] = 1;
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);

Dim newRow As DataRow = bugTable.NewRow( )
newRow("BugHistoryID") = 1
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)

Remember 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 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!

Your changes are immediately visible in the datagrid, as shown in Figure 20-6.

Figure 20-6. After updating the DataSet

Notice that the first record has been updated in Figure 20-6. This new value is reflected in a change to the Response field:

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

BugID 2, which was the second record (bugTable.Rows[1]), appears to have been deleted. In fact, it was marked for deletion, but the datagrid 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. (When looking at the example, you will note that you did not provide a BugID.) The BugID field is an identity column, which is provided by the database when you write this data back to the database.

The absence of a BugID illustrates 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 20-7.

Figure 20-7. Bug and history table after the DataSet update, but before the database update

20.3.3 Updating the Database from the Dataset

When the user clicks on the third button, Update Database, the btnUpdateDB_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, and 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 DataAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties. You'll learn more about these preparatory steps in the following sections.

20.3.3.1 The delete command

As indicated earlier, you must begin by creating the appropriate stored procedures. Example 20-11 shows the spDeleteBugFromDataSet stored procedure for deleting bug records.

When the user deletes a record from the grid, delete the entire bug and all of its history. Because of referential integrity, first remove all records from that bug within BugHistory, and then remove the record from the Bugs table.

Example 20-11. Delete bugs stored procedure

CREATE PROCEDURE spDeleteBugFromDataSet
@bugID int,
@BugHistoryID int
as
Begin Transaction
 Delete from BugHistory where 
 bugID = @BugID and BugHistoryID = @BugHistoryID 
if @@Error <> 0 goto ErrorHandler
 Delete from Bugs where bugID = @BugID
if @@Error <> 0 goto ErrorHandler
 commit transaction
 return
ErrorHandler:
 rollback transaction
 return

You will pass in two parameters that will identify the record to delete. You will delete from BugHistory and Bugs as part of a transaction. That way, if the delete from either table fails, the entire delete will be rolled back, protecting your database from potential corruption.

With this stored procedure, you are ready to create the Command object you will assign to the DataAdapters DeleteCommand property.

Begin by creating a new SqlCommand object:

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

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, like all the others you've used so far to invoke stored procedures.

Add two parameters, BugID and BugHistoryID. These are input parameters, but rather than assigning a value to them, this time 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 from which this parameter will get its value. 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 was changed by another process. This topic is covered later in Section 20.4.

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 have not changed the value, you can use the Current value as well):

param.SourceVersion=DataRowVersion.Original;

Create a Parameter object for the BugHistory in exactly the same way:

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

You are now ready to assign the Command object to the data adapter's DeleteCommand property:

dataAdapter.DeleteCommand=deleteCmd;

20.3.3.2 The Update command

The stored procedure for updating the database is more complicated than the procedure for deleting records. This time, pass in parameters for each field that may be changed. Also pass in the BugID and BugHistory ID to uniquely identify the bug you wish to alter. The complete code for the spUpdateBugFromDataSet stored procedure is shown in Example 20-12.

The word Description is a keyword for SQL, so you must bracket the Description field as shown in Example 20-12.

 

Example 20-12. 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
Begin Transaction
Update Bugs 
set 
 Product = @productID,
 [Description] = @Description,
 Reporter = @Reporter
 where bugID = @BugID
if @@Error <> 0 goto ErrorHandler
 
Update BugHistory 
Set
 status = @Status, 
 severity = @Severity, 
 response = @Response, 
 owner = @Owner
where BugHistoryID = @bugHistoryID and bugID = @bugID
if @@Error <> 0 goto ErrorHandler
commit transaction
return
ErrorHandler:
rollback transaction
return

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;

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

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 now you use the enumerated value DataRowVersion.Current for the SourceVersion property. Use Current for any value that may have been changed in the DataSet; this instructs the DataAdapter 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, be careful to use the ReporterID, OwnerID, StatusID, and SeverityID SourceColumns, respectively. Remember that while you display the full names of the reporter and owner and the text value of the status and severity, the records you update in the Bugs and BugHistory tables use the ID.

20.3.3.3 The Insert command

The final command you'll need to implement is the Insert command. Start, once again, by creating the necessary stored procedure, spInsertBugFromDataSet, as shown in Example 20-13.

Example 20-13. The stored procedure for inserting a bug

CREATE PROCEDURE spInsertBugFromDataSet
@ProductID int,
@Version varChar(50),
@Description varChar(8000),
@Response varChar(8000),
@Reporter int,
@Owner int,
@Status int,
@Severity int
as
Begin Transaction
 declare @bugID int
 Insert into Bugs values (@ProductID, @Version, @Description, @Reporter)
 if @@Error <> 0 goto ErrorHandler
 select @bugID = @@identity
 Insert into BugHistory 
 (bugHistoryID, bugID, status, severity, response, owner)
 values 
 ( 
 1, -- bug history id
 @bugID,
 @status, 
 @Severity,
 @response,
 @owner
 )
 if @@Error <> 0 goto ErrorHandler
 commit transaction
 return 
ErrorHandler:
 rollBack transaction
 return

Remember to insert into the Bugs table before inserting into the BugHistory table, since referential integrity constraints require that the BugID exist in Bugs before it can be inserted into BugHistory.

Do not pass in either the BugID or the BugHistoryID. The bugID is created by the database, and for new records, the BugHistoryID is always 1. The BugHistory table requires that the BugID be generated by adding a record to Bugs; obtain this value from @@identity.

This stored procedure will be called to insert the record you created by hand in the btnUpdateDS_Click event procedure. You must create a Command object, this time for the DataAdapter object's InsertCommand property:

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

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

dataAdapter.InsertCommand=insertCmd;

20.3.3.4 Adding transaction support

It is possible for one of the updates to fail, and if they do not all fail, returning the database to a valid state can be difficult. Therefore, wrap connection transaction support around all the updates. 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( );

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;

There is no need to provide database transaction support if you are providing a connection transaction, but there is no harm either. You may find that you want the connection transaction to ensure that all the updates succeed or fail together, but that the sprocs want their own transactions so they can be reused in other circumstances.

 

20.3.3.5 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 DataGrid. The code is as follows:

try
{
 int rowsUpdated = dataAdapter.Update(bugDS,"BugInfo");
 transaction.Commit( );
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.");
 RefreshDataSet( );
}
catch (Exception ex)
{
 MessageBox.Show("Unable to update db!" + ex.Message);
 transaction.Rollback( );
}

Try
 Dim rowsUpdated As Int16 = myDataAdapter.Update(bugDS, "BugInfo")
 transaction.Commit( )
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.")
 RefreshDataSet( )
Catch ex As Exception
 MessageBox.Show("Unable to update db!" + ex.Message)
 transaction.Rollback( )
End Try

If no exception is thrown, commit the transactions; otherwise, roll them back. If all goes well, you will see a message box indicating that the records were updated and the updates are reflected in the DataGrid, as shown in Figure 20-8.

Figure 20-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 20-9.

Figure 20-9. Bug and history table after the database update