XSLT–A Relational DB Approach Using DataSet.GetXML Output

All these hierarchies are getting in my way!

I was speaking with a friend of mine the other day about XSLT. He hasn’t used it much and I told him of an approach that makes it easy to use once you get the hang of it. I have found that for most developers XSLT has a steep learning curve. Some things about XSLT just stumps us.

For example, we are used to how variables work. You set them and then you can change them later if you want to. Well, XSLT doesn’t work like that. Oh, it has variables but once set, they can’t be changed, which isn’t intuitive to a developer.

Also, developers sometimes create deep hierarchies in their XML. This can make retrieving the data in XSLT challenging—especially when some levels have sub-levels and other levels don’t.

If you create XML the way that I suggest here and use the patterns for XSLT that I demonstrate, you’ll have an easier time with the whole thing.

I’ve included the XSLTDataApproach.zip which is a zip file of the project, the Northwinds database and some sample XML. You might want to download this first and check it out before moving forward.

(By the way, you can use the approach even if you don’t code in .NET. )

Creating the XML
The first step is to make your life easy by creating XML that is easy to consume in XSLT. Instead of deep hierarchies, use XML structured like a relational database. Here’s a piece of XML from the example project below (you might recognize the data from the Northwind database.):

   1: <?xml version="1.0" encoding="utf-8" ?>
   2: <Data>
   3:   <Customers>
   4:     <CustomerID>VINET</CustomerID>
   5:     <CompanyName>Vins et alcools Chevalier</CompanyName>
   6:     <ContactName>Paul Henriot</ContactName>
   7:     <ContactTitle>Accounting Manager</ContactTitle>
   8:     <Address>59 rue de l'Abbaye</Address>
   9:     <City>Reims</City>
  10:     <PostalCode>51100</PostalCode>
  11:     <Country>France</Country>
  12:     <Phone>26.47.15.10</Phone>
  13:     <Fax>26.47.15.11</Fax>
  14:   </Customers>

There are three and only three levels to the XML file. If you’ve ever used the DataSet.GetXML() function, you’ll recognize this XML structure immediately.

First, you have the document level. I called it Data. You can call it whatever you like. If you’re making this with a .NET DataSet (or DataTable) this is the name of the DataSet (or DataTable).

The second level is the table row level. You’ll have an element here for each row in each table. So, if you have three rows in the Customers table, you’ll have three Customers elements.

The third level is the column (field) level. For each field that you want in a row in the table, you create an element and place the value in there.

That’s it. It’s that simple.

Notice that we don’t use attributes. This makes things simpler in the XSLT as well. Plus, we avoid issues with fields that contain XML elements and special characters. Using elements, we can just use a CDATA tag set if we have text that will cause problems.

Creating the XML from a DataSet

It is so convenient that the DataSet.GetXML() function emits this exact structure. Here’s some sample code in C# showing how to create the above XML for the Customer, Orders, Order Details and Products tables for a given CustomerID in the Northwind database:

static string GetCustomerOrdersXML(string customerID)
{
    SqlConnection conn = new SqlConnection(@"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\Northwind.mdf;Database=Northwind;Trusted_Connection=Yes;");
    conn.Open();
    SqlCommand cmd = new SqlCommand(
        "SELECT [Customers].* FROM [Customers] WHERE CustomerID = @CustomerID;" + 
        "SELECT [Orders].* FROM [Orders] WHERE CustomerID = @CustomerID;" +
        "SELECT [Order Details].* FROM [Order Details] JOIN [Orders] ON [Orders].OrderID = [Order Details].OrderID AND [Orders].CustomerID = @CustomerID;" +
        "SELECT [Products].* FROM [Products] JOIN [Order Details] ON [Products].ProductID = [Order Details].ProductID JOIN [Orders] ON [Orders].OrderID = [Order Details].OrderID AND [Orders].CustomerID = @CustomerID",
        conn);
    cmd.Parameters.AddWithValue("@CustomerID", customerID);
    SqlDataReader dr = cmd.ExecuteReader();
    DataSet ds = new DataSet("Data");
    ds.Load(dr, LoadOption.OverwriteChanges, new string[4] { "Customers", "Orders", "OrderDetails", "Products" });
    conn.Close();
    return ds.GetXml();
}

The SQL command has 4 select statements so that you can get all the data in one request using the DataSet Load() command. Ideally this would be in a stored procedure.

The GetCustomerOrdersXML function creates the DataSet and loads it from an open DataReader creating a new table for each result set. You can name each of the tables (result sets) by providing an array of the names in the order they are being returned as a parameter to the Load() command. These names become the row element names in the XML. This is also incredibly convenient. We’ve created usable XML without having to do a single string concatenation. Of course, you could also use SQL Server’s XML features but the approach above is usually easier to crank out and maintain.

Pay close attention to the structure of the SELECT statements.

What you’ll notice is that I’m joining only for the purposes of finding the data. I’m actually just returning data from one table at a time in each SELECT statement on purpose. I want to do my actual joining in the XSLT through the use of nested templates (you’ll see below). Doing this gives you some flexibility in the future if you want to move data around within the template or use the same data in a different way. Make sure that your result sets include primary and foreign keys since you’ll be using XSLT searches to join them.

Creating the XSLT

Finally, here’s the XSLT in the format that I’m talking about:

   1: <?xml version="1.0" encoding="UTF-8" ?>
   2: <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   3:  
   4:   <xsl:output indent="yes" method="html"/>
   5:   <xsl:preserve-space elements = "*" />
   6:  
   7:   <xsl:template name="EmitProduct">
   8:     <xsl:param name="ProductID" />
   9:       <xsl:for-each select="/Data/Products[ProductID=$ProductID]"><xsl:value-of select="ProductName"/></xsl:for-each>
  10:   </xsl:template>
  11:  
  12:   <xsl:template name="EmitOrderDetails">
  13:     <xsl:param name="OrderID" />
  14:     <table cellpadding="2" cellspacing="0" border="1" width="100%">
  15:       <tr>
  16:         <th>Product</th>
  17:         <th>Unit Price</th>
  18:         <th>Quantity</th>
  19:       </tr>
  20:       <xsl:for-each select="/Data/OrderDetails[OrderID=$OrderID]">
  21:         <tr>
  22:           <td><xsl:call-template name="EmitProduct"><xsl:with-param name="ProductID" select="ProductID" /></xsl:call-template></td>
  23:           <td><xsl:value-of select="UnitPrice"/></td>
  24:           <td><xsl:value-of select="Quantity"/></td>
  25:         </tr>
  26:       </xsl:for-each>
  27:     </table>
  28:   </xsl:template>
  29:   
  30:   <xsl:template name="EmitOrder">
  31:     <xsl:param name="OrderID" />
  32:     <table cellpadding="2" cellspacing="0" border="1"  width="100%">
  33:       <xsl:for-each select="/Data/Orders[OrderID=$OrderID]">
  34:         <tr>
  35:           <td style="background:#e0e0e0">Order ID:</td>
  36:           <td style="background:#e0e0e0"><xsl:value-of select="OrderID"/></td>
  37:           <td style="background:#e0e0e0">Date:</td>
  38:           <td style="background:#e0e0e0"><xsl:value-of select="OrderDate"/></td>
  39:         </tr>
  40:         <tr>
  41:           <td colspan="4">
  42:             <xsl:call-template name="EmitOrderDetails">
  43:               <xsl:with-param name="OrderID" select="OrderID" />
  44:             </xsl:call-template>
  45:           </td>
  46:         </tr>
  47:       </xsl:for-each>
  48:     </table>
  49:  
  50:   </xsl:template>
  51:  
  52:   <xsl:template name="EmitOrders">
  53:     <xsl:param name="CustomerID" />
  54:     <xsl:for-each select="/Data/Orders[CustomerID=$CustomerID]">
  55:       <xsl:call-template name="EmitOrder">
  56:         <xsl:with-param name="OrderID" select="OrderID" />
  57:       </xsl:call-template>
  58:     </xsl:for-each>
  59:   </xsl:template>
  60:  
  61:   <xsl:template match="/">
  62:  
  63:     <xsl:for-each select="/Data/Customers">
  64:       <h1><xsl:value-of select="CompanyName"/></h1>
  65:       <h2><xsl:value-of select="Address"/>, <xsl:value-of select="City"/>, <xsl:value-of select="PostalCode"/></h2>
  66:       <table cellpadding="2" cellspacing="0" border="0" width="800">
  67:         <tr>
  68:           <td>
  69:             <xsl:call-template name="EmitOrders"><xsl:with-param name="CustomerID" select="CustomerID" /></xsl:call-template>
  70:         </td>
  71:         </tr>
  72:       </table>
  73:     </xsl:for-each>
  74:   </xsl:template>
  75:  
  76:   </xsl:stylesheet>

It’s pretty long so you might want to study it a second or look at the one in the zip file above. When transformed with the XML created by the above routine, you’ll get this snippet of HTML:

Vins et alcools Chevalier

59 rue de l’Abbaye, Reims, 51100

