DekGenius.com
Team LiB
Previous Section Next Section

Data Update Methods

Updating data via the DataSet can be tricky. In a previous version of ADO, the update was done row by row manually by looping through each row and running the relevant query. You can still do this with DataSet. You can reference each row of a DataTable in a DataSet, loop through each one, and run the appropriate query. However, the update method of the DataAdapter can take care of all the required looping for you.

There is still one problem with the update method. It does all updates, including deletes, inserts, and, obviously, updates, in the order that it retrieves the rows from the DataTable. It checks the RowState property and does the required INSERT, DELETE, or UPDATE command. If you require updates to be done in a particular order, you must filter the DataSet. In the case study, we need to control the order of the updates for the Order Details and Orders tables. You need to create separate update methods to take care of the deletes, updates, and inserts for both tables.

The Update Functions

Update Methods

Description

UpdateOrderDetails

Parameter:
ByRef DataTable
Updates the Order Details table

InsertOrderDetails

Parameter:
ByRef DataTable
Inserts new rows in the Order Details table

DeleteOrderDetails

Parameter:
ByRef DataTable
Deletes rows in the Order Details table

UpdateOrders

Parameter:
ByRef DataTable
Updates the Orders table

InsertOrders

Parameter:
ByRef DataTable
Inserts new rows in the Orders table

DeleteOrders

Parameter:
ByRef DataTable
Deletes rows in the Orders table

The update functions all follow a similar pattern—they initialize variables, define the query string, define parameters, and finally execute the query string, including trapping any errors that may arise.

The Protected Order Details Update Methods

Let’s look at the simpler update methods: the update methods for the Order Details table. They are simpler because the Order Details table is a child table and does not have dependent tables.

The DeleteOrderDetails Method

The DeleteOrderDetails method is the simplest of the three because it requires fewer parameters. In fact, you only need to supply parameters for the primary key columns, OrderID and ProductID. As is standard, the first step is to declare the method and the required variables.

Protected Function DeleteOrderDetails _
  (ByRef OrderDetailsTB As DataTable)

  'Declare SqlConnection object for connection to database
  'Use the Global Private string initialized in
  'MyBase.New()
  Dim objConn As New SqlConnection(SQLConnectionString)

  'Declare the DataAdapter that will be 
  'used to populate the DataSet.
  Dim OrderDataAdapter As New SqlDataAdapter()

  OrderDataAdapter.DeleteCommand = New SqlCommand()

  'Declare the DataAdapter that will be 
  'used to populate the DataSet.
  Dim workParm As SqlParameter

  'Declare the query string 
  'that will perform the update
  Dim DeleteOrderDetailsSTR As String = _
  "DELETE FROM [Order Details] " & _
  " WHERE " & _
  "OrderID = @OrderID AND " & _
  "ProductID = @ProductID "

Notice that we pass along a DataTable instead of a DataSet as a parameter. This is possible because the method will not be exposed as a web method. Furthermore, these update methods will only be used by other methods in the class or by descendants of the class, so we declare it as protected. The initialization also includes the query string required for the delete. Make a note of the parameters used because in the next section, we need to define these parameters.

  'Create new parameter and reference it
  'Set the source and version
  workParm = OrderDataAdapter.DeleteCommand. _
    Parameters.Add("@OrderID", SqlDbType.Int)
  workParm.SourceColumn = "OrderID"
  workParm.SourceVersion = DataRowVersion.Original

  workParm = OrderDataAdapter.DeleteCommand. _
    Parameters.Add("@ProductID", SqlDbType.Int)
  workParm.SourceColumn = "ProductID"
  workParm.SourceVersion = DataRowVersion.Original

We use the reference variable workParm to refer to each parameter that we create to reduce the amount of code we have to type. When a parameter is created, we also define the data type that is used in the database. In the database, both OrderID and ProductID are integers represented in the code by the enumerated data type SqlDbType.Int. We define the source of the parameter as a column in the DataTable. Next, we define which version of the row to use. Since we are going to delete the row, we need to make sure that we use the original version of the key columns so that we refer to the right row. If we use the default version, which is DataRowVersion.Current, we might end up deleting the wrong records. This can happen if the columns were changed and deleted in the DataSet.

After we have done all the required settings, we can then go ahead and do the update. 

  'Set command test and connection
  OrderDataAdapter.DeleteCommand. _
    CommandText = DeleteOrderDetailsSTR
  OrderDataAdapter.DeleteCommand.Connection = objConn

  Try
    'Open the Connection to database
    objConn.Open()
    
    'Next process updates.
    DeleteOrderDetails = OrderDataAdapter. _
    Update(OrderDetailsTB.Select _
     (Nothing, Nothing, DataViewRowState.Deleted))

  Catch ConcurncyErr As DBConcurrencyException
    Throw ConcurncyErr

  Catch Err As Exception
    Throw New ApplicationException( _
      "Exception encountered when executing command.",
        Err)

  Finally

    'Close the Connection to database
    objConn.Close()

  End Try

