Strategy for querying Microsoft Access as an In-Memory Database using DataSets and ADO.NET

Introduction

DataSets are powerful data structures with the capability of allowing the programmer to treat them as in-memory databases. Recently I had a project that required me to do queries from an Access Database to retrieve data. If you do enough queries in MS Access, the time to access a table becomes a noticeable performance issue. (I suspect there is a lot of file I/O going on each time you do a query.). In order to reduce the time in querying the database, I thought to myself, "Why not just read the whole database into a DataSet?". Then  you can just do all your queries in memory through the DataTable Select call.

Importing the Database into a DataSet

The first step is to get the data into the DataSet.  The way we do this is to do a few wildcard queries on the tables we care about in the DataSet through the OleDbAdapter. We will need to create an adapter for each table that we want to import from MSAccess. Then we can just call Fill on the DataSet using each of the OleDbAdapters.

Listing 1 - Filling the DataSet from the Access Database

// connect to the Access Database
OleDbConnection  ConnectToDatabase()
{
    string  managedConnString = GetDefinitionDatabaseConnectionString();
    _conn = new OleDbConnection(managedConnString);
    _conn.Open();
    return _conn;
}

// Read in all the data in the tables we are interested in
void SetupDatabaseInMemory
{
    // connect to the Access Database
    _conn = ConnectToDatabase();

    // create an oledb adapter containing a wildcard query for each table we are interested in
    OleDbDataAdapter customerAdapter= new OleDbDataAdapter("SELECT Customer.* from [Customer]", _conn);
    OleDbDataAdapter orderAdapter = new OleDbDataAdapter("Select * from Order", _conn);
    OleDbDataAdapter  productAdapter = new OleDbDataAdapter("Select * from Product", _conn);

    // fill a dataset with all of the tables we are interested in using the adapters
    _ds = new DataSet();
    customerAdapter.Fill(_ds, "Customer");
    orderAdapter.Fill(_ds, "Order");
    productAdapter.Fill(_ds, "Product");
}

Now that we have data from all the three tables that we are interested in memory,  we can do all are querying in memory using the Select method of the DataTable. We can wrap this method with the ExecuteQuery method shown below. This method specifies both the table name we are querying and the filter string (WHERE clause) that we are using to filter the data. Once we do the query, we want to take the resulting rows and put them in a result DataSet. We do this by cloning the DataTable structure and then importing the DataRows in the query result into the table.

Listing 2 - Executing the SELECT-WHERE query in Memory using the Select method

// This will allow us to do an in memory query on a particular database table
public DataSet  ExecuteQuery(string tableName, string filter)
{
    // construct an empty result database to return
    DataSet dsResult = new DataSet();

    // Filter all the rows we are interested in by calling a Select on the database
    DataRow[] rows = _ds.Tables[tableName].Select(filter);

    // clone the existing table structure and clear it out
    DataTable copyTable = _ds.Tables[tableName].Clone();
    copyTable.Rows.Clear();

    // add the table result
    dsResult.Tables.Add(copyTable);

    // import the data rows resulting from the query into the result dataset
    foreach (DataRow dr in rows)
    {
        dsResult.Tables[0].ImportRow(dr);
    }
    return ds;
}

Additionally you can alter the method above to handle an "Order By" of the table.   Simply pass in the sorting filter as shown in listing 3:

Listing 3 - Executing the SELECT-WHERE and ORDER-BY query in Memory using the Select method

// This will allow us to do an in memory query on a particular database table
public DataSet  ExecuteQuery(string tableName, string filter, string sortCriteria)
{
    // construct an empty result database to return
    DataSet dsResult = new DataSet();

    // Filter and sort all the rows we are interested in by calling a Select on the database
    DataRow[] rows = _ds.Tables[tableName].Select(filter, sortCriteria);

    // clone the existing table structure and clear it out
    DataTable copyTable = _ds.Tables[tableName].Clone();
    copyTable.Rows.Clear(); 

    // add the table result
    dsResult.Tables.Add(copyTable);

    // import the data rows resulting from the query into the result dataset
    foreach (DataRow dr in rows)
    {
        dsResult.Tables[0].ImportRow(dr);
    }
    return ds;
}

Using the ExecuteQuery Method

Using the in memory data querying is fairly straight forward.  Below is some sample code for a few possible queries:

MyInMemoryExecution.ConnectToDatabase();
DataSet ds1 = MyInMemoryExecution.ExecuteQuery("Customer", "LastName > 'Jones'");
DataSet ds2 = MyInMemoryExecution.ExecuteQuery("Order", "OrderDate >= '01.03.2005' AND OrderDate <= '31.03.2007'", "OrderDate DESC" );
DataSet ds2 = MyInMemoryExecution.ExecuteQuery("Product", "ProductName LIKE 'Peanut%' AND ProductID > 10563", "OrderDate ASC" );

Conclusion

Now that we have a strategy for pulling the initial information out of the database into memory, we can have speedy database query access without the hassle of file I/O hits on each query. If you want to speed this process up even more, you can eliminate the DataSet import in the ExecuteQuery call and just return the DataRows themselves.