his is Part II of an article series that describes how to pass data into and out of SQL Server not as raw data or as DataSets, but as objects that represent business entities. Getting data out of SQL Server and into an object, a process described in detail in Part I
of the article, is fairly straightforward, as long as you can write a query that returns an XML document with the appropriate schema. This technique, however, wouldn't be nearly as useful without the ability to pass objects into
SQL Server as well. This article describes how to the reverse the extraction processhow to submit an Order object directly to SQL Server (the definition of the Order class that represents orders was explained in Part I of this article). In this discussion, the presentation tier populates the properties of an Order object, which the code serializes into XML using a middle tier component and then submits to a stored procedure. You don't need all
the members of the Order class to commit new orders to the database. Obviously, you don't need to set the customer's name or the product names in a new orderall you need are the IDs. Even if the presentation tier sets the names, the stored procedure ignores them and uses the IDs instead. You can design a new class to submit new orders to the database, but there's no compelling reason to do so.
This technique arose from an application that had to record orders with many detail lines. The orders had to be inserted into the database within a transaction, so there were only two options:
- Initiate a transaction on a Connection object and then submit each row to the database.
- Create a stored procedure that accepts order values as arguments and performs the transaction right on the server.
It's generally better to use the stored procedure option to perform the transaction against the database rather than initiate a transaction on a Connection object. When possible, you should isolate transactions to the data tier, because it's the DBMS that actually performs the transactions. Of course, this isn't possible when you work with multiple databases, but most small business applications don't perform transactions against multiple databases.
Having decided to use a stored procedure, you now have the problem of figuring out how to pass a large (and/or unknown) number of arguments to a stored procedure. In this case, the order's header consists of a fixed number of fields, but the order body may contain any number of detail lines. One approach is to create a delimited string containing the order's fields, pass that to the stored procedure, and write T-SQL code to parse the string, but that's inefficient at both the presentation tier (you have to build a long string) and at the stored procedure level (you have to write T-SQL code to parse the string and extract the individual fields). Using SQL Server's support for XML you can create an XML document containing the data that represents the order, pass the generated document as an argument to a stored procedure, and then use the built-in XML parser to process the XML data within the stored procedure's code.
With .NET, converting an instance of a class that represents an order into an XML document is almost trivial, thanks to the XmlSerializer class. Although certainly not in common use (yet) you can use this interesting technique to pass an unknown number of arguments to a stored procedureand it works regardless of whether you use business objects in your presentation tier.