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

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.