Data Access to MsAccess database

msaccess
We’re developing an adapter to connect to a legacy application. The connection is a MsAccess database that we create as an export. The legacy application will import the database.

My initial thought was to use Entity Framework to write to the MsAccess database. It turns out that OleDB is not supported by EF. Since the solution is temporary we decided to create our own Data Access Layer.

We only export the data and create a new database for every export. This means insert-only and no other operations. Most of the work is writing the OR-mapping, but that is a one time exercise. During testing we discovered that working in batch (array of records) improved the throughput from 2 minutes to 2 seconds for 1500 records. A simplified implementation of the Save method is below

public void Save(object[] records) {
    var builder = new OleDbConnectionStringBuilder {
        // provider present from Windows 2000 and up
        Provider = "Microsoft.ACE.OLEDB.12.0",
        DataSource = @"path_to_database_file"
    };
    using(var connection = new OleDbConnection(builder.ConnectionString)) {
        connection.Open();
        foreach (var record in records) {
            using (var command = connection.CreateCommand()) {
                command.CommandText = "INSERT INTO Table1 (Field1) VALUES (@Field1)";
                command.Parameters.Add("Field1", OleDbType.Integer).Value = 1;
                command.ExecuteNonQuery();
            }
        }
        connection.Close();
    }
}

As Dick Moffat stated on SDC last year: MsAccess is here to stay.

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Data Access to MsAccess database

  1. Pingback: Ignore integration tests when provider is missing | .NET Development by Eric

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