End Function

This part of the code is more or less standard. All we do is add the query string to the DeleteCommand.commandtext property, set the connection object to use, and do the update. We filter rows in the DataTable only to pass the deleted rows using the select method of the DataTable and the DataViewRowState.Deleted row state filter. This will ensure that only deleted rows are passed to the update method of the DataAdpater, ensuring that the update method does only deletes.

Note 

We catch the concurrency error separately from other errors. Concurrency errors occur if the query did not affect any row. Because of the disconnected nature of the DataSet and Web Services, it is possible that the rows in the database, in this case, have been deleted by another user after the rows were last retrieved by the current user. I will discuss concurrency in more detail later in this chapter.

The InsertOrderDetails Method

The InsertOrderDetails method follows the same pattern as DeleteOrderDetails, but this time the query is slightly more complicated.

Dim InsertOrderDetailsSTR As String = _
  "INSERT INTO [Order Details]" & _
  "(OrderID,ProductID,UnitPrice,Quantity,Discount) " & _
  "SELECT @OrderID,@ProductID," & _
  "@UnitPrice,@Quantity,@Discount " & _
  "FROM Orders ,Products " & _
  "where Orders.OrderID = @OrderID " & _
  "AND Products.ProductID = @ProductID " & _
  "AND @ProductID not in " & _
  "( SELECT ProductID " & _
  "from [Order Details] " & _
  "where [Order Details].OrderID = @OrderID)"

The SQL INSERT command is used with a SELECT to restrict the insert. The SELECT returns the list of insert parameters only if the following conditions are true:

  • An order with the given OrderID exists.

  • A product with the given ProductID exists.

  • The given ProductID is not already part of the Order Details for the given OrderID.

These conditions are mainly for concurrency reasons, to avoid inserts of products that have already been added to the order since the order was last retrieved. It also prevents addition if the order or the product no longer exists. As for the parameter version, we now use current versions for all parameters because we are dealing with new rows. 

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@OrderID", SqlDbType.Int)
  workParm.SourceColumn = "OrderID"
  workParm.SourceVersion = DataRowVersion.Current

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ProductID", SqlDbType.Int)
  workParm.SourceColumn = "ProductID"
  workParm.SourceVersion = DataRowVersion.Current

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@UnitPrice", SqlDbType.Money)
  workParm.SourceColumn = "UnitPrice"
  workParm.SourceVersion = DataRowVersion.Current

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@Quantity", SqlDbType.SmallInt)
  workParm.SourceColumn = "Quantity"
  workParm.SourceVersion = DataRowVersion.Current

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@Discount", SqlDbType.Real)
  workParm.SourceColumn = "Discount"
  workParm.SourceVersion = DataRowVersion.Current

You do not need to specify the SourceVersion as DataRowVersion.Current since this is the default value, but doing so helps in the clarity of the code and removes ambiguity.

The update is similar to that of DeleteOrder, except this time we filter for the inserted rows.

  OrderDataAdapter.InsertCommand.CommandText =
    InsertOrderDetailsSTR
  OrderDataAdapter.InsertCommand.Connection = objConn

  Try
    'Open the Connection to database
    objConn.Open()

    'Next process updates.
    InsertOrderDetails = OrderDataAdapter.Update( _
      OrderDetailsTB.Select( _
        Nothing, Nothing, DataViewRowState.Added))

  Catch ConcurncyErr As DBConcurrencyException
    Throw ConcurncyErr

  Catch Err As Exception
    Throw New ApplicationException( _
      "Exception encountered when executing command.",
        Err)

  Finally

    'Close the Connection to database
    objConn.Close()

  End Try

To get the new rows from the DataTable, we use DataViewRowState.Added as the filter criteria.

The UpdateOrderDetails Method

The UpdateOrderDetails method is slightly more tricky than the DeleteOrderDetails and the InsertOrderDetails methods. First, UpdateOrderDetails is declared and variables are initialized. 