Order ID: 10248 Date: 1996-07-04T00:00:00-04:00
Product Unit Price Quantity
Queso Cabrales 14.0000 12
Singaporean Hokkien Fried Mee 9.8000 10
Mozzarella di GiovanniMozzarella di Giovanni 34.8000 5
Order ID: 10274 Date: 1996-08-06T00:00:00-04:00
Product Unit Price Quantity
Flotemysost 17.2000 20
Mozzarella di GiovanniMozzarella di Giovanni 27.8000 7
Order ID: 10295 Date: 1996-09-02T00:00:00-04:00
Product Unit Price Quantity
Gnocchi di nonna Alice 30.4000 4
Order ID: 10737 Date: 1997-11-11T00:00:00-05:00
Product Unit Price Quantity
Konbu 6.0000 4
Jack’s New England Clam Chowder 9.6500 12
Order ID: 10739 Date: 1997-11-12T00:00:00-05:00
Product Unit Price Quantity
Inlagd Sill 19.0000 6
Filo Mix 7.0000 18

Let’s walk through the template.

The main template starts on line 61. We loop through each of the customers—in this case there’s only one—and then we emit the company name and the address. Then, we call the EmitOrders template.

EmitOrders starts on line 52. You’ll notice that we pass in a parameter for the CustomerID. This is where the "XSLT Joining" is taking place and shows the real power of an XSLT "variable". We use that $CustomerID variable to select only the rows in the Order’s table for that customer ID. In this example, it will be all the rows for the customer ID since that’s how we created the XML. Next, we call the EmitOrder (no s) template passing in the OrderID as the parameter (starting to see the pattern yet?).

EmitOrder starts on line 30. It looks for orders with the given OrderID. Of course, there should only be one. It emits the order ID, the date and then calls the EmitOrderDetails() template to display every item on the order.

By now, you should see the pattern. Every detail table is being given it’s own template with the primary or foreign keys as parameters. The values being emitted in the transform are searched for in the XML by specifying the key to the rows. If it is a foreign key, you’ll possibly have many rows. If it’s a primary key, then you’ll have just one row.

There’s more going on in the template. For example, I didn’t discuss the EmitProduct template. Play around with the data and the templates in the VS 2005 project  until you get the feel for it. I’ve already created the sandbox for you. Also, I include a play XML as well as the Northwind.mdf in the project to make things easy for you.

Let me know what you think about this approach. It has worked very well for me.

Amazon’s 1-Click MP3 Download

I just want that one song.

iTunes lets you download albums and songs. The catch—you need an iPod to listen to them away from the computer.

Now—how can I put this—oh, I know. Ok. I’ve heard of a few people that rip CDs and get their MP3s from chat rooms and P2P software. Of course, I have never done that—uh hmm. They do this to have more flexibility with their music. Most of the people that I know that do this purchased the cassettes (remember those) or have the CDs but just don’t want to bother with ripping. They occasionally sample the music but for the most part, they are honest.

My kids have iPods (I don’t—I just stream my music mostly . . . I like the variety). They have discovered some interesting music on iTunes. One song I heard them listening to is Amaranth by Nightwish from their "Dark Passion Play" album . It’s catchy.  Great band. Today, the song got stuck in my head. I have occasionally watched the video on You Tube. But, the quality of the sound is nothing like from a CD or on my kids’ iPods.

So, I considered having my kids burn the CD and the let me rip it so that I could hear the album. I don’t really like the entire album but this song . . . this song I wanted. But, after thinking about it, I didn’t want to spend the time doing all that. So, I just went to Amazon all prepared to buy the album because I want these guys to be around, their music is good, and that’s when I saw it . . .

Amazon just released the ability to purchase single songs in MP3 format. No catch. No licenses. Nothing. Just a 256k ripped song for $0.99.  Oh . . . it was 1-click. 10 seconds later I’m jamming to that song on my computer from the MP3 in WinAmp with repeat (now I’ll never get it out of my head).

So, why is this on Dev Blog?

Ah, that’s because, for me, Amazon has done it. They have created software that you don’t think about. Consider this. When I wanted the album, what did I do? I didn’t get in my car and go to Wal-Mart (yeah, like they have Nightwish) or Best Buy. I didn’t even go to their websites. I went to a BOOK website expecting to get the album by Friday and instead, I got the single song I wanted instantly.

I don’t think I made my point though. Let me try again.

