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
DataSet update of database

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#)

image with no caption

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)

image with no caption

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

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.

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.

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:

image with no caption

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;
}

image with no caption

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:

image with no caption

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

image with no caption

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:

image with no caption

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

image with no caption

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:

image with no caption

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

image with no caption

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

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.

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:

image with no caption

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

image with no caption

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:

image with no caption

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

In VB.NET, this code is nearly identical:

image with no caption

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

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

image with no caption

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:

image with no caption

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

image with no caption

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.

Tip

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.

After updating the DataSet

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:

image with no caption

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.

Bug and history table after the DataSet update, but before the database update

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

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.

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:

image with no caption

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

image with no caption

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:

image with no caption

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):

image with no caption

param.SourceVersion=DataRowVersion.Original;

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

image with no caption

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:

image with no caption

dataAdapter.DeleteCommand=deleteCmd;

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.

Tip

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:

image with no caption

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

image with no caption

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

Add a SqlParameter object for each parameter to the stored procedure:

image with no caption

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.

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:

image with no caption

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:

image with no caption

dataAdapter.InsertCommand=insertCmd;

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:

image with no caption

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

image with no caption

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:

image with no caption

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

Tip

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.

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:

image with no caption

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(  );
}

image with no caption

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.

After updating the database

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.

Bug and history table after the database update

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

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset