LINQ to SQL and LINQ to XML in action: from database to XML

By Jacques Bron on Monday, September 15 2008

As promised in my last post, I am going to explain the technical part of my recent photoblog migration, which was also described on PixyBlog's website. I know that many users of OneReaction Photoblog (the software I used to run my photoblog) are not software developers. Thus, the following post might be a bit too technical for them. I apologize for that. Software developers (even those who don't care about photoblogs) who want to know more about LINQ to SQL and see some real code in action might be interested by this article.

In short, I'm going to fetch relational data from four SQL Server database tables and create an XML document that respects the specifications required by PixyBlog, my new photoblog software. Writing XML from database is not new task and previous version of the .NET Framework already had database and XML classes to do this job. What's new with LINQ is the simplicity, the compact code and the use of object classes to access database objects and relational data.

For this conversion, we are going to write a small console application with Microsoft Visual Studio 2008 and SQL Server 2005. You can also use the free editions of Visual Studio, Visual C# 2008 Express Edition, Visual Basic.NET 2008 Express Edition and SQL Server 2005 Express Edition. In order to use LINQ, you will need to download the latest version of the .Net Framework 3.5 SP1. I will provide the code in C# and in Visual Basic.NET. Interestingly, the VB.NET code uses a completely different syntax, which is more intuitive than its C# equivalent.

XML file specification

PixyBlog is able to import my JPEGs and all the data that my previous photoblog application had generated, but this data has to be in the form of an XML file with pre-defined fields. For those who don't know what XML is all about, it is a specification where people can define their own elements. XML is widely used for data exchange over the web and between non compatible systems. You will find more information about XML here and here.

Below is an example of the type of XML file we need to create. The definitions of the XML elements were given to me by rj from PixyBlog. This format was created initially for importing from Pixelpost to PixyBLog.

<?xml version="1.0" encoding="utf-8"?>
<Weblog>
   <Entries>
      <Entry>
         <Date>2006-07-23 21:43:32</Date>
         <Title>Forest</Title>
         <Publish>2006-07-23 21:43:32</Publish>
         <Update>2006-07-23 21:43:32</Update>
         <Text>This is the description of this entry</Text>
         <Galleries>
            <Gallery>Scenic</Gallery>
            <Gallery>Landscape</Gallery>
         </Galleries>
         <Images>
            <Image>
               <FileName>forest.jpg</FileName>
               <DateCreated>2006-07-23 21:43:32</DateCreated>
            </Image>
         </Images>
         <Comments>
            <Comment>
               <Datetime>2006-07-23 21:44:29</Datetime>
               <Text>This is a nice photo.</Text>
               <Name>John Doe</Name>
               <Url></Url>
               <Email></Email>
            </Comment>
            <Comment>
               <Datetime>2006-08-25-08:15:23</Datetime>
               <Text>I agree.</Text>
               <Name>Dave Smith</Name>
               <Url></Url>
               <Email></Email>
            </Comment>
         </Comments>
      </Entry>
   </Entries>
</Weblog>

The structure of the above XML document is hierarchical and easy to understand. We first define a weblog, with entries (only one entry is showed here) and then all the elements that describe this photoblog entry, like image(s),  gallerie(s) and comments.

Database schema

The database schema of OneReaction Photoblog is quite simple. We will need the four following database tables:

• OneReactionEntries: this is the table which contains the data about every entry, like date of creation, title, description, etc.
• OneReactionCategories: this table has information about the categories, which will map the galleries in PixyBlog.
• OneReactionCategoryEntryMappings: this table makes a link between the entries and categories tables. The tables are related by a many-to-many relationship. That means that a given entry can belong to several categories and that a given category can corresponds to several different entries.
• OneReactionComments: this table holds the data concerning the comments on the photoblog entries.

OneReaction photoblog software also uses other tables but we are not going to use them here, because PixyBlog ignores their data.

Here is a graphic representation of the tables we will work on (click on the image to see it at full size):

figure 1: database schema

Now that we have defined our starting and ending points, let's go further in the code and try understand how LINQ to SQL and LINK to XML can help us.

Using LINQ to SQL and LINQ to XML to generate our XML

LINK is a recent addition to the Microsoft developer's toolbox and it is used to query, read and modify content from databases, XML files and objects. LINQ to SQL stands for Language-Integrated Query for Relational Data. I am not going to give a primer explanation on LINQ, so if you feel that you need more knowledge on LINQ, I recommend that you read this great pdf book from Scott Guthrie at http://it-box.blogturk.net/2007/10/19/linq-to-sql-tutorial-series-by-scott-guthrie-pdf-book-format/. Another interesting source of information is this article from MSDN: http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic12. As stated at the beginning of this later document, "LINQ to SQL provides a run-time infrastructure for managing relational data as objects without losing the ability to query. It does this by translating language-integrated queries into SQL for execution by the database, and then translating the tabular results back into objects you define. Your application is then free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically".