Public Function UpdateOrderDetails _
  (ByVal OrderDetailsTB As DataTable)
  As Integer

  'Declare SqlConnection object for connection to database
  'Use the Global Private string initialized in 
  'MyBase.New()
  Dim objConn As New SqlConnection(SQLConnectionString)

  'Declare the DataAdapter that will be 
  'used to populate the DataSet.
  Dim OrderDataAdapter As New SqlDataAdapter()

  OrderDataAdapter.UpdateCommand = New SqlCommand()

  Dim workParm As SqlParameter

  Dim UpdateOrderDetailsSTR As String = _
  "UPDATE [Order Details] " & _
  "SET UnitPrice = @NewUnitPrice, " & _
  " Quantity = @NewQuantity, " & _
  " Discount = @NewDiscount " & _
  " WHERE OrderID = @OrigOrderID AND" & _
  " ProductID = @OrigProductID"

The query string is simple, but do make a note that only the non-primary key columns are updated. The primary key columns are used to qualify the update. The tricky part is the next code section dealing with the parameters.

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@OrigOrderID", SqlDbType.Int)
  workParm.SourceColumn = "OrderID"
  workParm.SourceVersion = DataRowVersion.Original

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@OrigProductID", SqlDbType.Int)
  workParm.SourceColumn = "ProductID"
  workParm.SourceVersion = DataRowVersion.Original

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@NewUnitPrice", SqlDbType.Money)
  workParm.SourceColumn = "UnitPrice"
  workParm.SourceVersion = DataRowVersion.Current

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@NewQuantity", SqlDbType.SmallInt)
  workParm.SourceColumn = "Quantity"
  workParm.SourceVersion = DataRowVersion.Current

  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@NewDiscount", SqlDbType.Real)
  workParm.SourceColumn = "Discount"
  workParm.SourceVersion = DataRowVersion.Current

For the parameter referring to the primary keys, you must use the original version of the row. This ensures that you do not update the wrong row and provides rudimentary concurrency error checks. In effect, we have also made the primary keys read-only. As for the rest of the parameters, use the current version so that the update is applied. Afterward, all that remains is to do the update, making sure that only changed rows are passed to the update method

  OrderDataAdapter.UpdateCommand.CommandText =
    UpdateOrderDetailsSTR
  OrderDataAdapter.UpdateCommand.Connection = objConn

  Try
    'Open the Connection to database
    objConn.Open()

    'Next process updates.
    UpdateOrderDetails = OrderDataAdapter.Update _
      (OrderDetailsTB.Select( _
       Nothing, Nothing, DataViewRowState.
         ModifiedCurrent))

  Catch ConcurncyErr As DBConcurrencyException
    Throw ConcurncyErr

  Catch Err As Exception
    Throw New ApplicationException( _
      "Exception encountered when executing command.",
        Err)

  Finally

    'Close the Connection to database
    objConn.Close()

  End Try

Concurrency Issues

In the update functions, there is no advance concurrency error check that checks if the rows have changed since they were last retrieved. This is a common problem in disconnected multiuser systems. To do this check, you may need to check every column and row in the table with the corresponding columns and rows in the DataTable. This, though, is not the most efficient design. The more common solution is to add a special column that you can use for tracking row versions in each table. This column, usually a datetime field or a timestamp, is changed each time any column in the particular row is modified. This can be done by the database engine through the use of triggers.

Note 

In Microsoft SQL Server 2000, the Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type. Future releases of the SQL Server may modify the behavior of the timestamp data type to comply with the behavior defined in the SQL-92 standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

The special concurrency check field is always retrieved with any updateable records. When doing an update or delete, the original value of the concurrency field is used against that of the database, usually in the WHERE clause. If the original does not match the database current value, the update method will cause a concurrency exception. The DataAdapter update method throws a concurrency error if the update causes zero rows to be affected.

The Protected Orders Update Methods

Thus far, we have completed the update methods that we will need to update the Order Details table. To be able to modify an order, we must also add update methods to modify the order header or, more accurately, the Orders table.

The methods follow what now is a familiar pattern for the update methods we have done so far. However, to show you the power of the DataAdapter update method, we will do the updates through stored procedures.

Note 

For illustration purposes, I have chosen not to update Order Details through stored procedures. However, in a real-world project, the use of stored procedures is advised over direct SQL query. This is mainly for performance and security purposes. It might also be a requirement imposed on you by the database administrator.

Before we move on with the methods, we need to define the stored procedure that the method will use. You can apply the required SQL query to generate the stored procedure using the query analyzer tool that is included with Microsoft SQL Server 2000. Make sure that you apply the stored procedure to the Northwind database.

The sp_UpdateOrders Stored Procedure

The first part of the script is to check if the stored procedure already exists. If it does, we drop the stored procedure. This part of the script is useful, especially when debugging and writing the scripts. It keeps us from having to manually drop the stored procedure each time we apply a new version. 

-- =======================================
-- Script for sp_UpdateOrders
-- =======================================

