Who Needs ORM, I've got SQL 2005
One of the coolest features of SQL 2005 is the XML support. With the recent enhancements to the FOR XML option, you can really get a lot of mileage out of SQL. A common task in a lot of applications involves retrieving an item with many children. Due to the way a lot of systems are built, this usually ends up resulting in a lot of extra queries. For example, suppose I have a customer entity like so:
public class Customer
{
public int CustomerID;
public string Name;
public string State;
public Collection<HistoryItem> History;
}
When I want to get a list of customers in a certain state, along with their histories, I might do something like this:
SELECT * from Customer where State = 'CA'
Then for each customer returned:
SELECT * from CustomerHistory where Customer = @CustomerID
The, for each of the result sets that is returned, you need to write code to take the info out of the data reader or data table and place it in your objects. As more customers are returned, the number of addition queries I need to make starts skyrocketing. The problem only gets worse as the number of levels deep increases. With SQL 2005's great XML path support, there is a really sweet thing you can do to get rid of all those extra queries and all that extra code.
SELECT CustomerID as "@CustomerID", Name as "@Name", State as "@State",
(SELECT CustomerHistoryID as "@CustomerHistoryID", SomeOtherField as "@SomeOtherField" from CustomerHistory Where CustomerHistory.CustomerID = Customer.CustomerID FOR XML Path('HistoryItem'), TYPE ) as "History"
from Customer FOR XML Path('Customer'), Root ('Customers'), Type
This ends up returning something like so:
<Customers>
<Customer CustomerID="123" Name="Jesse Ezell" State="CA">
<History>
<HistoryItem CustomerHistoryID="456" SomeOtherField="Blah" />
...
</History>
</Customer>
...
</Customers>
Here's where the magic comes in. Now, you've squished all those queries into a single command, and, if you execute your command with ExecuteXmlReader, you can pass the XmlReader straight to an XML serializer and return your object graph without having to write all that tedious get / set nonsense.