This definition may sound complicated but once you have learned the new syntax (which is close to traditional SQL syntax) it is very easy to use it. In short, LINQ to SQL allows you to write C# or VB.NET code using object classes that represent your application data. This has several advantages over traditional database code because you will be able to use Intelllisense on your LINQ code and see all your database object classes. You will also get compile-time checkof database objects properties and methods, which helps writing error-free code.

Now let's open Visual Studio 2008 and create a new C# console application. Name this application "OneReactionConversion" and make sure that it uses the .NET Framework 3.5.

figure 2: new C# console application

Then add a new item to your application and select "LINQ to SQL classes". This will create a .dbml document that we will name OneReaction.dbml. This file represents a Datacontext class. It contains a layout file(.layout) and a code file (.cs). The layout file will hold the database schema as in figure 1. The code file will contain the C# code for our database objects.
In order to generate these files with our data, open the Server Explorer panel and create a new connection to your OneReaction SQL database. Expand the Tables node, select the four tables that we need: OneReactionEntries, OneReactionCategories, OneReactionCategoryEntryMappings, OneReactionComments and drag these tables onto the surface of the blank layout document, also called relational designer. After a couple of seconds, the beautiful graphic of figure 1 is displayed, with relations between the tables. During the creation of this document, the code file has also been generated and you can check it by opening OneReaction.designer.cs in the editor. You will see all the objects that map the database elements, like tables, columns and relations.

We have now all the elements in place to write our code. All the code that accesses the database objects (through LINQ) and the code that will generate the XML will fit in one single procedure that we call CreateXmlFromDatabase(). This code uses LINQ to SQL for accessing the database and also uses LINQ to XML to create the XML document:

   1:  private static void CreatXmlFromDatabase()

   2:  {

   3:    OneReactionDataContext db = new OneReactionDataContext();

   4:   

   5:    XElement xml = new XElement("Weblog", new XElement("Entries",

   6:    from entry in db.OneReactionEntries

   7:    select new XElement("Entry",

   8:    new XElement("Date", String.Format("{0:u}", entry.DateCreated).TrimEnd('Z')),

   9:    new XElement("Title", entry.Title),

  10:    new XElement("Publish", String.Format("{0:u}", entry.DateVisible).TrimEnd('Z')),

  11:    new XElement("Update", String.Format("{0:u}", entry.DateModified).TrimEnd('Z')),

  12:    new XElement("Text", entry.Text),

  13:    new XElement("Galleries",

  14:    from categoriesMappings in entry.OneReactionCategoryEntryMappings

  15:    select new XElement("Gallery", categoriesMappings.OneReactionCategories.Title)),

  16:    new XElement("Images",

  17:    new XElement("Image",

  18:    new XElement("FileName", entry.OriginalImageName),

  19:    new XElement("DateCreated", String.Format("{0:u}", entry.DateTaken).TrimEnd('Z')))),

  20:    new XElement("Comments",

  21:    from comment in entry.OneReactionComments

  22:    select new XElement("Comment",

  23:    new XElement("DateTime", String.Format("{0:u}", comment.DateCreated).TrimEnd('Z')),

  24:    new XElement("Text", comment.Text),

  25:    new XElement("Name", comment.Name),

  26:    new XElement("Url", comment.Url),

  27:    new XElement("Email", comment.Email)

  28:    )))));

  29:   

  30:    xml.Save("c:\\temp\\OneReaction.xml");

  31:    Process.Start("c:\\temp\\OneReaction.xml");

  32:  }

Now let's see the important parts of this code:

OneReactionDataContext db = new OneReactionDataContext();

Here we create a DataContext object that holds all the tables we need. The OneReactionDataContext class is a strongly typed class that has been automatically created after we dragged the tables onto the .dbml file.

The next statement beginning with "XElement" is very long and is split into several lines to be more readable. We are creating an XElement object that holds all the underlying XML tags we need. By embedding other XElements inside this "master" XElement, we create the hierarchy of the XML tags within our document. The first argument of XElement() is the string that holds the XML tag. The order in which these strings appear successively matches the structure of the XML document: "Weblog", then "Entries", "Entry", "Date", etc. The second argument of XElement() is the data value itself, that we access using the local variable "entry" that we created in the select statement. As you can see, we are using some data-related code which is close to the SQL code we're used to (at least for those who have worked with databases before): "SELECT ...FROM...". Because there are foreign key relationships defined between our tables, their hierarchy is reflected in the object model. You can access the related records data simply by specifying the child table, or from a child table, the parent table to access relational data.reflected . As you can see in the above code, we have accessed relational data with foreign-key relationships without writing any Join statement explicitly.