/*********************************************/
/* Check if a version of the stored          */
/* procedure already exist and if so drop it */
/*********************************************/

IF EXISTS (SELECT name 
           FROM   sysobjects 
           WHERE  name = N'sp_UpdateOrders' 
           AND    type = 'P')
    DROP PROCEDURE sp_UpdateOrders
GO

Next, we create the procedure with all the required parameters.

CREATE PROCEDURE sp_UpdateOrders 
  @OrderID integer,
  @CustomerID nchar(5),
  @EmployeeID integer,
  @OrderDate datetime,
  @RequiredDate datetime,
  @ShippedDate datetime,
  @ShipVia integer,
  @Freight money,
  @ShipName nvarchar(40),
  @ShipAddress nvarchar(60),
  @ShipCity nvarchar(15),
  @ShipRegion nvarchar(15),
  @ShipPostalCode nvarchar(10),
  @ShipCountry nvarchar(15)
AS
BEGIN

Finally, we define the SQL scripts that we need to run to do the required update. 

/*********************************************/
/* set up SQL to use transaction to allow    */
/* rollback in case of error                 */
/*********************************************/

BEGIN TRANSACTION

/* Run script to update Orders table */
UPDATE Orders SET
       CustomerID = @CustomerID,
       EmployeeID = @EmployeeID,
       OrderDate = @OrderDate,
       RequiredDate = @RequiredDate,
       ShippedDate = @ShippedDate,
       ShipVia = @ShipVia,
       Freight = @Freight,
       ShipName = @ShipName,
       ShipAddress = @ShipAddress,
       ShipCity = @ShipCity,
       ShipRegion = @ShipRegion,
       ShipPostalCode = @ShipPostalCode,
       ShipCountry = @ShipCountry 
WHERE Orders.OrderID = @OrderID

/* check for error*/
IF (@@ERROR <> 0)
    GOTO Error


GOTO Ok

Error:
/* Error Condition, so rollback*/
ROLLBACK TRANSACTION
RETURN -1
GOTO Finally 

Ok:
/*Everything is ok, so commit*/
COMMIT TRANSACTION
RETURN 0
Finally:

END

GO

The SQL UPDATE script uses only basic concurrency checks. The stored procedure only checks for existence of the order but does not check if the order has been changed since it was last retrieved. It will override any changes made. This method is ideal only in a segregated multiuser system. In such a system, where updating is concerned, only one user can update certain rows. For instance, our system would allow only one connection per company to the Web Service, and the company will only be allowed to update its own order.

The sp_InsertOrders Stored Procedure

As is standard, we first do the required declaration:

-- =======================================
-- Script for sp_InsertOrders 
-- =======================================

/**********************************************/
/* Check if a version of the stored           */
/* procedure already exists and if so drop it */
/**********************************************/
IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'sp_InsertOrders' 
       AND    type = 'P')
    DROP PROCEDURE sp_InsertOrders
GO

CREATE PROCEDURE sp_InsertOrders 
  @OrderID integer output,
  @CustomerID nchar(5),
  @EmployeeID integer,
  @OrderDate datetime,
  @RequiredDate datetime,
  @ShippedDate datetime,
  @ShipVia integer,
  @Freight money,
  @ShipName nvarchar(40),
  @ShipAddress nvarchar(60),
  @ShipCity nvarchar(15),
  @ShipRegion nvarchar(15),
  @ShipPostalCode nvarchar(10),
  @ShipCountry nvarchar(15)
AS
BEGIN

Notice that the stored procedure has an output parameter. Use this technique when some column values are auto-generated and required in subsequent updates. In our case, we will need the generated value of the OrderID column for inserting related rows in Order Details.

The next step is to define a special variable “@@Now” that we will use to generate values to help retrieve the auto-generated value of OrderID. @@Now is set to the value of the current date and time. Since datetime is accurate to more than a millisecond, the chance of duplicating the value for @@Now is remote. We then do the insert, but instead of adding all values for all columns for certain datetime columns (OrderDate, RequiredDate, and ShippedDate), we set them to the value generated in @@Now. 

/*********************************************/
/* declare a special variable to be used as  */
/* a special marker in the inserted row so   */
/* that we can retrieve the auto-generated   */
/* value for the OrderID column              */
/*********************************************/
DECLARE @@Now datetime
SET @@Now = GETDATE()


BEGIN TRANSACTION
/*********************************************/
/* Run script to insert in the Orders table  */
/* Use the datetime columns and @@Now to add */
/* markers to record so that we can retrieve */
/* the auto generated OrderId                */
/*********************************************/

