I’ve seen a few Conchango developers in the past use sp_xml_preparedocument and OPENXML to commit a serialized collection object to the database. We can do this in SQL Server 2000 and it avoids a round trip to the database for each collection member. This can mean a huge performance improvement. Imagine having to call an update stored proc 500 times for 500 members rather than just a single db call ! Applications that store collection(s) in memory prior to committal - e.g. 'save button' functionality - can benefit considerably from this approach.
The only problem (in 2000) was that we could not get back the newly generated identity column values. @@Identity or SCOPE_IDENTITY obviously don’t help because they relate to only one row. This meant we’d often have to re-populate the collection after the update, which meant another hit on the database.
However, in SQL Server 2005, we have DML with OUTPUT. This allows us to resolve this – i.e. we can repopulate the xml with the new identity column values ready to be de-serialized.
Here is an example of the update sproc for a Customer.Orders collection.
/*
<procname>usp_CustomerOrders_Update</procname>
<summary>
Update orders for a customer.orders collection
</summary>
<history>
<entry date="2005-11-24" name="Christian Wade" action="Created" />
</history>
*/
CREATE PROCEDURE [dbo].[usp_CustomerOrders_Update]
(
@CustomerID int,
@Orders xml OUT
)
AS
-- sample xml format:
--<Orders>
-- <Order OrderID="110" CustomerID="2" OrderValue="250.8711" OrderDate="2005-11-01T00:00:00" />
-- <Order OrderID="111" CustomerID="2" OrderValue="350.8711" OrderDate="2005-11-01T00:00:00" />
-- <Order OrderID="112" CustomerID="2" OrderValue="450.8711" OrderDate="2005-11-01T00:00:00" />
--</Orders>
BEGIN TRY
-- prepare temp tables
CREATE TABLE #InputOrder
(
OrderID int,
CustomerID int,
OrderValue smallmoney,
OrderDate smalldatetime
)
CREATE TABLE #OutputOrder
(
OrderID int,
CustomerID int,
OrderValue smallmoney,
OrderDate smalldatetime
)
-- populate input temp table
INSERT INTO #InputOrder
(
OrderID,
CustomerID,
OrderValue,
OrderDate
)
SELECT
OrdersTbl.rows.value('@OrderID', 'int'),
OrdersTbl.rows.value('@CustomerID', 'int'),
OrdersTbl.rows.value('@OrderValue', 'smallmoney'),
OrdersTbl.rows.value('@OrderDate', 'datetime')
FROM
@Orders.nodes('/Orders/Order') OrdersTbl(rows)
-- now we've prepared the data, update it in the database
BEGIN TRAN
-- first we want to delete existing orders for
-- the same customer that are not in our input dataset
DELETE FROM dbo.[Order]
WHERE CustomerID = @CustomerID
AND OrderID NOT IN
(
SELECT
OrderID
FROM
#InputOrder
)
-- now update orders with the same id
UPDATE dbo.[Order]
SET
CustomerID = i.CustomerID,
OrderValue = i.OrderValue,
OrderDate = i.OrderDate
OUTPUT
Inserted.OrderID,
Inserted.CustomerID,
Inserted.OrderValue,
Inserted.OrderDate
INTO #OutputOrder
FROM dbo.Order c
INNER JOIN
#InputOrder i ON c.OrderID = i.OrderID
-- now insert values that are not already in the order table
INSERT dbo.[Order]
(
CustomerID,
OrderValue,
OrderDate
)
OUTPUT
Inserted.OrderID,
Inserted.CustomerID,
Inserted.OrderValue,
Inserted.OrderDate
INTO #OutputOrder
SELECT
i.CustomerID,
i.OrderValue,
i.OrderDate
FROM
#InputOrder i
WHERE
i.OrderID NOT IN
(
SELECT
c2.OrderID
FROM
dbo.Order c2
)
COMMIT TRAN
SELECT
@Orders =
'<Orders>' +
(
SELECT
o.OrderID,
o.CustomerID,
o.OrderValue,
o.OrderDate
FROM
#OutputOrder o
FOR XML AUTO
) +
'</Orders>'
FROM
#OutputOrder
END TRY
BEGIN CATCH
EXECUTE usp_LogAndRethrowError
END CATCH
Here is the code that serialises the collection ready to be passed in as a parameter – and then deserializes it after calling the sproc.
public static string Serialize(object obj) { StringWriter writer = new StringWriter();
XmlSerializer xs = new XmlSerializer(obj.GetType());
xs.Serialize(writer, obj);
return writer.ToString();
}
public static object Deserialize(string xml, object obj) { XmlSerializer xs = new XmlSerializer(obj.GetType());
StringReader reader = new StringReader(xml);
return xs.Deserialize(reader);
}
Note: I had to use XmlAttributeAttribute on the properties of the Orders class to get it to serialize as the sproc expects it.