XMLTextWriter v/s XElement

I have to move some data from Database to a XML file. A very simple task, write a SQL query, get the result and put it into a XML File. So here is my data query

DownloadDataContext db = new DownloadDataContext();
IQueryable downloads =
      from f in db.Files
      join fr in db.FamilyReleases
          on f.ReleaseID equals fr.ReleaseID 
      join fdl in  db.FamilyDetailsByLocales
          on fr.FamilyID equals fdl.FamilyID
              select new DownloadDetails{
                FamilyID = fdl.FamilyID,
                FileName = f.FileName,
                FileSize = f.FileSize, URL = f.URL
)

Once I got the data in IQueryable downloads, Out of old habit, I proceeded to make the XML as follows:

MemoryStream ms = new MemoryStream();
using (XmlTextWriter w = new XmlTextWriter( ms, Encoding.UTF8 )) {
     w.Formatting = Formatting.Indented;
     w.WriteStartElement("Products");
           foreach (DownloadDetails d in downloads) { 
          w.WriteStartElement("FamilyID"); w.WriteValue(d.FamilyID.ToString());                                

w.WriteEndElement(); w.WriteStartElement("FileName"); w.WriteValue(d.FileName ); w.WriteEndElement(); w.WriteStartElement("FileSize"); w.WriteValue(d.FileSize); w.WriteEndElement(); w.WriteStartElement("URL"); w.WriteValue(d.URL); w.WriteEndElement(); }
w.WriteEndElement(); //products
}
StreamReader r = new StreamReader(ms,Encoding.UTF8);
ms.Seek(0, SeekOrigin.Begin);
return r.ReadToEnd();

Very simple, However when I check the returned XML string,  it is always cut in between and a proper XML is not formed. As the data is localized there may be a possibility that there is some funny character came in between and stream reader ReadToEnd function got confused. Debugging with 40+ languages in data was a nightmare, and I had no clue why streamreader is not working as it is suppose to work. There were other problems too, If any of the values are null, TextWriter.WriteValue complain very loudly. So every where I have to put a check to see if the value is null or not. However, the XElement came to my rescue. XElement does take all the possible data types, and does not complain if any of the data type is null.

XElement x = new XElement("Products");
foreach (DownloadDetails d in downloads) {
x.Add(new XElement("Product",
new XElement("FamilyID", d.FamilyID.ToString()),
new XElement("FileName", d.FileName),
new XElement("FileSize", d.FileSize),
new XElement("URL", d.URL + ""))); }

XElement not only handles NULL gracefully but does not complain about any of the unknown characters which threw StreamReader off. Clearly XElement is a better choice to make  XML out of data, here is another example of XElement use.

ACCESS (MDB) to XML with LINQ

I had some data in Access DB and wanted to convert it to XML. Data in Access was in one table called ‘Words’, with the following columns: Word, Meaning, Synonyms, Antonyms, Example etc. I wanted to convert this data in XML as follows:

<Dictionary>
<Word Spelling="Munificent">
    <Meaning>very generous</Meaning>
    <Synonyms />
    <Antonyms />
    <Example />
  </Word>
  <Word Spelling="Anfractuosity">
    <Meaning>Twist or turn, A complicated or involved process. </Meaning>
    <Synonyms />
    <Antonyms />
    <Example> The anfractuosities of his intellect and temper. –Macaulay </Example>
  </Word>
</Dictionary>

LINQ made job to read the data and convert it to XML very easy, as you can see all this happen in one line.

using System;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Xml.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace Convert2XML
{
    class Program
    {
static void Main(string[] args)
        {
                  string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Vocabulary.mdb;Persist Security Info=false";
                  DataContext db = new DataContext(new OleDbConnection(ConnectionString)) ;
                  Table <words> tblwords =   db.GetTable<words>();
                  System.IO.File.WriteAllText("Data.XML",  
                            ( new XElement("Dictionary", from w in tblwords
                                                   select new XElement("Word" ,
                                                          new XAttribute("Spelling", w.Word),
                                                   new XElement("Meaning", w.Meaning),
                                                   new XElement("Synonyms", w.Synonyms),
                                                   new XElement("Antonyms", w.Antonyms),
                                                   new XElement("Example", w.Example)
                                                   )
                                           )
                             ).ToString()
                             , Encoding.UTF8);
        }

        [Table(Name = "words")]
        public class words
        {
            [Column]
            public string Word;
            [Column]
            public string Meaning;
            [Column]
            public string Synonyms;
            [Column]
            public string Antonyms;
            [Column]
            public int weight;
            [Column]
            public string Reference;
            [Column]
            public string Example;
            [Column]
            public string link;
        }
    }
}

If you don’t have OLEDB 12.0 then You can use the following connection string too

string ConnectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Vocabulary.mdb;Persist Security Info=True” ;

If you don’t have Microsoft.ACE.OLEDB.12.0 then get it from here

http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

These oledb providers are 32 bit, so make sure to compile your project for 32 bit on a 64 bit machine.
Error handling and comments are removed for the brevity.