Clone wiki

JetToObjects / Home

* NOTE: This project has moved to github: https://github.com/ekmsystems/JetToObjects *

JetToObjects

JetToObjects is a basic data access library that simplifies accessing a Jet database and getting results back.

Opening A Connection

All queries start with a call to Db.Open() which must be supplied with the path to the database file. Stemming from the .Open() call is a fluent interface that allows the rest of the query to be constructed.

var result = Db.Open("database.mdb")...


Query API

The following query methods are supported:

ExecuteNonQuery

// Inserts a new row into the Products table and requests that the identity 
// of the new row is returned from the call.

var id = Db.Open(db).ReturnIdentity().ExecuteNonQuery("insert into Products (Name) values (@Name)", 
            new Param("@Name", "New Product", OleDbType.VarWChar));

ExecuteNonQueryWithConnection

NOTE: This function does NOT automatically dispose of the conncetion, you must do this yourself.

// Performs an Execute NonQuery but on an exisiting connection that you pass in as a parameter to the function. 

var connection = new OleDbConnection(connectionString);
var id = Db.Open(db).ReturnIdentity().ExecuteNonQuery("insert into Products (Name) values (@Name)", connection
            new Param("@Name", "New Product", OleDbType.VarWChar));


ExecuteScalar

// Selects just the product name from the product whose ID is 42

var name = Db.Open(db).ExecuteScalar("select Name from Products where ID = @ID",
              new Param("@ID", 42, OleDbType.Numeric));


ExecuteScalarWithConnection

NOTE: This function does NOT automatically dispose of the conncetion, you must do this yourself.

// Performs an Execute Scalar but on an exisiting connection that you pass in as a parameter to the function. 

var connection = new OleDbConnection(connectionString);
var name = Db.Open(db).ExecuteScalar("select Name from Products where ID = @ID", connection
              new Param("@ID", 42, OleDbType.Numeric));


ExecuteSingle

// Selects the name, description and price of the product whose ID is 42. The result comes 
// back as a dynamic object with the respective properties added to it from the row.

var product = Db.Open(db).ExecuteSingle("select Name, Description, Price from Products where ID = @ID",
                 new Param("@ID", 42, OleDbType.Numeric));


ExecuteSingleWithConnection

NOTE: This function does NOT automatically dispose of the conncetion, you must do this yourself.

// Performs an Execute Single but on an exisiting connection that you pass in as a parameter to the function. 

var connection = new OleDbConnection(connectionString);
var product = Db.Open(db).ExecuteSingle("select Name, Description, Price from Products where ID = @ID", connection
                 new Param("@ID", 42, OleDbType.Numeric));


ExecuteMany

// Selects every product from the Products table. The result is an IEnumerable<dynamic>, 
// with each element holding every column from the row.

var products = Db.Open(db).ExecuteMany("select * from Products");


ExecuteManyWithConnection

NOTE: This function does NOT automatically dispose of the conncetion, you must do this yourself.

// Performs an Execute Many but on an exisiting connection that you pass in as a parameter to the function.

var connection = new OleDbConnection(connectionString);
var products = Db.Open(db).ExecuteMany("select * from Products", connnection);



ExecuteMultipleQueries

/// <summary>
/// Loops through the IEnumerable of statements passed in, executing each with respective params all whilst the current connection is open.
/// </summary>
/// <param name="queries">A list of MultipleQuery objects, including the query and params for each statement to be run and the QueryType to be executed (ExecuteSingle,ExecuteMany etc. MultipleQuery: int Id: Id, string Query: Query, Parameters Param[] : Param[], /* Enum */ QueryType? queryType: QueryType</param>
/// <returns>A dictionary collection of the results from the queries passed in, with the Id of the query as the Key in the dictionary and a dynamic as the Value. You will have to cast the dynamic value to the result type for each corresponding QueryType to obtain your results from it.</returns>

var selectAllProductSql = "SELECT * FROM [products];";
var updateProductSql = "UPDATE [products] SET [ProductName] = "Test product name" WHERE [id] = 1; ";
// QueryId has to be >= 1
var udpateQueryId= 1;
var selectQueryId= 2;
var queries = new List<MultipleQuery>()
                {
                    new MultipleQuery {
                    Id= udpateQueryId,
                    Query = updateProductSql, 
                    Parameters = parameters.ToArray(),
                    QueryType = QueryType.ExecuteNonQuery 
                },
                {
                    new MultipleQuery {
                    Id= selectQueryId,
                    Query = selectAllProductSql, 
                    Parameters = parameters.ToArray(),
                    QueryType = QueryType.ExecuteMany 
                }
             };

var queryResults = _databaseClient.ExecuteMultipleQueries(_database, queries);
var updateRowsAffected = queryResults[udpateQueryId].RowsAffected;

// Select Results need to be cast to an IEnumerable<dynamic> first as this is the return type from the ExecuteMany function. 
var castResults = (IEnumerable<dynamic>)queryResults[selectQueryId];

// If you map your results to a Product class you will now have your results in the correct format.
var selectResults = mapProductResultsExampleFunction(castResults);



ExecuteMultipleQueriesWithConnection

NOTE: This function does NOT automatically dispose of the conncetion, you must do this yourself.

/// <summary>
// Performs an Execute Multiple Queries but on an exisiting connection that you pass in as a parameter to the function. 
/// </summary>

var selectAllProductSql = "SELECT * FROM [products];";
var updateProductSql = "UPDATE [products] SET [ProductName] = "Test product name" WHERE [id] = 1; ";
// QueryId has to be >= 1
var udpateQueryId= 1;
var selectQueryId= 2;
var queries = new List<MultipleQuery>()
                {
                    new MultipleQuery {
                    Id= udpateQueryId,
                    Query = updateProductSql, 
                    Parameters = parameters.ToArray(),
                    QueryType = QueryType.ExecuteNonQuery 
                },
                {
                    new MultipleQuery {
                    Id= selectQueryId,
                    Query = selectAllProductSql, 
                    Parameters = parameters.ToArray(),
                    QueryType = QueryType.ExecuteMany 
                }
             };
var connection = new OleDbConnection(connectionString);
var queryResults = _databaseClient.ExecuteMultipleQueries(_database, queries, connection);
var updateRowsAffected = queryResults[udpateQueryId].RowsAffected;

// Select Results need to be cast to an IEnumerable<dynamic> first as this is the return type from the ExecuteMany function. 
var castResults = (IEnumerable<dynamic>)queryResults[selectQueryId];

// If you map your results to a Product class you will now have your results in the correct format.
var selectResults = mapProductResultsExampleFunction(castResults);


CompactRepair

// Database is compacted to a new database file, then copied over the old one 

var product = Db.Open(db).CompactRepair("newDatabasePath"));


Mapping API

To support legacy situations, JetToObjects comes with an object mapper that allows you to convert the dynamic results to other classes by following a mapping rule set.

Example
Let's assume you have the following class, and wish to map the results from a query (the dynamic object) to it:

public class ProductData
{
    public string Name { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
}


The Mapper.Map() call will allow you to do just that:

var product = Db.Open(db).ExecuteSingle("select name, desc, cost from tbl_products where id = @id",
                 new Param("@id", 42, OleDbType.Numeric));

var productData = Mapper.Map<ProductData, ProductMapping>(product);


Notice the use of ProductMapping (the second generic argument) which tells Mapper how to re-map the fields (name, desc, cost) from the query result to the fields in the ProductData class. ProductMapping looks like this:

public class ProductMapping : ObjectMapping
{
    public ProductMapping()
    {
        Map("name", "Name");
        Map("desc", "Description");
        Map("cost", "Price");
    }
}

Updated