Share via

Power of LINQ!

  • Article

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</Meaning>
<Synonyms />
<Antonyms />
<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
https://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.

Comments