Kros.Utils.MsAccess

Kros.Utils.MsAccess is a general library of various utilities to simplify the work of a programmer with Microsoft Access databases.

For some (especially database) stuff to work properly, the library needs to be initialized when the program starts by calling LibraryInitializer.InitLibrary.

Library is compiled for .NET Framework 4.6.

Documentation

For configuration, general information and examples see the documentation.

Download

Kros.Libs is available from Nuget Kros.Utils.MsAccess

Contributing Guide

To contribute with new topics/information or make changes, see contributing for instructions and guidelines.

This topic contains following sections

Kros.Utils.MsAccess

• General Utilities
• Database Schema
• Bulk Operations - Bulk Insert and Bulk Update
• Unit Testing Helpers

MsAccess General Utilities

The MsAccessDataHelper class contains general utilities for working with the MS Access database connection.

• Retrieve current MS Access provider: MsAccessProvider
• Determining whether the connection to the MS Access database is exclusive: IsExclusiveMsAccessConnection
• Determining whether the connection is a connection to the MS Access database: IsMsAccessConnection

MsAccess Database Schema

It is very easy to get a database schema. Since the acquisition of the schema is a time-consuming operation the loaded scheme is held in a cache and the next schema is retrieved. The database schema includes the TableSchema tables, their ColumnSchema columns and IndexSchema indexes.

OleDbConnection cn = new OleDbConnection("MS Access Connection String");

DatabaseSchema schema = DatabaseSchemaLoader.Default.LoadSchema(cn);

MsAccess Bulk Operations - Bulk Insert and Bulk Update

Inserting (INSERT) and updating (UPDATE) large amounts of data in a database are time-consuming. Therefore, support for rapid mass insertion, Bulk Insert and a fast bulk update, Bulk Update. The IBulkInsert and IBulkUpdate interfaces are used. They are implemented for MsAccess database in the MsAccessBulkInsert and MsAccessBulkUpdate classes. As a data source, it serves any IDataReader or DataTable table.

Because IDataReader is an intricate interface, you just need to implement the simplier interface IBulkActionDataReader. If the source is a list (IEnumerable), it is sufficient to use the EnumerableDataReader<T> class for its bulk insertion.

private class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public void InsertManyItems()
{
    IEnumerable<Item> data = GetData();

    using (var reader = new EnumerableDataReader<Item>(data, new string[] { "Id", "Name" }))
    {
        using (var bulkInsert = new MsAccessBulkInsert("connection string"))
        {
            bulkInsert.Insert(reader);
        }
    }
}

MsAccess Unit Testing Helpers

Standard unit tests should be database-independent. But sometimes it is necessary to test the actual database because the test items are directly related to it. To test the actual database you can use the MsAccessTestHelper class. It creates a database for testing purposes on the server and runs tests over it. When tests are finished the database is deleted.

private const string BaseDatabasePath = "C:\testfiles\testdatabase.accdb";

private const string CreateTestTableScript =
@"CREATE TABLE [TestTable] (
    [Id] number NOT NULL,
    [Name] text(255) NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY ([Id])
)";

[Fact]
public void DoSomeTestWithDatabase()
{
    using (var helper = new MsAccessTestHelper(ProviderType.Ace, BaseDatabasePath, CreateTestTableScript))
    {
        // Do tests with connection helper.Connection.
    }
}