How to load an Access Database into a DataSet object in C#

Today I needed to load a Microsoft Access Database (*.ACCDB / *.MDB) into a DataSet object in .NET environment using C#.

After finding some (partial) solutions I finally developed a little C# class in order to accomplish this task. This class loads an Access Database (whatever in ACCDB or MDB formats) into a DataSet object. After the operation you can directly access to the loaded tables by using the .Tables property of the generated DataSet object.

/* 
 * Microsoft Access Database DataSet Loader for .NET
 * Version 20150511
 *
 * Created by SiZiOUS
 * sizious (at) gmail (dot) com - @sizious - www.sizious.com - fb.com/sizious
 *
 * Licensed under the WTFPL licence
 * See http://www.wtfpl.net/txt/copying/
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace DbLoader
{
    /// <summary>
    /// Useful utilities for Microsoft Access Database files.
    /// </summary>
    public static class AccessDbLoader
    {
        /// <summary>
        /// Loads a Microsoft Access Database file into a DataSet object.
        /// The file can be the in the newer ACCDB format or MDB legacy format.
        /// </summary>
        /// <param name="fileName">The file name to load.</param>
        /// <returns>A DataSet object with the Tables object populated with the contents of the specified Microsoft Access Database.</returns>
        public static DataSet LoadFromFile(string fileName)
        {
            DataSet result = new DataSet();

            // For convenience, the DataSet is identified by the name of the loaded file (without extension).
            result.DataSetName = Path.GetFileNameWithoutExtension(fileName).Replace(" ", "_");

            // Compute the ConnectionString (using the OLEDB v12.0 driver compatible with ACCDB and MDB files)
            fileName = Path.GetFullPath(fileName);            
            string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};User Id=Admin;Password=", fileName);

            // Opening the Access connection
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();

                // Getting all user tables present in the Access file (Msys* tables are system thus useless for us)
                DataTable dt = conn.GetSchema("Tables");
                List<string> tablesName = dt.AsEnumerable().Select(dr => dr.Field<string>("TABLE_NAME")).Where(dr => !dr.StartsWith("MSys")).ToList();

                // Getting the data for every user tables
                foreach (string tableName in tablesName)
                {
                    using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", tableName), conn))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                        {
                            // Saving all tables in our result DataSet.
                            DataTable buf = new DataTable("["+tableName+"]");
                            adapter.Fill(buf);
                            result.Tables.Add(buf);
                        } // adapter
                    } // cmd
                } // tableName
            } // conn

            // Return the filled DataSet
            return result;
        }
    }
}

* Microsoft Access Database DataSet Loader for .NET

* Version 20150511

* Created by SiZiOUS

* sizious (at) gmail (dot) com - @sizious - www.sizious.com - fb.com/sizious

* Licensed under the WTFPL licence

* See http://www.wtfpl.net/txt/copying/

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Linq;

using System.Text;

namespace DbLoader

{

/// <summary>

/// Useful utilities for Microsoft Access Database files.

/// </summary>

public static class AccessDbLoader

{

/// <summary>

/// Loads a Microsoft Access Database file into a DataSet object.

/// The file can be the in the newer ACCDB format or MDB legacy format.

/// </summary>

/// <param name="fileName">The file name to load.</param>

/// <returns>A DataSet object with the Tables object populated with the contents of the specified Microsoft Access Database.</returns>

public static DataSet LoadFromFile(string fileName)

{

DataSet result = new DataSet();

// For convenience, the DataSet is identified by the name of the loaded file (without extension).

result.DataSetName = Path.GetFileNameWithoutExtension(fileName).Replace(" ", "_");

// Compute the ConnectionString (using the OLEDB v12.0 driver compatible with ACCDB and MDB files)

fileName = Path.GetFullPath(fileName);

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};User Id=Admin;Password=", fileName);

// Opening the Access connection

using (OleDbConnection conn = new OleDbConnection(connString))

{

conn.Open();

// Getting all user tables present in the Access file (Msys* tables are system thus useless for us)

DataTable dt = conn.GetSchema("Tables");

List<string> tablesName = dt.AsEnumerable().Select(dr => dr.Field<string>("TABLE_NAME")).Where(dr => !dr.StartsWith("MSys")).ToList();

// Getting the data for every user tables

foreach (string tableName in tablesName)

{

using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", tableName), conn))

{

using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))

{

// Saving all tables in our result DataSet.

DataTable buf = new DataTable("["+tableName+"]");

adapter.Fill(buf);

result.Tables.Add(buf);

} // adapter

} // cmd

} // tableName

} // conn

// Return the filled DataSet

return result;

}

To use it, it’s really simple:

Add the provided AccessDbLoader class to your project.
Load the Access Database file by using the following snippet:
DataSet ds = AccessDbLoader.LoadFromFile("my.accdb");
You can now use the ds.Tables property.

For your convenience you’ll find a sample program below:

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

namespace DbLoader
{
    public class Program
    {
        private static void DumpDataSet(DataSet ds)
        {
            Console.Out.WriteLine("DataSet: {0}", ds.DataSetName);

            // For every tables in the DataSet ...
            foreach (DataTable dt in ds.Tables)
            {
                Console.Out.WriteLine("\tTableName: {0}", dt.TableName);

                // ... Write the table schema
                foreach (DataColumn col in dt.Columns)
                {
                    Console.Out.Write("\t\t" + col.ColumnName + " ");
                }
                Console.Out.WriteLine("\t\t");

                // ... Write the table contents
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        Console.Out.Write("\t\t" + row[i]);
                    }
                    Console.Out.WriteLine("");
                }
            }
        }

        public static void Main(string[] args)
        {
            // Load an Access ACCDB file
            DataSet ds1 = AccessDbLoader.LoadFromFile("test.accdb");
            DumpDataSet(ds1);

            // Load an Access MDB (legacy) file
            DataSet ds2 = AccessDbLoader.LoadFromFile("test.mdb");
            DumpDataSet(ds2);

            Console.Out.WriteLine("\nStrike <ENTER> to exit . . .");
            Console.In.ReadLine();
        }
    }
}

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Data.OleDb;

using System.IO;

using System.Linq;

using System.Text;

namespace DbLoader

{

public class Program

{

private static void DumpDataSet(DataSet ds)

{

Console.Out.WriteLine("DataSet: {0}", ds.DataSetName);

// For every tables in the DataSet ...

foreach (DataTable dt in ds.Tables)

{

Console.Out.WriteLine("\tTableName: {0}", dt.TableName);

// ... Write the table schema

foreach (DataColumn col in dt.Columns)

{

Console.Out.Write("\t\t" + col.ColumnName + " ");

}

Console.Out.WriteLine("\t\t");

// ... Write the table contents

foreach (DataRow row in dt.Rows)

{

for (int i = 0; i < dt.Columns.Count; i++)

{

Console.Out.Write("\t\t" + row[i]);

}

Console.Out.WriteLine("");

}

public static void Main(string[] args)

{

// Load an Access ACCDB file

DataSet ds1 = AccessDbLoader.LoadFromFile("test.accdb");

DumpDataSet(ds1);

// Load an Access MDB (legacy) file

DataSet ds2 = AccessDbLoader.LoadFromFile("test.mdb");

DumpDataSet(ds2);

Console.Out.WriteLine("\nStrike <ENTER> to exit . . .");

Console.In.ReadLine();

}

I think this class can be improved in some ways (e.g. handling primary / foreign keys constraints) but that’s enough for my needs.

How to load an Access Database into a DataSet object in C#

One thought on “How to load an Access Database into a DataSet object in C#”

Leave a Reply Cancel reply