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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s