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