Insert INTO Orders(
       CustomerID,
       EmployeeID,
       OrderDate,
       RequiredDate,
       ShippedDate,
       ShipVia,
       Freight,
       ShipName,
       ShipAddress,
       ShipCity,
       ShipRegion,
       ShipPostalCode,
       ShipCountry )
VALUES (
       @CustomerID,
       @EmployeeID,
       @@Now,
       @@Now,
       @@Now,
       @ShipVia,
       @Freight,
       @ShipName,
       @ShipAddress,
       @ShipCity,
       @ShipRegion,
       @ShipPostalCode,
       @ShipCountry )

/* check for error*/
IF (@@ERROR <> 0)
    GOTO Error

Once we have checked for errors with the insert and everything is fine, the next step is to retrieve the generated OrderID value. For this, OrderDate, RequiredDate, and ShippedDate are retrieval arguments since we know that only the newly inserted record will have the value of the @@Now variable for each of the above mentioned columns.

/* Retrieve auto-generated OrderID*/
SELECT @OrderID = OrderID  
FROM Orders
WHERE OrderDate     = @@Now AND
      RequiredDate  = @@Now AND
      ShippedDate   = @@Now

/* check for error*/
IF (@@ERROR <> 0)
    GOTO Error

Finally, all that remains is to set OrderDate, RequiredDate, and ShippedDate to the correct value.

/*Update columns used in marker with proper value*/
UPDATE Orders SET
   OrderDate     = @OrderDate,
   RequiredDate  = @RequiredDate,
   ShippedDate   = @ShippedDate
WHERE OrderID = @OrderID

/* check for error*/
IF (@@ERROR <> 0)
    GOTO Error

GOTO Ok

Error:
/* Error Condition, so rollback*/
ROLLBACK TRANSACTION
RETURN -1
GOTO Finally 

Ok:
/*Everything is ok, so commit*/
COMMIT TRANSACTION
RETURN 0
Finally:

END

GO

The sp_DeleteOrders Stored Procedure

The stored procedure for delete is much simpler than the other two:

-- =======================================
-- Script for sp_DeleteOrders 
-- =======================================

/*********************************************/
/* Check if a version of the stored          */
/* procedure already exist and if so drop it */
/*********************************************/
IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'sp_DeleteOrders' 
       AND    type = 'P')
    DROP PROCEDURE sp_DeleteOrders
GO

CREATE PROCEDURE sp_DeleteOrders 
    @OrderID integer 
AS
BEGIN
/*********************************************/
/* First delete order entries for the order  */
/* to avoid constraint violation error.      */
/*********************************************/

BEGIN TRANSACTION

DELETE FROM [Order Details]
WHERE [Order Details].OrderID = @OrderID

/* check for error*/
IF (@@ERROR <> 0)
    GOTO Error
/* We can now delete the order from the Orders table */
DELETE FROM Orders
WHERE Orders.OrderID = @OrderID

/* check for error*/
IF (@@ERROR <> 0)
    GOTO Error

GOTO Ok

Error:
/* Error Condition, so rollback*/
ROLLBACK TRANSACTION
RETURN -1
GOTO Finally

Ok:
/*Everything is ok, so commit*/
COMMIT TRANSACTION
RETURN 0

Finally:

END

GO

Note that the script is designed in such a way to optimize the delete process. The stored procedure will delete the whole order, including the related rows in the Order Details table, to prevent foreign key constraint violation in the database.

The UpdateOrders Method

First, we need to declare the UpdateOrders method:

Protected Function UpdateOrders _
  (ByRef OrdersTB As DataTable) _
  As Integer

  'Declare SqlConnection object for connection to database
  'Use the Global Private string initialized in 
  'MyBase.New()
  Dim objConn As New SqlConnection(SQLConnectionString)

  'Declare the DataAdapter that will be
  'used to populate the DataSet.
  Dim OrderDataAdapter As New SqlDataAdapter()

  OrderDataAdapter.UpdateCommand = New SqlCommand()

Then we have to define the command text for UpdateCommand. We are going to use the stored procedures that we created. As you can see below, all you need to specify in the command text is the stored procedure name. We must also tell UpdateCommand that the commandtype is a stored procedure.

  OrderDataAdapter.UpdateCommand.CommandText =
    "sp_UpdateOrders"
  OrderDataAdapter.UpdateCommand.Connection = objConn
  OrderDataAdapter.UpdateCommand.CommandType = _
    CommandType.StoredProcedure

Next, we need to define all the parameters that the stored procedure uses. The return value from the stored procedure is also captured in a parameter. This is a simple matter of setting the parameter direction to ParameterDirection.ReturnValue.

  'Declare variable to be used as a 
  'reference to Parameters
  Dim workParm As SqlParameter

  'return value
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@return", SqlDbType.Int)
  workParm.Direction = ParameterDirection.ReturnValue

Remember that the OrderID is read-only, so we choose the original value and ignore the current value in case it has been changed. 

  'parameter 1
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@OrderID", SqlDbType.Int)
  workParm.SourceColumn = "OrderID"
  workParm.SourceVersion = DataRowVersion.Original

For the other parameters, since they are updateable, we choose the current value to apply any change to the database.

 'parameter 2
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@CustomerID", SqlDbType.NChar, 5)
  workParm.SourceColumn = "CustomerID"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 3
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@EmployeeID", SqlDbType.Int)
  workParm.SourceColumn = "EmployeeID"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 4
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@OrderDate", SqlDbType.DateTime)
  workParm.SourceColumn = "OrderDate"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 5
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@RequiredDate", SqlDbType.DateTime)
  workParm.SourceColumn = "RequiredDate"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 6
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShippedDate", SqlDbType.DateTime)
  workParm.SourceColumn = "ShippedDate"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 7
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShipVia", SqlDbType.Int)
  workParm.SourceColumn = "ShipVia"
  workParm.SourceVersion = DataRowVersion.Current
 'parameter 8
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@Freight", SqlDbType.Money)
  workParm.SourceColumn = "Freight"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 9
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShipName", SqlDbType.NVarChar, 40)
  workParm.SourceColumn = "ShipName"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 10
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShipAddress", SqlDbType.NVarChar, 60)
  workParm.SourceColumn = "ShipAddress"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 11
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShipCity", SqlDbType.NVarChar, 15)
  workParm.SourceColumn = "ShipCity"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 12
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShipRegion", SqlDbType.NVarChar, 15)
  workParm.SourceColumn = "ShipRegion"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 13
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShipPostalCode", 
    SqlDbType.NVarChar, 10)
  workParm.SourceColumn = "ShipPostalCode"
  workParm.SourceVersion = DataRowVersion.Current

 'parameter 14
  workParm = OrderDataAdapter.UpdateCommand. _
    Parameters.Add("@ShipCountry", SqlDbType.NVarChar, 15)
  workParm.SourceColumn = "ShipCountry"
  workParm.SourceVersion = DataRowVersion.Current

We are now ready to do the update, passing only changed rows to the update method.

  Try
    'Open the Connection to database
     objConn.Open()
    'Next process updates.
     UpdateOrders = OrderDataAdapter.Update _
     (OrdersTB.Select( _
  Nothing, Nothing, DataViewRowState.ModifiedCurrent))

  Catch ConcurncyErr As DBConcurrencyException
    Throw ConcurncyErr

  Catch Err As Exception
    Throw New ApplicationException( _
      "Exception encountered when executing command.",
        Err)

  Finally

    'Close the Connection to database
    objConn.Close()

  End Try

End Function

The DeleteOrders Method

The DeleteOrders method follows a pattern similar to UpdateOrders.

Protected Function DeleteOrders _
  (ByRef OrdersTB As DataTable) _
  As Integer

  'Declare SqlConnection object for connection to database
  'Use the Global Private string initialized in 
  'MyBase.New()
  Dim objConn As New SqlConnection(SQLConnectionString)

  'Declare the DataAdapter that will be
  'used to populate the DataSet.
  Dim OrderDataAdapter As New SqlDataAdapter()

  OrderDataAdapter.DeleteCommand = New SqlCommand()

  Dim workParm As SqlParameter

  OrderDataAdapter.DeleteCommand.CommandText =
    "sp_DeleteOrders"
  OrderDataAdapter.DeleteCommand.Connection = objConn
  OrderDataAdapter.DeleteCommand.CommandType = _
    CommandType.StoredProcedure

  'return value
  workParm = OrderDataAdapter.DeleteCommand. _
    Parameters.Add("@return", SqlDbType.Int)
  workParm.Direction = ParameterDirection.ReturnValue

  'parameter 1
  workParm = OrderDataAdapter.DeleteCommand. _
    Parameters.Add("@OrderID", SqlDbType.Int)
  workParm.SourceColumn = "OrderID"
  workParm.SourceVersion = DataRowVersion.Original

  Try
    'Open the Connection to database
    objConn.Open()
    ' Next process updates.
    DeleteOrders = OrderDataAdapter.Update( _
      OrdersTB.Select( _
        Nothing, Nothing, DataViewRowState.Deleted))

  Catch ConcurncyErr As DBConcurrencyException
    Throw ConcurncyErr

  Catch Err As Exception
    Throw New ApplicationException( _
      "Exception encountered when executing command.",
        Err)

  Finally

    'Close the Connection to database
    objConn.Close()

  End Try
