Monthly Archives: July 2008

Retrieving XML from Microsoft SQL Server


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.

EDI CCNET Web Dashboard

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())
       return new XmlDocument().Load(reader);

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.

Screenshots on the iPod Touch and other enhancements in v2.0

iPod Touch screen shot feature in v2.0

With the release of v2.0 firmware for the iPhone/iPod Touch Apple added a much needed feature which is the ability to take screenshots. If you hold the home button down and press and release the power button the screen will blank out for a split second giving the illusion of a photo being taken, a very nice touch. The screenshot is stored in your photos and easily accessible. When you reconnect to Windows you’ll first see this prompt:

Windows AutoPlay dialog for importing iPod Touch pictures

If you select the highlighted option you’ll see the following prompt:

Vista’s Importing Pictures and Video dialog

Which will allow you to quickly grab these screenshots for use in things like blog posts.  🙂

Keyboard Improvements

There are a number of other nice features in the v2.0 firmware like this minor but super useful change in keyboard layout (below left). Notice the spacebar is 1/3 size and the @ symbol and the period are provided for things like email addresses. The screenshot on the right illustrates the new password handling where the last character that you typed stays on screen for a second before switching to a dot which makes it easier to correctly enter passwords (notice the spacebar in this case is full size).

image   image

Review of iPhone v2.0 applications

I’m going to use this post to rate the iPhone/iPod Touch applications I’ve tried using a one to five rating. In alphabetical order:

  • Apple’s Remote 
    I think this is a great little app.
  • Banner 
    Cool scrolling LED sign app, free but missing a number of key features like scroll speed, color etc. still cool
  • Bloomberg 
    Ignore’s accelerometer so no wide screen reading. I think I like the white text on black but we’ll see. Portfolio tracking could be a bit better but hey, it’s free!
  • BofA Banking App –
    It’s just their web site in an app
  • Facebook –  
    The update-to-date address book alone is awesome
  • Google’s Mobile App
    Like BofA there are just a bunch of links to web apps. I’ll have to play more with the search capabilities to see how those improve on the web version.
  • NY Times –
    Seems pretty slow, though nice to have.
  • Pandora – (added July 13, 2008)
    Radio comes to the Touch!
  • Tap Tap Revenge – (added July 16, 2008)
    I can’t stop laughing when the dots start streaming down faster than my reflexes can handle!!
  • Where
    For the longest time it displayed the large red “W” then returned to the home screen. IOW, it did nothing. I think their servers are probably getting pounded as just browsing to is very slow or times out. It’s working now though only in pieces but I can see there is a ton of stuff to look at. I’m not sure I like bubble selector at the bottom.

What apps are you liking?

Apple's Remote iPhone application rocks!


With the introduction of v2.0 of the iPhone/iPod Touch software Apple introduced an application called Remote giving you the ability to remotely (surprise) control iTunes on your computer and yes, it’s two way. Personally, I’ve been wanting this sort of functionality for a long time as I’ve had my computer connected to my receiver so I can listen to my $g(Yahoo LaunchCAST) station. The downside is the lack of control as my computer is upstairs in my office and my receiver is downstairs in my family room. With Apple’s Remote application can control what’s playing from anywhere in the house, very nice!

I think Remote is a great example of how the iPhone is going to showcase new ways for people to interact with their PC’s remotely. I can already imagine a dozen other situations where I’d like to be able to remotely control other applications on my machine.

Now, where’s Remote Desktop?  🙂

iPod Touch 2.0 finally available only I can't download it

Like pretty much everyone it seems I’ve been awaiting the update to v2.0 of the iPhone/iPod software and according to iTunes an update is available as of 7:30pm PST however:

We could not complete your iTunes Store request 
I’ve been able to download a number of what appear to be interesting applications so I’m definitely interested in getting my iPod updated. Here’s what I’ve found:

iPod Touch Applications - Facebook, Google Moblie, NYTimes, Remote, ShoZu, WHERE 
Anyone else find something interesting?

Also, anyone know if the new Exchange support works with Intermedia’s hosted Exchange servers?

[Update: July 11, 2008 9:27pm PST] Now downloading…
[Update: July 11, 2008 9:31pm PST] Backing up, preparing for restore…
[Update: July 11, 2008 10:00pm PST] Done installing, exploring the above apps. Google’s app seems very lame as it’s just a bunch of icons to web pages, what am I missing??

Other iPod posts:
Is the new iPod Touch worth it?
My answer to Apple’s iPod Touch is it worth it?
Protecting the iPod Touch with an Agent 18 Shield Case

Setting Subsonic's connectionstring at runtime

I’m a fan of the Open Source project SubSonic, spearheaded by Rob Conery of Microsoft, which describes itself thusly:

A Super High-fidelity Batman Utility Belt. SubSonic works up your DAL for you, throws in some much-needed utility functions, and generally speeds along your dev cycle.

I’ve seen a few questions on the Internet about setting the SubSonic connectionstring at runtime and thought I’d post what I’ve done. SubSonic includes a tool to generate a DAL for you which picks up settings from your .config file which provides the connectionstring among other things. I’m using SubSonic in a plugin to CruiseControl.NET for EDI processing and here the portion of my ccservice.exe.config file related to SubSonic:

<?xml version="1.0" encoding="utf-8"?> <configuration> <configSections> <section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" requirePermission="false"/> </configSections> <connectionStrings> <add name="Velocity2" connectionString="Data Source=db;Initial Catalog=db;Persist Security Info=True;" providerName="System.Data.SqlClient" /> </connectionStrings> <SubSonicService defaultProvider="Velocity"> <providers> <clear/> <add name="Velocity" type="Subsonic.SqlDataProvider, SubSonic" connectionStringName="Velocity2" generatedNamespace="VelocitySubSonic" includeTableList="AR_*,CORE_*,EDI_*,II_*,IN_*,SA_*" excludeProcedureList="WMS_InsertImportQueue" /> </providers> </SubSonicService> ...

One of the things I wanted to do is override the connectionstring using a property on my CCNET plugin. To do that I implemented a property like this:

[ReflectorProperty("connectionString")] public string ConnectionString { get { return m_connectionstring; } set { m_connectionstring = value; SubSonic.DataService.GetInstance("Velocity").SetDefaultConnectionString(value); } }

The instance name comes from the name used in the config file above then simply call SetDefaultConnectionString and voila the SubSonic connectionstring is now set at runtime.