Français
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
/* * 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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.