Chapter 22
Combining XML with Other Data Models
In This Chapter
Creating XML from SQL Data
Updating SQL Data from XML
“I think you end up doing the stuff you were supposed to do at the time you were supposed to do it.”
—Robert Downey, Jr.
In a very simple but real sense, software development is about moving data around in a digital world that exists at the atomic level and mostly in our minds. Software development is about moving data around and the rules for how, when, where, and why the data moves. That doesn’t sound so hard, but, as it turns out, creating software is very hard.
Creating software is hard because software is purely an invention of minds. Almost everything that really happens—if happens is the right word—in software happens in the gray matter of the brain. Possibly the fact that no one is really sure how the mind works—except Stephen Pinker—explains why creating software is hard, because software is purely mind-stuff.
This chapter is about moving data from place to place and how you can do that with Language INtegrated Query (LINQ). Because this part of the book is about Extensible Markup Language (XML), this chapter shows you how to create XML from Structured Query Language (SQL) data and how to update a SQL database from XML data, all accomplished by using LINQ to XML and LINQ to SQL.
Creating XML from SQL Data
Every custom bit of software is purely a new invention in the world. Programmers are really inventors. This is true because otherwise you would just grab someone else’s invention and use it rather than roll your own. Invention is a messy business.
In one of my favorite movies, the character Ben Gates played by actor Nicholas Cage paraphrases Thomas Edison when referring to inventing the light bulb: “I didn’t fail. I found 2,000 ways how not to make a light bulb.” Unfortunately, in software you seldom get more than one or two tries, but you still have to find the right way. Patterns and practices and frameworks are actually instances where someone believes they have found the right way (not the only way but a right way).
This section shows you one right way to move data easily out of a SQL database and into an XML form. This is useful because SQL data doesn’t travel across wide area networks well but XML, because it’s self-describing and text, does travel well. Hence, the scenarios supporting the need to move between an open standard, XML, and a proprietary technology, SQL databases, will occur frequently.
To summarize, in this section, you use LINQ to SQL to get data from a SQL database and LINQ to XML to convert the data from an object-relational mapping (ORM) entity to an XML document. (You can also accomplish a similar thing by using a DataSet and the serialization capabilities of a DataSet. The difference between this technique and DataSet serialization is that LINQ to XML supports functional construction and allows you to reorganize the XML tree. With DataSet serialization, you essentially still have the same DataSet.)
Defining the Object-Relational Map
Chapter 13, “Querying Relational Data with LINQ,” went into a lot of depth about defining object relational maps for LINQ to SQL, so that information is not repeated here. However, before you see the code for the example, let’s take a moment to talk about strategy.
Having worked all over the Western Hemisphere, I have witnessed a lot of talk about standards, procedures, processes, and similar such folderol. I am not saying that this dialogue is not important—what I am saying is that on a project, where the rubber is meeting the road, is the wrong place and time to have such discussions. A key to success on an actual project is to put a specialist in each role, someone who already knows how to do the work that needs to be done. For example, when you need requirements, put someone who is very good at getting requirements in that role. Next, it is essential that that person have local authority sufficient to make a judgment call about how that role is played and the team provide the forgiveness to allow course corrections. These three aspects are paramount: highly specialized role, role authority, and acceptance that things happen in situ that will need to be corrected.
Specialization and local authority are important because it permits the expert—not the armchair quarterback—the latitude to do what is necessary and sufficient to achieve the end.
How this applies here is that LINQ to SQL supports complicated ORM maps that specify keys, nulls, database type maps, and additional information, but you don’t always need to add all of the details to an ORM that you can. In fact, it might be counterproductive to do so because details consume time and, sometimes, because the more detail your ORM has the more you will have to change entities if your database schema changes. In the example in Listing 22.1, you just don’t need a lot of the details.
For our purposes, the code in Listing 22.1 maps the Northwind Customers table to an entity. The remaining subsections use that entity and LINQ to XML to convert the entity to an XML document.
Listing 22.1 A Very Basic ORM Mapping of the Northwind Customers Table, Mapped Minimally as a Matter of Discretion Based on Sufficient Need
[Table(Name=“Customers”)]
public class Customer
{
[Column()]
public string CustomerID{ get; set; }
[Column()]
public string CompanyName{ get; set; }
[Column()]
public string ContactName{ get; set; }
[Column()]
public string ContactTitle{ get; set; }
[Column()]
public string Address{ get; set; }
[Column()]
public string City{ get; set; }
[Column()]
public string Region{ get; set; }
[Column()]
public string PostalCode{ get; set; }
[Column()]
public string Country{ get; set; }
[Column()]
public string Phone{ get; set; }
[Column()]
public string Fax{ get; set; }
}
Listing 22.1 uses a bare-bones mapped entity. Notice automatic properties and empty ColumnAttributes are used. In this case, both of these choices are sufficient for the problem at hand. For example, you can use a no-parameters approach to the ColumnAttribute because the property names match the column names, and you can use automatic properties because there is no business logic. If you need business logic, automatic properties won’t work.
Listing 22.2 contains the custom DataContext. In practice, the difference you might want to make is to put the connection string in the App.config file and perhaps encrypt it. At least those are the choices I would make in practice.
Listing 22.2 A Typed DataContext Containing a Customers Property, Making It Easy to Get the Customer Data
public class Northwind : DataContext
{
private static readonly string connectionString =
“Data Source=.\SQLEXPRESS;AttachDbFilename=” +
“”C:\Books\Sams\LINQ\Northwind\northwnd.mdf“”+
“;Integrated Security=True;Connect Timeout=30;User Instance=True”;
public Northwind() : base(connectionString){}
public Table<Customer> Customers
{
get{ return this.GetTable<Customer>(); }
}
}
In your code, remember to change your connection string. Because this book was written in part on my laptop (in hotels) and in part on my workstation at home (between games of Warcraft III and Grand Theft Auto IV, near the end) different instances of the Northwind database were used.
Constructing the XML Document from the SQL Data
Assuming you put the code in Listings 22.1 and 22.2 in a console application, you are now ready to use LINQ to XML and functional construction in the projection clause—the select clause with new—of the LINQ to query and generate the XML.
You learned about functional construction in Chapter 20, “Constructing XML from Non-XML Data,” so that information is not repeated here. Listing 23.3 orchestrates the two technologies—LINQ to SQL and LINQ to XML—to convert SQL data to XML data. Listings 22.1 and 22.2 support the LINQ to SQL and Listing 22.3 shows a Main function that combines the two technologies. Listing 22.4 shows the output XML document.
Listing 22.3 A Console Application That Uses LINQ to SQL and LINQ to XML
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Xml.Linq;
using System.Data.Linq.Mapping;
namespace CreatingXMLFromSQLData
{
class Program
{
static void Main(string[] args)
{
Northwind northwind = new Northwind();
XDocument xml = new XDocument(
new XComment(“First 3 Customers Starting with ‘C’”),
new XElement(“Customers”,
(from cust in northwind.Customers
where cust.CompanyName.StartsWith(“C”)
orderby cust.CompanyName
select new XElement(“Customer”,
new XAttribute(“CustomerID”, cust.CustomerID),
cust.CompanyName,
new XElement(“ContactName”,
new XAttribute(“ContactTitle”, cust.ContactTitle),
cust.ContactName),
new XElement(“Address”,
new XElement(“Street”, cust.Address),
new XElement(“City”, cust.City),
new XElement(“Region”, cust.Region),
new XElement(“PostalCode”, cust.PostalCode),
new XElement(“Country”, cust.Country)),
new XElement(“Phones”,
new XElement(“Phone”,
new XAttribute(“Type”, “Primary”),
cust.Phone),
new XElement(“Phone”,
new XAttribute(“Type”, “Fax”),
cust.Fax)))).Take(3)));
Console.WriteLine(xml.ToString());
Console.ReadLine();
}
}
}
Listing 22.4 The Generated XML Document from the Code in Listings 22.1, 22.2, and 22.3
<!—First 3 Customers Starting with ‘C’—>
<Customers>
<Customer CustomerID=“CACTU”>
Cactus Comidas para llevar
<ContactName ContactTitle=“Sales Agent”>Patricio Simpson</ContactName>
<Address>
<Street>Cerrito 333</Street>
<City>Buenos Aires</City>
<Region></Region>
<PostalCode>1010</PostalCode>
<Country>Argentina</Country>
</Address>
<Phones>
<Phone Type=“Primary”>(1) 135-5555</Phone>
<Phone Type=“Fax”>(1) 135-4892</Phone>
</Phones>
</Customer>
<Customer CustomerID=“CENTC”>Centro comercial Moctezuma
<ContactName ContactTitle=“Marketing Manager”>Francisco
Chang</ContactName>
<Address>
<Street>Sierras de Granada 9993</Street>
<City>México D.F.</City>
<Region></Region>
<PostalCode>05022</PostalCode>
<Country>Mexico</Country>
</Address>
<Phones>
<Phone Type=“Primary”>(5) 555-3392</Phone>
<Phone Type=“Fax”>(5) 555-7293</Phone>
</Phones>
</Customer>
<Customer CustomerID=“CHOPS”>
Chop-suey Chinese
<ContactName ContactTitle=“Owner”>Yang Wang</ContactName>
<Address>
<Street>Hauptstr. 29</Street>
<City>Bern</City>
<Region></Region>
<PostalCode>3012</PostalCode>
<Country>Switzerland</Country>
</Address>
<Phones>
<Phone Type=“Primary”>0452-076545</Phone>
<Phone Type=“Fax”></Phone>
</Phones>
</Customer>
</Customers>
Notice three things about Listing 22.3. The first is that the projection clause changed the organization of the Customer object for the XML document. The second is that an aggregate method—Take—was used to limit the size of the resultset, and the third is that the XComment node type was introduced.
Using the XComment Node Type
LINQ to XML classes like XComment inherit from XNode or XContainer. Similar to classes in the CodeDOM namespace, classes like XDocument, XElement, XComment, and XCData represent objects in a tree graph that will each emit the corresponding element. XComment is used to write properly formatted comments to an XML document.
Displaying the XML Document in a TreeView
There are practical uses for SQL to XML to SQL scenarios. A neat one that I read about on the Internet was to support bulk loading of Active Directory from an XML file using LINQ to XML. If you combine what you learned about implementing an IQueryProvider for Active Directory in Chapter 12, “Querying Outlook and Active Directory,” with the LINQ to XML information in this part of the book, it shouldn’t be much of a stretch to implement such a bulk loader.
The scenario in this section simply gets SQL from a database, converts it to a flat XML tree, and uses that tree to create a simple web page with a TreeView of the selected customers. Listing 22.5 depends on the same Customer entity class and Northwind DataContext from Listings 22.1 and 22.2 to get the SQL data. Listing 22.5 is a new method, GetData. GetData can be used to replace the LINQ code in Listing 22.3. GetData yields a flatter construction of the XML document than the LINQ query in the Main function in Listing 22.3.
Listing 22.5 shows the implementation of the GetData method with a query that defines an XML document with XElements for each column in the Customers table.
Listing 22.5 GetData Can Be Placed in a Separate Library with Listings 22.1 and 22.2; This Listing Returns an XDocument Object Representing the Constructed XML Document
// simpler xml function construction
public static XDocument GetData()
{
Northwind northwind = new Northwind();
XDocument xml = new XDocument(
new XComment(“First 3 Customers Starting with ‘C’”),
new XElement(“Customers”,
(from cust in northwind.Customers
where cust.CompanyName.StartsWith(“C”)
orderby cust.CompanyName
select new XElement(“Customer”,
new XElement(“CustomerID”, cust.CustomerID),
new XElement(“CompanyName”, cust.CompanyName),
new XElement(“ContactTitle”, cust.ContactTitle),
new XElement(“ContactName”, cust.ContactName),
new XElement(“Address”, cust.Address),
new XElement(“City”, cust.City),
new XElement(“Region”, cust.Region),
new XElement(“PostalCode”, cust.PostalCode),
new XElement(“Country”, cust.Country),
new XElement(“Phone”, cust.Phone),
new XElement(“Fax”, cust.Fax))).Take(3)));
return xml;
}
To create the web page, simply create an ASP.NET website in Microsoft Visual Studio. Place a TreeView on the page and bind the XDocument as a string—call ToString—to an XmlDataSource. Use the XmlDataSource as the DataSource for the TreeView. You will need to map the TreeNodeBinding’s DataMember and TextField properties for each of the nodes that you want to see in the tree. The foreach statement shows you how to do that (see Listing 22.6). (Figure 22.1 shows the very basic output from Listing 22.6.)
Figure 22.1 The very simple TreeView output from the XML document.
Listing 22.6 Showing the Code-Behind for the Web Page
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml.XPath;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
XDocument doc = XmlData.GetData();
XmlDataSource1.Data = doc.ToString();
foreach (XElement elem in doc.Element(“Customers”).Elements(“Customer”).
Elements())
{
TreeNodeBinding binding = new TreeNodeBinding();
binding.DataMember = elem.Name.ToString();
binding.TextField = “#InnerText”;
TreeView1.DataBindings.Add(binding);
}
TreeView1.DataSource = XmlDataSource1;
TreeView1.DataBind();
}
}
}
Direct support for binding right to WinForm DataGridViews and TreeViews is iffy. It would be nice if the XDocument or XElement could be bound to these controls without a lot of code, but a thorough search of the web makes it seem as if this problem hasn’t been completely resolved. Some legwork is required, but Chapter 23, “LINQ to XSD Supports Typed XML Programming,”—the last chapter—explores LINQ to XSD and how you can use schemas to help with some of the extra work involved.
Updating SQL Data from XML
Now let’s go in the other direction. Suppose you have some XML data and you want to send it to your database. To do this, you use the LINQ technologies in reverse: Use LINQ to XML to walk the XML nodes and LINQ to SQL to update the database.
Listing 22.7 offers a sample XML file that contains a reference to Okemos’ eclectic international tuba restaurant, which is a lot of fun if you are in touch with your inner hipster and the food is good, and one of Okemos’ first-class dining spots, Dusty’s Cellar.
Listing 22.7 A Sample XML File Containing Some New Customers for the Northwind Traders Database
<?xml version=“1.0” encoding=“utf-8” ?>
<Customers>
<Customer>
<CustomerID>TRAVL</CustomerID>
<CompanyName>Travelers Club Rest. and Tuba Museum</CompanyName>
<ContactTitle></ContactTitle>
<ContactName>William White</ContactName>
<Address>2138 Hamilton</Address>
<City>Okemos</City>
<Region>MI</Region>
<PostalCode>48864</PostalCode>
<Country>USA</Country>
<Phone>517-349-1701</Phone>
<Fax></Fax>
</Customer>
<Customer>
<CustomerID>DUSTY</CustomerID>
<CompanyName>Dusty’s Cellar</CompanyName>
<ContactTitle></ContactTitle>
<ContactName>Matt Rhodes</ContactName>
<Address>1839 Grand River Avenue</Address>
<City>Okemos</City>
<Region>MI</Region>
<PostalCode>48864</PostalCode>
<Country>USA</Country>
<Phone>1 517 349-5150</Phone>
<Fax>1 517 349-8416</Fax>
</Customer>
</Customers>
In this scenario, both restaurants have earned a place in the Northwind database. To get the customers in, you can use the Customer ORM from Listing 22.1 and the DataContext from Listing 22.2. In Listing 22.7, the XElement class loads the XML document (Listing 22.6). A Customer object is instantiated and a foreach loop walks the elements initializing the properties of the Customer object. At the end of the loop, LINQ to SQL is used to insert the new object and submit the changes.
[Column(IsPrimaryKey=true)]
public string CustomerID { get; set; }
Listing 22.8 Loading the XML Document, Walking the Nodes with a foreach Loop Updating the Entity Object, Customer, and Inserting the Customer Using the DataContext and LINQ to SQL
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Data.Linq;
using System.Xml.Linq;
using System.Data.Linq.Mapping;
namespace UpdateSQLFromXML
{
class Program
{
static void Main(string[] args)
{
XElement xml = XElement.Load(“..\..\DataToUpdate.xml”);
Northwind northwind = new Northwind();
foreach (XElement elem in xml.Elements(“Customer”))
{
Customer cust = new Customer();
cust.CustomerID = elem.Element(“CustomerID”).Value;
cust.CompanyName = elem.Element(“CompanyName”).Value;
cust.ContactTitle = elem.Element(“ContactTitle”).Value;
cust.ContactName = elem.Element(“ContactName”).Value;
cust.Address = elem.Element(“Address”).Value;
cust.City = elem.Element(“City”).Value;
cust.Region = elem.Element(“Region”).Value;
cust.PostalCode = elem.Element(“PostalCode”).Value;
cust.Country = elem.Element(“Country”).Value;
cust.Phone = elem.Element(“Phone”).Value;
cust.Fax = elem.Element(“Fax”).Value;
northwind.Customers.InsertOnSubmit(cust);
northwind.SubmitChanges();
}
}
To verify that the data was inserted, you can execute the following SQL statement in Visual Studio (see Listing 22.9). To execute a SQL statement in Visual Studio, follow these steps:
1. Expand the Server Explorer.
2. Expand the Data Connections node, Database node, and Tables node each in turn. (In this example, theDatabase node will be the Northwind data connection.)
3. Right-click on the Customers table and select New Query.
4. In the Add Table dialog box, add the Customers table and close the dialog box.
5. Enter the SQL text in the SQL pane.
6. Click the Execute button (the exclamation icon) or press Ctrl+R.
You also have the option of using the designer to builder the query, using SQL Server Management tools, or the osql.exe or sqlcmd.exe command-line tools. Figure 22.2 shows the output from the query in Listing 22.9.
Figure 22.2 The results from the SQL query in Listing 22.9, after you have run the insertion code from Listing 22.8.
Listing 22.9 A SQL Query to Examine the Inserted Data from Listing 22.8
SELECT
CustomerID, CompanyName, ContactName, ContactTitle, Address, City,
Region, PostalCode, Country, Phone, Fax
FROM
Customers
WHERE
(CustomerID = ‘DUSTY’) OR (CustomerID = ‘TRAVL’)
To use the osql.exe command line, follow these steps:
1. Make sure that Authenticated Users have full access to the drive containing the Northwind database. (Right-click on the folder, select Properties, select the Security tab, select Authenticated Users, and then select Edit. Change control to Full Control; see Figure 22.3.)
Figure 22.3 Modified Northwind properties folder permission settings, permitting osql.exe command-line interaction.
2. At the command prompt, type osql -E -S .SQLExpress and press Enter.
3. At the 1> prompt, type sp_attach_db “northwind”, “[path]Northwind.mdf” <enter>.
4. Type go <enter>.
5. Type use northwind <enter>.
6. Type go <enter>.
7. Type SELECT * FROM Customers WHERE CustomerID = ‘DUSTY’ OR CustomerID = ‘TRAVL’ <enter>.
8. Type go <enter>.
After the last steps, you should see the output from the database showing you the two inserted records. If you want to generate a script with inputs and outputs, change step 2 to include the –o [outputfile] –e (echo command) as follows:
osql –E –S .SQLExpress –o script.txt –e
And type quit after the last command. Listing 22.10 shows the output from the script file (named script.txt).
Listing 22.10 The Output from the osql Scripting Session
1> 2> use northwind
1> 2> select * from customers where customerid = ‘DUSTY’ OR customerID = ‘TRAVL’
CustomerID CompanyName
ContactName ContactTitle
Address
City Region PostalCode Country
Phone Fax
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DUSTY Dusty’s Cellar
Matt Rhodes
1839 Grand River Avenue
Okemos MI 48864 USA
1 517 349-5150 1 517 349-8416
TRAVL Travelers Club Rest. and Tuba Museum
William White
2138 Hamilton
Okemos MI 48864 USA
517-349-1701
(2 rows affected)
1>
Summary
As the last chapter of this book approaches, you will forgive me if I get a bit sentimental. A book like this takes me (now at 42) several months and a thousand hours or more to research and write. Because I am sentimental, I throw in things that you must know like LINQ to SQL to LINQ to XML and things I think you’d like to know like Universal Data Links (.udl file) tips and osql.exe command-line sessions, along with the movie references and analogies. These exist in the text in an attempt to make the journey both informative and enjoyable. That is, I hope you will read the book rather than Google through it.
After 20 years of experience, I occasionally still am subjected to technical interviews, nuts-and-bolts stuff, for development work. Perhaps this happens because some (a few) people think that writers write and don’t do. In my case, my time is spent writing, coding, or modeling 40 to 60 hours per week developing software—up until two years ago closer to 80—and as many as 40 more on writing books like this one.
My books are written for two audiences. The first one is for me. There is no way one person can have 100% recall on all of this material at all times. For example, I had to look up the sp_attach_db stored procedure, but I remember that the IDTExtensibility2 interface is for writing Visual Studio wizards. And, the second audience is you. From the many hundreds of encouraging emails and sometimes gifts—$100 in Denny’s coupons, smoked fish from Norway, and a free dinner in St. Louis—its obvious that we are in this together.
Perhaps given a photographic memory, there would be less need to spend weekends, holidays, and family gatherings writing. But, I have to keep learning too, and writing is a constructive way to do it. So, in part, selfishly this—my umpteenth book—is sort of like an extension of or overflow from my brain, like Dumbledore’s pen sieve, and in part I hope you will actually enjoy the journey with me.
So to borrow from my friend Jackson Wayfare, “brevity does not mean inconsequence,” but a whole lot of palaver won’t get us to the end of this book.