The above code creates an XML document in-memory. We need to save it on the disk and then we load the default browser to see the result. That's what the following lines of code do:

xml.Save("c:\\temp\\OneReaction.xml");  

Process.Start("c:\\temp\\OneReaction.xml");

That's it! In just a few lines of code, we have accessed a four-tables data schema and created a XML document.
The hard part is the XElement creation statement, which is a bit confusing because of the hierarchy of XElements inside it. I suggest that you read the basic examples provided in the articles cited earlier, in order to get familiar with this syntax. Once this is done, you wil be able to write almost any XML document from a database!

VB.NET is even simpler and more intuitive

Now that you have a fair understanding of the syntax and that you know how to use LINQ to SQL and LINQ to XML in C#, let's see what VB.NET can do for us. The VB.NET syntax is simpler than the above C# code and it is also very efficient. In VB.NET you can write the XML elements directly and insert LINQ to SQL statements within the XML tags. Here is our CreateXmlFromDatabase() procedure in VB.NET:

   1:  Sub CreateXmlFromDatabase()

   2:        Dim db As New OneReactionDataContext

   3:        Dim entries = <Weblog>

   4:                         <Entries>

   5:                            <%= From entry In db.OneReactionEntries _

   6:                               Select <Entry>

   7:                                         <Date><%= String.Format("{0:u}", entry.DateCreated).Trim("Z") %></Date>

   8:                                         <Title><%= entry.Title %></Title>

   9:                                         <Publish><%= String.Format("{0:u}", entry.DateVisible).Trim("Z") %></Publish>

  10:                                         <Update><%= String.Format("{0:u}", entry.DateModified).Trim("Z") %></Update>

  11:                                         <Text><%= entry.Text %></Text>

  12:                                         <Galleries>

  13:                                            <%= From categoriesMappings In entry.OneReactionCategoryEntryMappings _

  14:                                               Select <Gallery><%= categoriesMappings.OneReactionCategories.Title %></Gallery> %>

  15:                                         </Galleries>

  16:                                         <Images>

  17:                                            <Image>

  18:                                               <FileName><%= entry.OriginalImageName %></FileName>

  19:                                               <DateCreated><%= String.Format("{0:u}", entry.DateTaken).Trim("Z") %></DateCreated>

  20:                                            </Image>

  21:                                         </Images>

  22:                                         <Comments>

  23:                                            <%= From comments In entry.OneReactionComments _

  24:                                               Select <Comment>

  25:                                                         <Datetime><%= comments.DateCreated %></Datetime>

  26:                                                         <Text><%= comments.Text %></Text>

  27:                                                         <Name><%= comments.Name %></Name>

  28:                                                         <Url><%= comments.Url %></Url>

  29:                                                         <Email><%= comments.Email %></Email>

  30:                                                      </Comment> %>

  31:   

  32:                                         </Comments>

  33:                                      </Entry> %>

  34:                         </Entries>

  35:                      </Weblog>

  36:   

  37:        entries.Save("c:\temp\onereactionvb.xml")

  38:        Process.Start("c:\temp\onereactionvb.xml")

  39:     End Sub

Isn't this sweet? I personally usually write code in C# but this VB.NET syntax is easier to read and easier to implement.

