SQL Server 2008 - The MERGE and TVP data access pattern with DataAdapters

The MERGE statement and Table Valued Parameters (TVPs) are two new features in SQL Server 2008 that make the process of doing DML (Data Manipulation i.e. INSERT, UPDATE and DELETE) on multiple rows very easy. This article shows how you can adapt

Historically to save data using the DataAdpater you configure it with insert, update and delete statements, pass it a DataTable of changed data and it will then look at the DataTable and fire off the relevant insert, update or delete statement.

This is fine except it fires off a statement for each change. This isn’t very efficient due to, overheads with the network, making a connection to the database and performing a single row change in the database. We ideally want to reduce network calls and perform SET based operations in the database. TVPs and the MERGE statement allow us to do just that.

So how does the DataAdapater work using a TVP. The short answer is, it doesn’t. Fortunately its fairly easy to amend your code to use TVPs

If you’ve read the article on handling DELETEs with MERGE you will know that the best way to handle DELETES with MERGE is to pass them as soft deletes, i.e. flag the row as DELETED using an extra column in the table.

To do that we take the DataTable containing our changes and we add a column. We now need to populate this column for DELETED rows (we don’t need to populate it for the INSERTS or UPDATEs). When you DELETE a row in a DataTable you can’t make any further modifications to the row. Therefore we have to reject the change, which allows us to change the rows, and then update the column with a “D” for DELETED

 

DataTable ChangedDT = SourceDT.GetChanges (DataRowState.Added | DataRowState.Modified | DataRowState.Deleted|DataRowState);

DataColumn DelCol = ChangedDT.Columns.Add("action",typeof(string));

              

foreach (DataRow ChangedRow in ChangedDT.Rows )

{

    if (ChangedRow.RowState == DataRowState.Deleted)

    {

        ChangedRow.RejectChanges();

        ChangedRow ["action"] = "D";

    }

}

Now we have a DataTable only containing the changes and with our DELETED rows flagged with the D we need to create a SqlCommand object that will call the stored procedure we are using.

This is a normal SqlCommand with the commandText set to the name of the procedure. Creating the parameter is slightly different to normal, the SqlDbType of the parameter has to be SqlDbType.Structured. This is the type for all table valued parameters. In addition to this you have to specify the TypeName, this is used to say which TABLE TYPE your table valued parameter is. From my testing the TypeName is only required if your CommandType is NOT CommandType.StoreProcedure (So in the example below setting TypeName isn’t required), I guess this is because for StoredProcedures the type of the table is defined in the stored procedure definition.

 

Once the SqlCommand  is setup you can execute it as you would any other SqlCommand.

 

SqlCommand UpdateCommand = new SqlCommand();

UpdateCommand.Connection = conn;

UpdateCommand.CommandText = "dbo.up_OrderDetail_SaveSet";

   

UpdateCommand.CommandType = CommandType.StoredProcedure;

 

UpdateCommand.Parameters.AddWithValue("@ChangedOrderDetail", ChangedDT);

UpdateCommand.Parameters[0].SqlDbType = SqlDbType.Structured;

UpdateCommand.Parameters[0].TypeName = "SalesOrderDetail_Type";

 

conn.Open();

UpdateCommand.ExecuteNonQuery();

 

The stored procedure used by this code is derived from the MERGE example in the “Handling Deletes with MERGE” article.

 

The thing to note is that your TABLE TYPE has to have the additional column in it so the action can be passed through.

 

CREATE TYPE SalesOrderDetail_Type  as TABLE (

    [SalesOrderID]          [int] NOT NULL ,

    [SalesOrderDetailID]    [int] IDENTITY(1,1) NOT NULL,

    [CarrierTrackingNumber] [nvarchar](25) NULL,

    [OrderQty]              [smallint] NOT NULL,

    [ProductID]             [int] NOT NULL,

    [SpecialOfferID]        [int] NOT NULL,

    [UnitPrice]             [money] NOT NULL,

    [UnitPriceDiscount]     [money] NOT NULL,

    [LineTotal]             AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

    [rowguid]               [uniqueidentifier] ROWGUIDCOL  NOT NULL,

    [ModifiedDate]          [datetime] NOT NULL,

    Action                  char(1),

    PRIMARY KEY (SalesOrderId,SalesOrderDetailID))

 

 

We then create our stored procedure to use this type,

 

 

CREATE PROCEDURE up_OrderDetail_SaveSet

  @ChangedOrderDetail SalesOrderDetail_Type READONLY

 ,@SalesOrderID       int

AS

 

    MERGE Sales.SalesOrderDetail OrderDetail

    USING @ChangedOrderDetail    Chng        ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]

                                            AND Chng.[SalesOrderID]       = OrderDetail.[SalesOrderID]

    WHEN MATCHED AND Chng.Action = 'D' THEN

        DELETE

    WHEN MATCHED THEN

        UPDATE SET [SalesOrderID]            = Chng.SalesOrderID           

                  ,[CarrierTrackingNumber]   = Chng.CarrierTrackingNumber 

                  ,[OrderQty]                = Chng.OrderQty               

                  ,[ProductID]               = Chng.ProductID               

                  ,[SpecialOfferID]          = Chng.SpecialOfferID           

                  ,[UnitPrice]               = Chng.UnitPrice               

                  ,[UnitPriceDiscount]       = Chng.UnitPriceDiscount       

                  ,[rowguid]                 = Chng.rowguid               

                  ,[ModifiedDate]            = Chng.ModifiedDate           

    WHEN NOT MATCHED THEN

       INSERT ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice]

              ,[UnitPriceDiscount],[rowguid],[ModifiedDate])

       VALUES (Chng.SalesOrderID,Chng.CarrierTrackingNumber,Chng.OrderQty,Chng.ProductID

              ,Chng.SpecialOfferID ,Chng.UnitPrice,Chng.UnitPriceDiscount,Chng.rowguid,Chng.ModifiedDate);

 



-
Published Wednesday, June 18, 2008 6:19 PM by simonsabin

Comments

# Dew Droplet - June 19, 2008 | Alvin Ashcraft's Morning Dew

Pingback from  Dew Droplet - June 19, 2008 | Alvin Ashcraft's Morning Dew

Sunday, June 22, 2008 10:15 PM by Weekly Link Post 47 « Rhonda Tipton’s WebLog

# Weekly Link Post 47 « Rhonda Tipton’s WebLog

Pingback from  Weekly Link Post 47 « Rhonda Tipton’s WebLog