End Function

The big difference from UpdateOrders is the smaller number of parameters required.

The InsertOrders Method

The InsertOrders Method also follows the same pattern. However, the parameter direction for the OrderID is now set to ParameterDirection.Output because we will need this value in the DataSet to update related records in the Order Details table.

Protected Function InsertOrders _
  (ByRef OrdersTB As DataTable) _
  As Integer

  'Declare SqlConnection object for connection to database
  'Use the Global Private string initialized in 
  'MyBase.New()
  Dim objConn As New SqlConnection(SQLConnectionString)


  'Declare the DataAdapter that will be
  'used to populate the DataSet.
  Dim OrderDataAdapter As New SqlDataAdapter()

  OrderDataAdapter.InsertCommand = New SqlCommand()

  OrderDataAdapter.InsertCommand.CommandText =
    "sp_InsertOrders"
  OrderDataAdapter.InsertCommand.Connection = objConn
  OrderDataAdapter.InsertCommand.CommandType = _
    CommandType.StoredProcedure

We also need to instruct UpdateCommand to update data in the DataSet using the return output parameter. This is useful because we will only know the true value of OrderID after we have done the insert. Furthermore, using foreignkey constraint, we can cascade this update to include related child rows in the Order Details DataTable.

  'Instruct InsertComand to use outparameter to update
  'the dataset.
  OrderDataAdapter.InsertCommand.UpdatedRowSource = _
    UpdateRowSource.OutputParameters

All that remains now is to continue with the same pattern as with the other order update methods.

Dim workParm As SqlParameter
'return value
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@return", SqlDbType.Int)
workParm.Direction = ParameterDirection.ReturnValue

'parameter 1
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@OrderID", SqlDbType.Int)
workParm.SourceColumn = "OrderID"
workParm.Direction = ParameterDirection.Output

'parameter 2
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@CustomerID", SqlDbType.NChar, 5)
workParm.SourceColumn = "CustomerID"
workParm.SourceVersion = DataRowVersion.Current

'parameter 3
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@EmployeeID", SqlDbType.Int)
workParm.SourceColumn = "EmployeeID"
workParm.SourceVersion = DataRowVersion.Current

'parameter 4
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@OrderDate", SqlDbType.DateTime)
workParm.SourceColumn = "OrderDate"
workParm.SourceVersion = DataRowVersion.Current

'parameter 5
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@RequiredDate", SqlDbType.DateTime)
workParm.SourceColumn = "RequiredDate"
workParm.SourceVersion = DataRowVersion.Current

'parameter 6
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShippedDate", SqlDbType.DateTime)
workParm.SourceColumn = "ShippedDate"
workParm.SourceVersion = DataRowVersion.Current

'parameter 7
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShipVia", SqlDbType.Int)
workParm.SourceColumn = "ShipVia"
workParm.SourceVersion = DataRowVersion.Current

'parameter 8
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@Freight", SqlDbType.Money)
workParm.SourceColumn = "Freight"
workParm.SourceVersion = DataRowVersion.Current

'parameter 9
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShipName", SqlDbType.NVarChar, 40)
workParm.SourceColumn = "ShipName"
workParm.SourceVersion = DataRowVersion.Current

'parameter 10
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShipAddress", SqlDbType.NVarChar, 60)
workParm.SourceColumn = "ShipAddress"
workParm.SourceVersion = DataRowVersion.Current

'parameter 11
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShipCity", SqlDbType.NVarChar, 15)
workParm.SourceColumn = "ShipCity"
workParm.SourceVersion = DataRowVersion.Current

'parameter 12
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShipRegion", SqlDbType.NVarChar, 15)
workParm.SourceColumn = "ShipRegion"
workParm.SourceVersion = DataRowVersion.Current

'parameter 13
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShipPostalCode", _
  SqlDbType.NVarChar, 10)
workParm.SourceColumn = "ShipPostalCode"
workParm.SourceVersion = DataRowVersion.Current
'parameter 14
workParm = OrderDataAdapter.InsertCommand. _
  Parameters.Add("@ShipCountry", SqlDbType.NVarChar, 15)
workParm.SourceColumn = "ShipCountry"
workParm.SourceVersion = DataRowVersion.Current

Try
  'Open the Connection to database
  objConn.Open()

  'Next process updates.
  InsertOrders = OrderDataAdapter.Update( _
    OrdersTB.Select( _
      Nothing, Nothing, DataViewRowState.Added))