To compare with more traditional XML from database coding, you can have a look at the PHP code that PixyBlog provides for those who want to convert from PixelPost (a popular photoblog application) to PixyBlog. Here is the databse to XML part:

   1:      $query = "SELECT * FROM ".$pixelpost_db_prefix."pixelpost";

   2:      $sql = mysql_query($query) or die("db error");

   3:      $num = mysql_num_rows($sql);

   4:   

   5:      if ($num != 0) {    

   6:          $file= fopen($xmlfilename, "w");    

   7:          $_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";

   8:          $_xml .= "<Weblog>\r\n";

   9:          $_xml .= "<Entries>\r\n";

  10:      }

  11:   

  12:    while($row = mysql_fetch_array($sql)) {

  13:   

  14:      $id = $row['id'];

  15:   

  16:      $_xml .= "<Entry>\r\n";

  17:      $_xml .= "\t<Date>".$row['datetime']."</Date>\r\n";

  18:      $_xml .= "\t<Title>".htmlspecialchars($row['headline'])."</Title>\r\n";

  19:      $_xml .= "\t<Publish>".$row['datetime']."</Publish>\r\n";

  20:      $_xml .= "\t<Update></Update>\r\n";

  21:      $_xml .= "\t<Text>".htmlspecialchars($row['body'])."</Text>\r\n";

  22:   

  23:      // get Categories

  24:      $category_list = mysql_query("SELECT t2.name FROM ".$pixelpost_db_prefix."catassoc t1 INNER JOIN ".$pixelpost_db_prefix."categories t2 ON t1.cat_id = t2.id WHERE t1.image_id = '$id' ORDER BY t2.name ");

  25:      $num = mysql_num_rows($category_list);

  26:      if ($num != 0) $_xml .= "\t<Galleries>\r\n";

  27:      while(list($category_name) = mysql_fetch_row($category_list)) {

  28:            $category_name = pullout($category_name);

  29:          $_xml .= "\t\t<Gallery>".htmlspecialchars($category_name)."</Gallery>\r\n";

  30:      }

  31:      if ($num != 0) $_xml .= "\t</Galleries>\r\n";

  32:   

  33:      // get Tags

  34:      if ($cfgrow['altlangfile'] != 'Off') $alt = "alt_";

  35:      else $alt = "";

  36:      $sql_tag = "SELECT ".$alt."tag FROM " . $pixelpost_db_prefix . "tags WHERE img_id = " . $id . " AND ".$alt."tag NOT LIKE '' ORDER BY ".$alt."tag ASC";

  37:      $tag_list = mysql_query($sql_tag);

  38:      

  39:      if (mysql_error()) {

  40:          //$addon_description .= "<br/>this pixelpost version does not support TAGS yet.";

  41:      } else {

  42:          if ($num != 0) $_xml .= "\t<Tags>\r\n";

  43:          while(list($tag) = mysql_fetch_row($tag_list)) {

  44:              $_xml .= "\t\t<Tag>".htmlspecialchars($tag)."</Tag>\r\n";

  45:          }

  46:          if ($num != 0) $_xml .= "\t</Tags>\r\n";

  47:      }

  48:   

  49:      $_xml .= "\t<Images>\r\n";

  50:      $_xml .= "\t\t<Image>\r\n";

  51:      $_xml .= "\t\t<FileName>".htmlspecialchars($row['image'])."</FileName>\r\n";

  52:      $_xml .= "\t\t<DateCreated>".$row['datetime']."</DateCreated>\r\n";

  53:      $_xml .= "\t\t</Image>\r\n";

  54:      $_xml .= "\t</Images>\r\n";

  55:      

  56:      // get Comments

  57:          $comment_list = mysql_query("SELECT * FROM ".$pixelpost_db_prefix."comments WHERE parent_id = '$id' AND publish = 'yes' ORDER BY datetime");

  58:      $num = mysql_num_rows($category_list);

  59:      if ($num != 0) $_xml .= "\t<Comments>\r\n";

  60:      while($row = mysql_fetch_array($comment_list)) {

  61:          $_xml .= "\t<Comment>\r\n";

  62:          $_xml .= "\t\t<Datetime>".$row['datetime']."</Datetime>\r\n";

  63:          $_xml .= "\t\t<Text>".htmlspecialchars($row['message'])."</Text>\r\n";

  64:          $_xml .= "\t\t<Name>".htmlspecialchars($row['name'])."</Name>\r\n";

  65:          $_xml .= "\t\t<Url>".htmlspecialchars($row['url'])."</Url>\r\n";

  66:          $_xml .= "\t\t<Email>".htmlspecialchars($row['email'])."</Email>\r\n";

  67:          $_xml .= "\t</Comment>\r\n";

  68:      }

  69:      if ($num != 0) $_xml .= "\t</Comments>\r\n";

  70:   

  71:      $_xml .= "</Entry>\r\n";

  72:   

  73:      //$_xml .="\t<page title=\"" . $cfgrow['imagepath'].$row['image'] . "\">\r\n";

  74:      //$addon_description.= "<font color=\"blue\">Reading Image ".$cfgrow['imagepath'].$row['image'].".</font><br />";

  75:      $imgcount += 1;

  76:    }

  77:      $_xml .= "</Entries>\r\n</Weblog>";

  78:      fwrite($file, $_xml);

  79:       fclose($file);

  80:      

  81:      $addon_description.= "<font color=\"blue\">".$imgcount." Images export.</font><br />";

  82:      $addon_description.= "XML has been written. <a href=\"$xmlfilename\">Download the xml.</a>";

  83:      

In C#, you need to write 32 lines of code. In VB.NET, 39 lines. In PHP, 82 lines.  Still want to use classic database code?

I hope this post has helped you understand what are LINQ to SQL and LINQ to XML. For those who want to import their data from OneReaction photoblog, they will probably need to get a local copy of their SQL Server database. This is because LINQ to SQL might not be able to access their remote SQL Server, depending on connection protocols that are enabled on this server.

This example of LINQ to SQL and LINQ to XML code can also be the starting point for any type of database to XML conversion using this new and powerful LINQ technology.