.NET Information center: Converting an Access DB to XML using C#

Monday, July 26, 2010

Converting an Access DB to XML using C#

I recently needed to import an Access database into a C# program for a sample that I will be blogging about soon. My objective was to convert the data into a more convenient form for use with my “real” application. Nothing here will be very cutting edge! A quick and dirty way to get the job done seemed to be to read the data into a DataSet and export it to XML. Access MDB files can be read using the Jet OLDB provider with OleDbConnection. Once the connection is established, the GetOleDbSchemaTable method can be used to get the table names. Then each table can be read using a select. Writing the data out to XML is easy using the built-in DataSet.WriteToXml() method. I also write out the schema file so that the columns will have the correct types when I read the data back in.

One last hitch: in .Net 4/VS2010 the OLEDB component works only with a 32-bit build. So change the Platform target in the Project properties as follows:

Here's the code. I’ve made even less of an effort than usual to make the code “production quality”, but note that a couple of the classes I use are IDisposable so I’m taking care to wrap them in a “using” block.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

// None of this is foolproof...caveat emptor.
namespace AccessToDataSet {
class Program {
static void Main(string[] args) {
if (args.Length == 0 || !args[0].EndsWith(".mdb", StringComparison.InvariantCultureIgnoreCase)) {
Console.WriteLine("Please specify the path to an MDB file.");
return;
}

DataSet dataSet = new DataSet();
using (var conn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=" + args[0])) {
conn.Open();
// Retrieve the schema
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// Fill the DataTables.
foreach (DataRow dataTableRow in schemaTable.Rows) {
string tableName = dataTableRow["Table_Name"].ToString();
// I seem to get an extra table starting with ~. I can't seem to screen it out based on information in schemaTable,
// hence this hacky check.
if (!tableName.StartsWith("~", StringComparison.InvariantCultureIgnoreCase)) {
FillTable(dataSet, conn, tableName);
}
}
}

string name = args[0].ToLowerInvariant();
dataSet.WriteXmlSchema(name.Replace(".mdb", ".schema.xml"));
dataSet.WriteXml(name.Replace(".mdb", ".xml"));