Tuesday, July 31, 2007
« Visual Studio 2008 Beta 2 and .NET FX 3.... | Main | Executing a workflow »
Using XML to store object graphs into SQL Server 2005

Srinivas Surampalli, a fellow Magenic consultant, just wrote the following article:

Using XML with Stored Procedures Effectively in SQL Server 2005

One common question that comes up relative to CSLA data access is how to efficiently save an object graph where there's a parent with children or even grandchildren.

The typical answer is to make an INSERT/UPDATE/DELETE call for the parent and each child. This article shows a different approach, where you could put the business object data into a DTO object graph, serialize that object graph into XML and make a single database call.

What surprised me, looking at the article's code, is that the SQL in the stored procedure is so straightforward.

As always, you should test different approaches to find the one that works best for your application, based on performance, maintainability and other requirements.

Tuesday, July 31, 2007 8:15:00 AM (Central Standard Time, UTC-06:00)  #    Disclaimer  |  Comments [3]  | 

Tuesday, July 31, 2007 11:04:44 AM (Central Standard Time, UTC-06:00)
If you use this approach isn't the database now dependent on the object model in the business layer? Shouldn't there be some sort of abstraction or mapping before you pass the XML to the the stored procedure?
Nate Kresse
Tuesday, July 31, 2007 11:24:14 AM (Central Standard Time, UTC-06:00)
That's why you use data transfer objects (DTOs) as a go-between to get data from the db to the business objects and back again.

The DTO pattern is rapidly becoming the dominant model, for good or ill. LINQ, ADO.NET EF, web services, WCF services and similar technologies all provide you (the business object author) with DTOs.

In my view, we're looking at a future where the dominant "data access" model for business object authors is to get and put data into and out of DTOs, and to let some other technology handle the persistance of those DTOs.

Is this less efficient than loading your objects directly from a DataReader? Sure. But the productivity gains and reduction of overall complexity (thanks to designers for LINQ and EF) will often offset the performance cost. Given a choice, the majority of architects will opt for maintainability and lower cost over performance.
Rockford Lhotka
Wednesday, August 01, 2007 4:50:21 PM (Central Standard Time, UTC-06:00)
(third try with FireFox...IE7, or this site, is timimg out on POST)

There are a few caveats: (

1) this is handling only inserts. The code will become a nightmare when handling updates and deletes. The object may have a hierarchy but this must be translated into relationel lingo. Those stored procedures will become hell to maintain if done by hand. BTW, all major db vendors (MS, Oracle and IBM) support similar syntax.

2) parsing XML on SQL is efficient but CPU intensive. You are trading a network bottleneck for CPU. Actually, that may be good. CPU is much cheaper than Network or Disk IO, but your mileage may vary and you may lose in some cases. Especially with large objects and/or scenarios when you have **lots** of concurrent inserts/updates.

PS: SQL 2008 supports the MERGE operator. In short, you can handle INSERT/UPDATE/DELETE in one statement in that stored procedure...(wow)...If we automated the code generation of that sp we can hit a home run! Still, we would have to test, test, test because MERGE is doing its magic by using a FULL OUTER JOIN under the covers and their is a price tag to that too (TANSTAAFL - http://en.wikipedia.org/wiki/TANSTAAFL), but there are powerful things building up under the cover (SQL2008-ANSI-SQL:2006-XML-LINQ-IMMORTAL TABLES-WPF-Silverlight-C#3..0-who said life was dull?!?)



Eric
Comments are closed.