When I bought the song, I didn’t really think about what I was doing. I just did it. They have become a habit to me as the place to go for things. By the way, I just realized that I was looking at the new Sony 56 inch wide-screen TVs on Amazon the other day. They were cheaper than BestBuy and had free shipping. If I buy one, I think I’m going to try Amazon. Jeff Bezos, if you’re listening, congratulations, Amazon has just become equivalent with purchasing in my mind and I didn’t even see it coming. Nice job.

See, that’s the ultimate goal of software as a product for a company.

People use it, they expect it, and it becomes a part of their life without consideration. Just like the OS you’re reading this website on, and the browser . . . that’s the goal.

By the way Jeff, if you take the wifi concept you have in Kindle and hook it up with this MP3 download . . . you’ll be unstoppable. Music on demand, 1-click, getting a 1 dollar a click. Nice. I can’t wait.

Sandboxes and Great Ideas

I wonder what would happen if I did this instead of what I’m doing now?

Sometimes, when you’re coding, you’ll have an idea–a good idea. But, instead of taking the risk to implement it in the middle of what you’re doing you think to yourself, "I don’t have time to try that now . . . plus . . . I might lose what I’m in the middle of."

Of course, you can backup files and take a stab at the idea. But often times, that will require ripping out a bunch of code or shoe horning things in. Or, even worse, it might take a while to stage an environment so that you can test the idea. So, instead you just toss the idea out.

But it doesn’t have to be that way.

First of all, if you have a good idea but you’re pretty sure you can’t do anything about it, then stop and at least comment about it. It doesn’t take long and at least the idea is written down for future efforts.

However, if you’re not that constrained but don’t want to jeopardize your current environment, then just crank up a new app and play a little. Give yourself permission to do that.

I can’t tell you how many times I’ve been been walking by a cube and someone asked me, "Hey! What happens if I want to do x." My response is always, "I don’t know (even if I do). Let’s open up a new app and play a second." It gives the developer asking the question permission to play. Plus, I always end up learning something or brushing off some cobwebs. Also, it helps me to make sure someone on my team isn’t about to do something crazy.

I almost always create play apps (I even have a folder for them) as console apps. They are easy to make and most of the time I’m just trying to work out something quick and small. This "sandbox" approach is really good for working things out. You don’t have to worry about harming your existing application at all. Also, you can always refer back to the play app later if you want to extend the idea in a risk free environment or shoot it off in an email to a coworker playing around with a similar idea. I like to think of them as "sketches".

If you’re working on a large application in a very complicated area, it can take a while to get to the point in your code where you’re exploring options. Having a little application on the side that just cuts to the chase can make your work go a lot faster and will definitely move you closer to the 10 in McConnell’s 10x Software Development site.

Let’s say I’m working up a new display format for dates for one specific page in a web site that takes 5 or 6 pages to get to every time I want to test it out. It’s better for me to play in my sandbox until I get the format right and then port that deep into the application rather than working through my 5 or 6 pages on each pass. This kind of thinking is what gets you closer to the 1 in 10.

Essentially, you shouldn’t worry about wasting time so much when working in a sandbox. Exploring is part of development–even if you don’t use the code. You’ve learned something. Maybe your idea works. Maybe it doesn’t. In the end, you and your company are better for it I believe.

So, get dirty and makes some mud pies in your sandbox!

Structured Walkthroughs

It’s been a long time since I’ve heard the term “Structured Walkthroughs”. Back in 1978, Edward Yourdon wrote a book by that name. I have the second edition of “Structured Walkthroughs“. It’s a good book. It gives you a good, human-perspective approach to critiquing other people’s work–that can get tricky sometimes.

In the late 90s, while working on large, client-server projects, we would walk a fellow coworker through our work and explain to them our approach for solving the problem we were working on. Usually, this person would be the technical lead or a more senior developer. For the most part, my walkthroughs were easy and I quickly learned the common techniques used in that shop. I was fortunate to be working with good people. That certainly makes life easier.

One habit I picked up was anticipation. As I coded, I began anticipating what my fellow coworkers would say about the solutions I was arriving at. This anticipation improved my code with respect to their standards. It made the whole process more efficient because I had less changes to make after I was done. Eventually, the quality of my code started to improve to the point that it was rare to find a bug in it. This fact didn’t go unnoticed. I got a reputation for turning in perfect code.

However, there was a small price. I ended up turning in my code a little later than most. Not much later, but enough that it didn’t go unnoticed either. I took a little extra time because I was anticipating–anticipating the walkthrough but also the QA and ultimately the customer’s looking at my finished product. I wanted it to be right–always.

Here’s a timeline graph of when thing happened with my code with respect to other developers:

Productivity Graph

Figure1: Productivity Graph

