Click here to Skip to main content
Click here to Skip to main content

Updating a DataSet with Multiple Tables using Enterprise Library

, 21 Oct 2005
Rate this:
Please Sign up or sign in to vote.
How to update a DataSet with multiple tables, especially those with parent-child relationships.

Introduction

Updating a DataSet with multiple tables especially with identical columns and more specifically in a situation of parent child relationship poses a great challenge. And this is especially true if you are using Enterprise Library. A good example is a DataSet that has Orders and OrderDetails tables. The table Orders has a primary key which is auto generated. While updating multiple tables the Enterprise Library June edition first enables you to get the DataAdapter that can accept a table as parameter so that you have all the granularity.

Now let's consider a scenario. Suppose we have a purchase order management system and we want our suppliers to supply items in installments. This will result in having many consignments for a given order. Now after normalization we find that our consignment has two tables, one containing some general details and the other containing the details for the items consigned.

This screen shot below shows a part of the schema of our database that will hold our data:

Figure 1. Database schema

Now let's see the stored procedures we have for inserting Consignments (Parent) and ConsignedItems (Child):

CREATE PROCEDURE [dbo].[sp_ConsignmentsInsert] (
    @POID bigint,
    @ReceivingOfficer varchar(50),
    @DateConsigned datetime,
    @WarehouseCode bigint,
    @CheckedBy varchar(50),
    @DateChecked datetime,
    @DNNo bigint,
    @UserID int)
AS
SET NOCOUNT ON
INSERT INTO [Consignments] (
    [POID],
    [ReceivingOfficer],
    [DateConsigned],
    [WarehouseCode],
    [CheckedBy],
    [DateChecked],
    [DNNo],
    [UserID]) 
VALUES (
    @POID,
    @ReceivingOfficer,
    @DateConsigned,
    @WarehouseCode,
    @CheckedBy,
    @DateChecked,
    @DNNo,
    @UserID)
Select 
    [ConsignmentNo],
    [POID],
    [ReceivingOfficer],
    [DateConsigned],
    [WarehouseCode],
    [CheckedBy],
    [DateChecked],
    [DNNo],
    [UserID]
from [Consignments] where 

ConsignmentNo=SCOPE_IDENTITY()

Figure 2. Stored procedure for inserting Consignments data.

And for ConsignedItems we have the following procedure for inserting data:

CREATE PROCEDURE [dbo].[sp_ConsignedItemsInsert] (
    @ConsignmentNo bigint,
    @ItemCode bigint,
    @QuantityReceived bigint)
AS
SET NOCOUNT ON
INSERT INTO [ConsignedItems] (
    [ConsignmentNo],
    [ItemCode],
    [QuantityReceived]) 
VALUES (
    @ConsignmentNo,
    @ItemCode,
    @QuantityReceived)
Select 
    [ConsignmentNo],
    [ItemCode],
    [QuantityReceived]
from [ConsignedItems] where 
[ConsignmentNo] = @ConsignmentNo AND [ItemCode] = @ItemCode

Figure 3. Stored procedure for inserting ConsignedItems data.

First of all let's see some of the features of the code snippets included in this article.

  1. ConsignmentsDB and ConsignedItemsDB are table gateways that pass the table and a transaction. This is important if you want all the changes in a transaction.
  2. I avoided using Enterprise Services for managing transactions and hence had to include transaction objects in my business object though it is not a good practice. I think ADO.NET 2 has introduced something better. However I find it cumbersome to use Enterprise Services. (Sorry for this).

Consignments and ConsignedItems are tables in an offline DataSet. My business object inherits from a typed DataSet ConsignmentsDataSet so that I have direct access to the tables and access to call methods such as AccepChanges(), GetChanges(), Merge() etc. I have included the source files for you to go through and see what I mean. Sorry that I couldn't include a demo but I hope this information is sufficient. Remember to add/update the parent first and then the child and delete the child first and then the parent. Here Consignments is the parent table of ConsignedItems. Because we don't want to update the DataSet directly as this results in loss of all other un-submitted changes in the DataSet ,we decided to fetch those changes separately into another table by using the method <Table>.GetChanges(). The row state determines which rows are to be fetched from the table.

Now here we start: Fetch changes from the parent table (Inserts and Updates):

DataTable modifiedConsignments
  =Consignments.GetChanges(DataRowState.Modified|DataRowState.Added);

Figure 4. Fetching changes from the DataSet.

Let's implement SaveChanges() as shown in figure below that helps us to submit changes in the DataSet to the database for persistence:

public void SaveChanges()
{
    Database db=DatabaseFactory.CreateDatabase();
    IDbConnection conn=db.GetConnection();
    conn.Open();
    IDbTransaction trans=conn.BeginTransaction();
    try
    {
        DataTable modifiedConsignments
            =Consignments.GetChanges(DataRowState.Modified|DataRowState.Added);
        
        if(modifiedConsignments!=null)
        {
            ConsignmentsDB.Update(modifiedConsignments,trans);
            ReconcileHelper.Reconcile(Consignments,
                                modifiedConsignments,"RowID");
            modifiedConsignments.Dispose();
        }
        DataTable modifiedConsignedItems=
            ConsignedItems.GetChanges(DataRowState.Modified|DataRowState.Added);
        if(modifiedConsignedItems!=null)
        {
            ConsignedItemsDB.Update(modifiedConsignedItems,trans);
            modifiedConsignedItems.Dispose();
        }
        DataTable deletedConsignedItems=
                          ConsignedItems.GetChanges(DataRowState.Deleted);
        if(deletedConsignedItems!=null)
        {
            ConsignedItemsDB.Update(deletedConsignedItems,trans);
            deletedConsignedItems.Dispose();
        }
        DataTable deletedConsignments=
                          Consignments.GetChanges(DataRowState.Deleted);
        if(deletedConsignments!=null)
        {
            ConsignmentsDB.Update(deletedConsignments,trans);
            deletedConsignments.Dispose();
        }
        trans.Commit();
        AcceptChanges();
    }
    catch
    {
        trans.Rollback();
        throw ;
    }
}

Figure 5. The SaveChanges method in the ConsignmentsBO, a business object.

Note: We have used transactions because we want every INSERT, UPDATE or DELETE to succeed in whole or fail in whole, not partially. That is the case when there is no identity column in the Consignments table as a primary key. Then if that is not the case what do we do? Here we have got to manage the issue of identity column as a primary key.

It is a very simple idea but seems promising, although some one may find it shocking as it is not a built in solution in the .NET Framework. The issue in the above scenario occurs when we want to merge the original DataSet and the rows that have been updated. The simplest way is to introduce another column in the DataTable that has an identity column, say RowID. I often ask myself why Microsoft has not introduced some thing like this that would be of great use to developers and even in the SQL Server this would be of great benefit. This should also be identical, hence making every row have a unique row ID.

Now after fetching the rows that were added from the DataSet and passing them to the DataAdapter for propagating the changes to the database and re-fetching our newly generated identity primary key as our stored procedures are geared for (use of SCOPE_IDENTITY), we need an efficient way to reconcile it to the original table in the DataSet. Here is where the RowID plays a great role by using it to compare the updated row and the row in the Dataset.

Here is a helper to do that:

using System;
using System.Data;
namespace InventMan.Helpers
{
    /// <summary>
    /// Summary description for Recoinciler.
    /// </summary>
    public class ReconcileHelper
    {
        public ReconcileHelper()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public static void Reconcile(DataTable source,
                          DataTable updated,string commonKey)
        {
            DataColumn[] pk=source.PrimaryKey;
            string primaryKey=pk[0].ToString();
            source.Columns[primaryKey].ReadOnly=false;
            foreach (DataRow ur in updated.Rows) 
            {
                string filter=
                      commonKey + "=" + ur[commonKey].ToString();
                DataRow[] dr=source.Select(filter,
                              primaryKey,DataViewRowState.Added);
                if(dr!=null && dr.Length>0)
                { 
                    dr[0][primaryKey]=ur[primaryKey];
                }
            
            }
            source.Columns[primaryKey].ReadOnly=true;
        }
    }
}

Figure 6. Full implementation of the ReconcileHelper class that helps us in reconciling identity primary keys after re-fetch from the database (Stored procedures Figure 3 and 4).

Key:

  • source is the table in the DataSet that holds the rows that were added by the user.
  • updated is the table holding only the added rows that were passed to the data adapter for propagation into the database.
  • commonKey is the name of a key that is used instead of the primary key to identify rows because the identity primary key is now different for the two row sets (original DataSet row and the updated row).

OK, now we come to the actual updating method in the table gateway:

/// <summary>
/// Updates a record in the Consignments table.
/// </summary>
public static void Update(DataTable table,IDbTransaction trans)
{
    Database myDatabase = DatabaseFactory.CreateDatabase();
    DBCommandWrapper insertCommand = 
       myDatabase.GetStoredProcCommandWrapper("sp_ConsignmentsInsert");
    insertCommand.AddInParameter("@POID", 
       DbType.Int64,"POID", DataRowVersion.Current);
    insertCommand.AddInParameter("@ReceivingOfficer", 
       DbType.String,"ReceivingOfficer", DataRowVersion.Current);
    insertCommand.AddInParameter("@DateConsigned", 
       DbType.DateTime,"DateConsigned", DataRowVersion.Current);
    insertCommand.AddInParameter("@WarehouseCode", 
       DbType.Int64,"WarehouseCode", DataRowVersion.Current);
    insertCommand.AddInParameter("@CheckedBy", 
       DbType.String,"CheckedBy", DataRowVersion.Current);
    insertCommand.AddInParameter("@DateChecked", 
       DbType.DateTime,"DateChecked", DataRowVersion.Current);
    insertCommand.AddInParameter("@DNNo", 
       DbType.Int64,"DNNo", DataRowVersion.Current);
    insertCommand.AddInParameter("@UserID", 
       DbType.Int32,"UserID", DataRowVersion.Current);
    DBCommandWrapper updateCommand = 
       myDatabase.GetStoredProcCommandWrapper("sp_ConsignmentsUpdate");
    updateCommand.AddInParameter("@ConsignmentNo", 
       DbType.Int64,"ConsignmentNo", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@POID", 
       DbType.Int64,"POID", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@ReceivingOfficer", 
       DbType.String,"ReceivingOfficer", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@DateConsigned", 
       DbType.DateTime,"DateConsigned", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@WarehouseCode", 
       DbType.Int64,"WarehouseCode", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@CheckedBy", 
       DbType.String,"CheckedBy", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@DateChecked", 
       DbType.DateTime,"DateChecked", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@DNNo", 
       DbType.Int64,"DNNo", DataRowVersion.Current);
    
    updateCommand.AddInParameter("@UserID", 
       DbType.Int32,"UserID", DataRowVersion.Current);
    
    DBCommandWrapper deleteCommand = 
       myDatabase.GetStoredProcCommandWrapper("sp_ConsignmentsDelete");
    
    deleteCommand.AddInParameter("@ConsignmentNo", 
       DbType.Int64,"ConsignmentNo", DataRowVersion.Current);
    
    IDbDataAdapter dbAdapter = 
       (IDbDataAdapter)myDatabase.GetDataAdapter();
    dbAdapter.InsertCommand=insertCommand.Command;
    dbAdapter.UpdateCommand=updateCommand.Command;
    dbAdapter.DeleteCommand=deleteCommand.Command;
    dbAdapter.InsertCommand.Connection=trans.Connection;
    dbAdapter.InsertCommand.Transaction=trans;
    dbAdapter.UpdateCommand.Connection=trans.Connection;
    dbAdapter.UpdateCommand.Transaction=trans;
    dbAdapter.DeleteCommand.Connection=trans.Connection;
    dbAdapter.DeleteCommand.Transaction=trans;
    try
    {
        ((DbDataAdapter)dbAdapter).Update(table);
    }
    catch
    {
        throw ;
    }
}

Figure 7. The Update method in the TableGateway.

I find this self explanatory. If you don't understand the snippets please go through the collection of classes in the download that will help you explore the whole world. In the end, I would like to say I am very sorry for not being able to include every thing that you would expect, e.g. all the stored procedures mentioned in this article. I thought they were unnecessary and wanted to make the article as short as possible. Lastly, I welcome your contribution to modify this for a better solution.

Happy programming!!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Amour Rashid
Web Developer
Tanzania (United Republic Of) Tanzania (United Republic Of)
Born On the Pemba Island in Zanzibar. Joined the IT world in 1999 when Joined University of Dar Es Salaam for my BSc in Computer Science and Statistics.
 
I started Development with Visual Basic 5,Tried Some Java and Finally Embraced C# and .NET.
 
It is very wonderful.

Comments and Discussions

 
GeneralMy vote of 2 Pinmemberd_saravanan12-Dec-11 1:19 
GeneralUpdating a DataSet with Multiple Tables using Enterprise Library and unTyped DataSet PinmemberArun kumar patro20-Nov-07 3:11 
GeneralRuntime SQL and Recursive Table Order PinmemberRicardo Casquete22-Feb-06 2:59 
GeneralRe: Runtime SQL and Recursive Table Order Pinmembercerdoski6-Jul-06 6:27 
GeneralRe: Runtime SQL and Recursive Table Order PinmemberRicardo Casquete6-Jul-06 21:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 21 Oct 2005
Article Copyright 2005 by Amour Rashid
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid