A
fter completing work on the EDI Invoicing system I’ve recently started work on the other half of the equation, Purchase Order processing. There’s an existing system in place but it’s old, brittle and doesn’t support everything the system requires these days. The new CCNET based Invoice processing is humming along nicely and has handled over $3 million in invoices.
Getting XML from a SqlDataReader
Like the invoicing system the purchase order side needs to query the database fetching PO data for conversion to EDI and transmission to the trading partner. I’ve been looking for clean ways to produce XML from a SqlDataReader and discovered this post by Roy Osherove. IMO, the interesting piece of that post isn’t necessarily Roy’s VB code but what’s found in the comments. Specifically, the comment I’m referring to is related to using a DbDataAdapter descendent like this:
public class DataReaderAdapter : DbDataAdapter
{
public int FillFromReader(DataTable dataTable, IDataReader dataReader)
{
return this.Fill(dataTable, dataReader);
}
protected override RowUpdatedEventArgs CreateRowUpdatedEvent(
DataRow dataRow,
IDbCommand command, StatementType statementType,
DataTableMapping tableMapping)
{ return null; }
protected override RowUpdatingEventArgs CreateRowUpdatingEvent(
DataRow dataRow,
IDbCommand command, StatementType statementType,
DataTableMapping tableMapping)
{ return null; }
protected override void OnRowUpdated(RowUpdatedEventArgs value)
{ }
protected override void OnRowUpdating(RowUpdatingEventArgs value)
{ }
}
A descendent is necessary here to help expose the inherited, protected Fill(...) method. Once you have the data in a DataTable you can simply call WriteXml() and viola! But there is an easier way (at least for MSSQL)!
Fetching XML from SQL Server using "FOR XML"
After mentioning the above to John Waters, Falafel’s CTO, he proposed querying directly FOR XML (literally), an MSSQL feature I wasn’t unaware of (no surprise there having been a non-database guy for so long). Using SqlCommand’s ExecuteXmlReader makes creating an XmlDocument very easy as you can see below. Simply setup the SqlCommand normally and use its ExecuteXmlReader() method:
using (XmlReader reader= command.ExecuteXmlReader())
{
try
{
reader.Read();
return new XmlDocument().Load(reader);
}
finally
{
reader.Close();
}
}
The SQL statement looks like this:
1 SELECT * FROM SA_OrderHeader WHERE OrderHeaderID = @OrderHeaderID FOR XML, AUTO ROOT('details')
Notice "AUTO ROOT" which allows you to control the name of the root node of the returned XML document, very handy.
Having said all this I’m sure there are other ways to accomplish the same thing so please let me know what you’ve come up with.