While my first delivery to QA was typically much later than most of the developers on the team, my overall delivery to the customer was much sooner because my work spent less time in QA. You can show this graph to a project manager and they’ll understand what’s going on. Doing it in real life however is a whole different story.

“Why isn’t your code in QA yet?” they’ll ask. If you’re anticipating and creating quality code, just smile and say, “it’s coming” and don’t worry. In the end, you’ll have a better result. I always do without fail.

One of the best lessons I took away from the Yourdon book was about games. Chapter 9, “Games Programmers Play” was an extension of the classic book by Ernie Berne, “Games People Play”. Basically, there are patterns that people operate under that help them to avoid responsibility for the work. Look up transactional analysis if you’re curious about these games.

However, the lesson I learned wasn’t so much about other people’s behavior–it was about mine. I learned to recognize when I was playing the games and avoiding the truth about the problems that I was solving. This helped me to stop playing the games. My productivity improved. This made life easier as you can imagine–improved self-esteem, confidence, etc.

So, the next time you get stuck with a problem or are about to turn over some code, stop playing games, grab a coworker and take them for a walk. And if you can’t find a coworker, pretend and go for a walk anyway. Over the years, I’ve created a faceless, relentless uber-coder comprised of all the authors that I respect that I take on walks through my code. He’s not for the faint of heart but if he joins you, your code will be the better for it. Give him a try sometime (imagine a deep, echoing laugh . . . moo ha ha ha).

FileBots — Quick, File Processing Tools

FileBots transform files quickly, on-the-fly from the comfort of your very own Windows Explorer.

Occasionally, I’ll need to transform text files that I’m working. This will usually happen when I’m porting over old, ASP-based applications to ASP.NET or converting from VB.NET to C#—that sort of thing.

Recently, I was converting over an old site that had HTML in all uppercase. This used to be the standard. But, as you know, XHTML requires everything to be in lower case.

To solve the problem, I really didn’t need anything fancy. I wasn’t trying to create anything perfect. No need to employ DOMs or YACC and such. I just wanted to crank out something real quick that would convert the majority of the HTML as I needed it. There’s probably even a plugin to VS for this but I didn’t look. If you find one, let me know. VS doesn’t do this for me that I could find.

So, to make my life easier with as little effort as possible, I created a console app (.EXE) that takes the first argument as the name of the file. In the console application, I just load the file, do the processing and then save the file with a ".actionTaken" extension. Done! I call these little applications FileBots.

Let’s look at at a FileBot a little closer. Here’s the Main() routine from a recent FileBot that I created that lowercases the elements and attributes in an HTML file:

static void Main(string[] args)
{
    if (args.Length == 1)
    {
        File.WriteAllText(args[0] + ".lower", LowerCaseHtml(LowerCaseAttributes(File.ReadAllText(args[0]))));
        Process np = new Process();
        np.EnableRaisingEvents = false;
        np.StartInfo.Arguments = args[0] + ".lower";
        np.StartInfo.FileName = "notepad.exe";
        np.Start();
        np.Close();

    }
    else
    {
        Console.WriteLine("Usage:");
        Console.WriteLine("");
        Console.WriteLine(" ConvertUpperHTMLToLowerHTML.exe filename");
        Console.WriteLine("");
        Console.WriteLine("Outputs filename.lower");
    }
}

Example 1: C# Main for a FileBot

Pretty straight forward, yes?

"Yeah, but you just created a console application! How is that going to make things easier for me?" you might say. Well, here’s the sweet part about Windows. Drop this .EXE in to your SendTo folder located here:

C:\Documents and Settings\LoginName\SendTo

Now, when you right-click on a file, you’ll see that the .EXE shows up in the Send To menu as a valid option. When you choose the program from the menu, the Windows explorer executes the application with– you guessed it–the file name as the first argument!

In the example above, you’ll notice that in just one line of code, I read the file, process it and write it back down. I don’t even bother with error handling. No need.

In the code that follows, I start notepad.exe and pass the output filename to it. You don’t have to open the file in notepad. You can do whatever you want including not doing anything.

This is really useful I think. Take it one step further. You can select the previous output filename and then send it to another FileBot until you get the final results you’re looking for. This is similar to the UNIX and MS-DOS pipe without having to go to a command console and chain them together.

The key is, when developing, don’t try to over engineer your scaffolding code. Be smart. Use the OS and the development environments to help you move quickly. And, I sure as heck had a lot more fun creating the above than searching and replacing HTML in hundreds of files. Take a second every now and then to add a tool to your chest. It pays off in the long run.

Now, go make some FileBots!