There is a reason technical titles are so boring: the title is trying to tell you what the technical content is about. Its still boring. If Google had been called web crawling, indexing search engine, or Starbucks had been called hip, expensive coffee shop I doubt they would have been as popular as they are. Same thing with Amazon.com and a bunch of other stuff. The difference between going to Starbucks and a technical article is that people will eventually get curious about what Starbucks might be and it catches on and then the coolness sells itself. If that approach were used with a technical article by the time, if ever, people discovered what the technical article is about the technology would have changed and no one would ever come. The equivalent in consumer product time would be—Seattle’s Pike Place opens Starbucks in 1974 and in the fall of 1974 people never went back to coffee except for three guys who also still love FoxPro. (I like to pick on the Fox guys because they get so irate and write the funniest hate mail. No, really, I love you FoxPro guys. FoxPro rocks.) I think the concept is known as branding. With consumer products there is time to associate McDonalds with hamburger. In Internet time and with technical articles branding probably doesn’t work. So, instead of something clever, like “The Inmates are Running the Asylum” (which Alan Cooper swiped from me-just kidding) this article is called the equivalent of “tasty, inexpensive hamburger place that sells cheap toys your kids will beg for, play with once, and then leave for you to step on while you are getting up to pee late at night”, but at least with this blog you know what you are getting into.
If you need to convert SQL data to XML with minimal fuss, pomp, and circumstance, and that data has some natural grouping arrangement and you are bi-curious about LINQ then this is your Erewhon—see Erewhon tells you I like Sam Butler but it tells you nothing about the technical content.
Suppose you have a process that uses an XML file. Further suppose that you want to update that file occasionally from a database. A technology exists that makes this easy. Its called function construction. Functional construction is where you can create an XML document from data using classes in the System.Xml.Linq namespace, specifically XElement and XAttribute. If the data comes from a database then you can use something like LINQ to DataSets to easily get the data from the database to LINQ and from LINQ to XML. Now, add a twist. What if the data represents natural groups like Order Details from the Northwind database. the challenge is not that you have SQL data and you want to produce a hierarchical XML document. That’s ok. LINQ supports grouping and you can mix grouping in with your XML and functional construction and produce the XML document. All of this can be done with relatively few lines of code. (For more on LINQ groups and functional construction check out my book LINQ Unleashed for C# from Sams.) Listing 1 shows the flat table structure of Northwind database’s order Details table. Listing 2 shows the XML structure we’d like to get it to.
Listing 1: A CREATE TABLE statement that shows the stripped down SQL script that will create the Northwind.[Order Details] table, including schema information.
USE [Northwind]
CREATE TABLE [dbo].[Order Details](
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT ((0)),
[Quantity] [smallint] NOT NULL CONSTRAINT [DF_Order_Details_Quantity] DEFAULT ((1)),
[Discount] [real] NOT NULL CONSTRAINT [DF_Order_Details_Discount] DEFAULT ((0)),
CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Listing 2: The target XML showing the order detail items grouped by OrderID (with just a few records selected to keep the listing short).
<?xml version="1.0" encoding="utf-8"?>
<Orders>
<Order ID="10248">
<Order_Detail>
<ProductID>11</ProductID>
<UnitPrice>14.0000</UnitPrice>
<Quantity>12</Quantity>
<Discount>0</Discount>
</Order_Detail>
<Order_Detail>
<ProductID>42</ProductID>
<UnitPrice>9.8000</UnitPrice>
<Quantity>10</Quantity>
<Discount>0</Discount>
</Order_Detail>
<Order_Detail>
<ProductID>72</ProductID>
<UnitPrice>34.8000</UnitPrice>
<Quantity>5</Quantity>
<Discount>0</Discount>
</Order_Detail>
</Order>
<Order ID="10249">
<Order_Detail>
<ProductID>14</ProductID>
<UnitPrice>18.6000</UnitPrice>
<Quantity>9</Quantity>
<Discount>0</Discount>
</Order_Detail>
<Order_Detail>
<ProductID>51</ProductID>
<UnitPrice>42.4000</UnitPrice>
<Quantity>40</Quantity>
<Discount>0</Discount>
</Order_Detail>
</Order>
<Order ID="10250">
<Order_Detail>
<ProductID>41</ProductID>
<UnitPrice>7.7000</UnitPrice>
<Quantity>10</Quantity>
<Discount>0</Discount>
</Order_Detail>
<Order_Detail>
<ProductID>51</ProductID>
<UnitPrice>42.4000</UnitPrice>
<Quantity>35</Quantity>
<Discount>0.15</Discount>
</Order_Detail>
<Order_Detail>
<ProductID>65</ProductID>
<UnitPrice>16.8000</UnitPrice>
<Quantity>15</Quantity>
<Discount>0.15</Discount>
</Order_Detail>
</Order>
<Order ID="10251">
<Order_Detail>
<ProductID>22</ProductID>
<UnitPrice>16.8000</UnitPrice>
<Quantity>6</Quantity>
<Discount>0.05</Discount>
</Order_Detail>
<Order_Detail>
<ProductID>57</ProductID>
<UnitPrice>15.6000</UnitPrice>
<Quantity>15</Quantity>
<Discount>0.05</Discount>
</Order_Detail>
</Order>
</Orders>
The solution comes in two parts. The first part is plain vanilla ADo.NET that selects some order detail items from the Northwind database and returns those items in a DataTable. DataTables can be used as a source for LINQ queries. The next part of the solution is a slightly longish LINQ query that demonstrates the functional construction. I won’t elaborate on the GetData function because many of you probably already know ADO.NET quite well. (You can just copy the code if you and to experiment with it. Note that the XML we are trying to produce is organized by all of the detail items that are part of a single order id, a grouping relationship. Listing 3 contains the complete solution. An explanation describing how the LINQ query works follows the listing.
Listing 3: Some ADO.NET that reads a table and uses LINQ and functional construction to produce an XML file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;
namespace SqlLinqXmlFuncConstruction
{
class Program
{
static void Main(string[] args)
{
DataTable orders = GetData();
XElement doc =
new XElement("Orders",
from book in orders.AsEnumerable()
group book by book.Field<int>("OrderID") into g
select
new XElement("Order",
new XAttribute("ID", g.Key),
from item in g
select new XElement("Order_Detail",
new XElement("ProductID",
item.Field<int>("ProductID")),
new XElement("UnitPrice",
item.Field<decimal>("UnitPrice")),
new XElement("Quantity",
item.Field<Int16>("Quantity")),
new XElement("Discount",
item.Field<float>("Discount"))
)));
doc.Save("..\\..\\Orders.xml");
Console.WriteLine(doc);
Console.ReadLine();
}
private static DataTable GetData()
{
string connectionString =
@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;" +
"Integrated Security=True";
string SQL = "SELECT TOP 10 * FROM [Order Details]";
DataTable data = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command =
new SqlCommand(SQL, connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);
}
return data;
}
}
}
The code we are interested in is defined in Main.
Main calls GetData and keeps local copy of the DataTable returned by that method. the next statement beginning with XElement doc = new XElement(“Orders” starts what is referred to as functional construction. That is, the XML will be constructed with function calls. Instead of of entirely literal calls to XElement and XAttribute the second argument to the XElement constructor is a LINQ query.
The statement from book in orders.AsEnumerable() defines the range variable book, which is like an iterator control variable. The clause in orders.AsEnumerable() uses an extension method to convert the DataTable to an EnumerableRowCollection<DataRow>, allowing LINQ to query the table data. The clause group book in book.Field<int>(“OrderID”) into g tells LINQ to group on ORDERID and store the results in g. Following the group by is the select. The select clause plays roughly the same role as a SQL select. In LINQ select establishes an iterator over the data in the from clause. The first select creates the <Order> element with the ID attribute for each order in the group g.
The inner from clause—from item in g—starts a new LINQ query, a nested query, defining the range variable as item from the collection of group items represented by g. Finally, the nested select returns the results of the functional construction method calls to XElement adding a sub-item <Order_Detail> for each item in the order and subordinate to the <Order_Detail> item are the actual details. Finally the XElement is saved as an XML file. (The XML declaration<?xml version=”1.0” encoding=”utf-8” ?>—is added automagically when you save the XElement (document). the XML is also written to the console for sanity checking.
Writing LINQ queries like the one in listing 3 take a little practice but you can get a lot of work done with a relatively little bit of code, and, everything you learn about LINQ can be used with SQL, XML, XSD, Entities and so much more. LINQ is a learn-once-leverage-many-times kind of tool and is being routinely extended into many existing technology areas like SharePoint and Active Directory.
Got to go back to the book—Professional DevExpress ASP.NET Controls—it doesn’t seem to be writing itself. Of course, the best thing about all of this .NET goodness is that you can use it with Developer Express’ ASP.NET and WinForms controls.