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.