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:

AccessToDataSet

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"));
    }

    private static void FillTable(DataSet dataSet, OleDbConnection conn, string tableName) {
      DataTable dataTable = dataSet.Tables.Add(tableName);
      using (OleDbCommand readRows = new OleDbCommand("SELECT * from " + tableName, conn)) {
        OleDbDataAdapter adapter = new OleDbDataAdapter(readRows);
        adapter.Fill(dataTable);
      }
    }
  }
}

Author: natebrix

Follow me on twitter at @natebrix.

1 thought on “Converting an Access DB to XML using C#”

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