Catch ConcurncyErr As DBConcurrencyException
  Throw ConcurncyErr

Catch Err As Exception
  Throw New ApplicationException( _
  "Exception encountered when executing command.", Err)

Finally

  'Close the Connection to database
  objConn.Close()

End Try

End Function

The FullUpdateOrder Method

Now that we have completed all the methods required for updating the Orders and Order Details tables, we can move on to expose the update functions to the Web Service clients. For the web method, we will only need to expose one method that does all the updates. The advantage of doing one method is to reduce the number of calls that have to be made to the Web Service. The disadvantage is that we are passing a bigger set of data at once, and it is more difficult to recover from update errors.

The signature for our method is shown here:

Class Method Name

Web Service Method Name

Description

FullUpdateOrder

FullUpdateOrder

Parameter:
ByRef OrderDS (Dataset)
Return:
String
Return error message if any

The first step is not the declaration of variables, as is usually the case, but rather we first check if the given DataSet contains the two required DataTables for the update. If it does, then we provide reference variables to them.

<WebMethod()> _
Public Function FullUpdateOrder _
  (ByRef Orders As DataSet) _
  As String

  'check that the Order table is available in the dataset
  If Not Orders.Tables.Contains("Orders") Then
    Return "Order table not in dataset"
  End If

  'check that the Order Details table is available in 
  'the dataset
  If Not Orders.Tables.Contains("Order Details") Then
    Return "Order Details table not in dataset"
  End If

  'reference the two tables for easier access
  Dim OrdersTB As DataTable = Orders.Tables("Orders")
  Dim OrderDetailsTB As DataTable = Orders.Tables("Order
    Details")

We now need to make sure that there is a foreign key constraint between the two DataTables with cascading update. 

  'Make sure a foreign key between parent table (Orders)
  'and child table (Order Details) exists
  'Create foreign key
  Dim fk_orders As ForeignKeyConstraint = _
    New ForeignKeyConstraint("fk_orders", _
      OrdersTB.Columns("OrderID"), _
        OrderDetailsTB.Columns("OrderID"))
  fk_orders.DeleteRule = Rule.Cascade
  fk_orders.UpdateRule = Rule.Cascade

  ' Add new foreign key constraint
  Try
    OrderDetailsTB.Constraints.Add(fk_orders)
  Catch err As Exception
    'If cannot add constraint, then
    'it must already exist or some other
    'restrictions exist
  'We can still try to update though
  End Try

The key is only vital if we are going to do inserts.

We are now ready to begin the updates. First, we take care of the deletes. To avoid concurrency errors in the DataSet, we delete the child records first. If we do not do so, the DeleteOrders method will still work. However, when we later run the DeleteOrderDetails method, it will fail because child records marked as deleted in the DataSet (due to cascade delete) will no longer exist in the database and a concurrency error would be thrown. In other words, the DataSet will be out of sync with the database.

  'The first step is to do the deletion
  'Remember that must delete child first
  'to avoid concurrency issue in dataset
  Try
    DeleteOrderDetails(OrderDetailsTB)

  Catch Err As Exception
    Return "Error deleting rows in OrderDetails, 
      Message:" Err.Message
  End Try
  Try
   DeleteOrders(OrdersTB)
     Catch Err As Exception
     Return "Error deleting rows Orders, Message:" _
       & Err.Message
  End Try

Next, we do the inserts and the updates:

  'Do Inserts
  'With insert, the parent has to be inserted first
  'to avoid concurrency error
  Try
    InsertOrders(OrdersTB)
  Catch Err As Exception
    Return "Error inserting rows Orders, Message:" &
      Err.Message
  End Try

  Try
    InsertOrderDetails(OrderDetailsTB)
    Catch Err As Exception
    Return "Error inserting rows in OrderDetails,
    Message:" & Err.Message
  End Try

  'Finally do the updates
  Try
    UpdateOrders(OrdersTB)
  Catch Err As Exception
    Return "Error updating rows Orders, Message:" &
      Err.Message
  End Try

  Try
    UpdateOrderDetails(OrderDetailsTB)
  Catch Err As Exception
    Return "Error updating rows in OrderDetails,
      Message:" & Err.Message
  End Try
  Return ""

End Function

The method will return error messages if any exceptions are thrown. Otherwise, an empty string is returned.

Testing the Update Methods

Since these methods take DataSet as a parameter, the Web Server cannot automatically generate test pages. To do testing, you have to either create test methods, which you then comment out when you move from production to live system, or you can create a test application that uses the Web Service.

In the source code on the companion CD, you will find some test methods that you can use. Also included are additional methods that return employees and products.

Team LiB
Previous Section Next Section