Skip to content
This repository has been archived by the owner on Dec 29, 2020. It is now read-only.

Home

Jump to bottom
Trevor Pilley edited this page Mar 16, 2020 · 35 revisions

Getting Started

In order to use the MicroLite ORM framework, you need to reference it in your solution. The easiest way to do this is install it via NuGet (if you are unfamiliar with NuGet, it's a package manager for Visual Studio - visit nuget.org for more information).

Install-Package MicroLite

Configuring the Connection

Once MicroLite has been installed, you need to add the connection string for the database you want to connect to into your app.config/web.config. MicroLite currently supports the following databases, follow the relevant page for the database you use:

The ISessionFactory created as a result of calling Configure.Fluently().ForConnection().CreateSessionFactory() should be created once at start-up and either registered in an IOC container or stored as a static property so that a single instance exists for the lifetime of your application.

Mapping Classes to Tables

MicroLite currently supports 2 options to define the mapping from a class and its properties to the columns in a table:

  • Convention Based Mapping (the default mapping option)
  • Attribute Based Mapping

The Convention Based Mapping offers a quick and easy way to define the mapping relationship between a class and a table and is the recommended approach. The default conventions are as follows:

  • The class name is the singular of the table name (e.g. public class Customer -> TABLE [Customers])
  • The property mapped as the identifier must be called either Id or {ClassName}Id
  • The identifier values are generated by the database (Identity or AutoIncrement)
  • The property is mapped if it has a public get and set
  • The column name and property name are the same, unless the property type is an enum in which case the column name should be {EnumTypeName}Id
  • The column value can be inserted
  • The column value can be updated

Consider the following simple table:

CREATE TABLE [dbo].[Customers]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Forename] [nvarchar](50) NOT NULL,
    [Surname] [nvarchar](50) NOT NULL,
    [DateOfBirth] [date] NOT NULL,
    [CustomerStatusId] [int] NOT NULL
)

When following the default conventions, the class would be defined as follows:

public class Customer // Singular of Customers
{
    public int Id { get; set; } // or public int CustomerId { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public DateTime DateOfBirth { get; set; }
    public CustomerStatus Status { get; set; }
}

public enum CustomerStatus
{
    Pending = 0,
    Current = 1,
    Closed = 2
}

It is easy to alter the conventions to match your own preference, see the Convention Based Mapping.

Attribute Based Mapping is declarative, explicit and more useful for a use with a legacy database where the column names don't easily map to a nice property name (e.g FName or ClntFstNm in the table and Forename in the property). There is a separate page for further details on the Attribute Based Mapping.

Identifier Strategies

MicroLite supports 3 strategies for specifying identifier values. Only one strategy can be used per class, however a mixture of strategies can be used through out your application.

  • Assigned - This strategy means that the value of the identifier property must be assigned by user code before the object is passed to ISession.InsertAsync().
  • DbGenerated (the default) - This strategy means that the value of the identifier is generated by the database when a new row is inserted. This strategy will update the identifier value on the inserted object when the transaction completes.
  • Sequence - This strategy means that the value of the identifier is generated from a database sequence and set when a new row is inserted. This strategy will update the identifier value on the inserted object when the transaction completes.

Note: Not all databases support Sequences

Using the Session

Once the connection has been setup and the classes mapped, it's time to start reading and writing data.

The ISession is the entry point to the database for user code. It exposes the methods to perform basic CRUD operations for objects.

To ensure that all resources and connections are properly cleaned up, ensure that each ISession is disposed correctly. Either in a using block or by configuring your IOC container to dispose of it after use.

An ISession or IReadOnlySession is opened via the ISessionFactory:

var session = sessionFactory.OpenSession(); // returns an ISession
var session = sessionFactory.OpenReadOnlySession(); // returns an IReadOnlySession

Read Operations (Available via both ISession and IReadOnlySession)

FetchAsync

Fetch returns the results of the query in an IList<T>.

Since this could result in a large number of objects being returned, it is suggested that FetchAsync is used to retrieve associated data rather than searching.

The following are examples of cases where using FetchAsync is appropriate:

  • Reading a static selection of lookup data (a list of countries, or product categories).
  • Reading a collection of associated data where you are fetching based upon a foreign key such as SELECT * FROM Invoice WHERE CustomerId = @p0

An example of where using PagedAsync is more appropriate:

  • Finding all customers based upon their postal (zip) code.

Note - Objects will be populated based upon the values in the select list which allows you to choose which properties are populated, however take care if you then want to issue an update based upon a partially read object as it could result in data loss (e.g. nulling or zeroing existing values). The easiest way to mitigate this is to use the SqlBuilder.Select("*").From(typeof(Customer))...ToSqlQuery() see the SqlBuilder section for further details

IList<Customer> customers;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        var query = new SqlQuery("SELECT [Id], [Forename], [Surname], [DateOfBirth], [CustomerStatusId] FROM [Customers]");

        customers = await session.FetchAsync<Customer>(query);

        transaction.Commit();
    }
}

PagedAsync

Paged will take an SqlQuery and rewrite it so that you can easily page queries. Paged supports WHERE and ORDER BY clauses when generating the paged query. Since Paged will always return a subset of the full result set, it is ideally suited for searching. There are 2 supported paging modes, PagingOptions.ForPage(page, resultsPerPage) and PagingOptions.SkipTake(skip, take) to allow for simple and flexible paging.

Note - As with Fetch, the objects returned will be populated based upon the values in the select criteria.

PagedResult<Customer> result;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        var query = new SqlQuery("SELECT [Id], [Forename], [Surname], [DateOfBirth], [CustomerStatusId] FROM [Customers]");

        result = await session.PagedAsync<Customer>(query, PagingOptions.ForPage(page: 1, resultsPerPage: 25));

        transaction.Commit();
    }
}

The result object is a PagedResult<T> which provides the following properties.

  • HasResults - A value indicating whether there are any results (checks paged.Results.Count > 0).
  • MoreResultsAvailable - A value indicating whether further pages of results can be retrieved.
  • Page - This is the page number requested.
  • Results - This is an IList<T> of objects.
  • ResultsPerPage - This is the number of results requested per page.
  • TotalPages - This is the number of pages based upon the current number of results per page.
  • TotalResults - This is the total number of results available.

SingleAsync

Single will result in a select statement being generated, it will include all mapped columns and filter against the identifier value. It will return either an object populated with the values found for the supplied identifier or null if no record is found with the identifier value.

Customer customer;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        customer = await session.SingleAsync<Customer>(12353);

        transaction.Commit();
    }
}

if (customer == null)
{
    // No customer found with id 12353.
}

There is also an overload for .Single which allows you to specify an SqlQuery - be sure that the query will return 1 or 0 results if you use this otherwise you will get an exception thrown at runtime if there are multiple results:

var query = new SqlQuery(
    "SELECT * FROM Customers WHERE Surname = @p0 AND CustomerStatusId = @p1",
    "Flintstone", CustomerStatus.Current);

customer = await session.SingleAsync<Customer>(query);

Include

Each call to FetchAsync, PagedAsync or SingleAsync will result in a round trip to the database which means that if you make 2 or more queries in a single piece of code, you can make multiple round trips which introduce more network latency into the time taken to run that piece of code.

For example, if we want to list a customer and all their invoices, we would usually write something like this:

Customer customer;
IList<Invoice> invoices;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        customer = await session.SingleAsync<Customer>(12353);
        invoices = await session.FetchAsync<Invoice>(new SqlQuery("SELECT * FROM Invoice WHERE CustomerId = @p0", 12353));

        transaction.Commit();
    }
}

Using the Include API allows MicroLite to batch the queries over a single connection to the database. The previous example could be re-written as follows:

Customer customer;
IList<Invoice> invoices;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        // specify all Include calls first, an IIncludeMany<T> will be returned which can then be inspected
        // after a call to .Single, .Fetch or .Paged
        var includeInvoices = session.Include.Many<Invoice>(new SqlQuery("SELECT * FROM Invoice WHERE CustomerId = @p0", 12353));

        // Load the customer, this will trigger all the include queries to be executed.
        customer = await session.SingleAsync<Customer>(12353);

        transaction.Commit();

        // Then access the values from the IIncludeMany<T>
        invoices = includeInvoices.Values;
    }
}

View the Includes blog post where the feature was introduced for further information.

Write Operations (Only available via an ISession)

DeleteAsync

To delete an object you have already loaded as an object instance, you can pass the object to the delete method. Delete returns a boolean value indicating whether the record was deleted successfully or not. If you choose to do so, you can act upon this to inform the user whether the delete was successful.

bool deleted;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        deleted = async session.DeleteAsync(customer);

        transaction.Commit();
    }
}

if (!deleted)
{
    // Inform the user.
}

This will result in a SQL statement being generated to delete the row from the table with the identifier set on the object passed.

Alternatively, if you know the type and identifier - you can delete the record without first having to read the object.

bool deleted;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        deleted = await session.Advanced.DeleteAsync(typeof(Customer)), 12345);

        transaction.Commit();
    }
}

if (!deleted)
{
    // Inform the user.
}

InsertAsync

Insert is used to insert a record into the database table that a class is mapped to, pushing the property values into the correct columns.

var customer = new Customer
{
    DateOfBirth = DateTime.Today,
    Forename = "Fred",
    Surname = "Flintstone",
    Status = CustomerStatus.Current
};

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        await session.InsertAsync(customer);
        // customer.Id will now be set to the value generated by the database when the record was inserted.
       
        transaction.Commit();
    }
}
  • If you are using the DbGenerated or Sequence strategy, the identifier value on the object will be updated.
  • If you are using the Assigned strategy, you should specify the identifier before inserting.

UpdateAsync

Update is used to update the values in the database table that a class is mapped to.

bool updated;

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        var customer = await session.SingleAsync<Customer>(12354);
        customer.Status = CustomerStatus.Closed;

        updated = session.Update(customer);

        transaction.Commit();
    }
}

if (!updated)
{
    // Inform the user.
}

Querying

The way to query data from a SQL database is by writing an SQL query. Therefore, MicroLite ORM does not provide any custom query language, OO facade or Linq provider. These always have limitations and ultimately just end up producing SQL anyway. It is always easier and more performant to simply write an SQL statement.

Any queries that MicroLite generates are parameterised and it is recommended that all user created queries are also parameterised. MicroLite will always generate parameters using the parameter identifier for the dialect specified such as @p0, @p1 ... @p10 for MsSQL, MySQL and SQLite and :p0, :p1 ... :p10 for PostgreSQL. It is recommended that you use the same syntax unless you are calling a stored procedure in which case you would need to specify the parameter names.

SqlQuery

The MicroLite SqlQuery object encapsulates the command text and any parameter values required for the query.

A basic query with no parameters.

var query = new SqlQuery("SELECT [Id], [Forename], [Surname], [DateOfBirth], [CustomerStatusId] FROM [Customers]");

A parameterised query.

var query = new SqlQuery(
    "SELECT [Id], [Forename], [Surname], [DateOfBirth], [CustomerStatusId] FROM [Customers] WHERE [DateOfBirth] > @p0",
    new DateTime(1980, 1, 1));

Note - Objects will be populated based upon the values in the select list which allows you to choose which properties are populated, however take care if you then want to issue an update based upon a partially read object as it could result in data loss (e.g. nulling or zeroing existing values). The easiest way to mitigate this is to use the SqlBuilder.Select("*").From(typeof(Customer))...ToSqlQuery() see the SQL Builder page for further details

A query which executes a stored procedure. The syntax has been simplified, you just add the parameter names and the values in the order specified in the query text.

var query = new SqlQuery("EXEC CustomersByStatus @StatusId", CustomerStatus.Current);

The recommended approach for queries is to create a class that contains them per type being queried. This allows you to maintain them easily and easily see what queries you already have to promote re-use and stay dry (don't repeat yourself). It also makes it easier to unit test code to ensure that the correct query is being used.

public static class CustomerQueries
{
    public static SqlQuery FindByStatus(CustomerStatus customerStatus)
        => new SqlQuery("EXEC CustomersByStatus @StatusId", customerStatus);

    public static SqlQuery BornAfter(DateTime minDateOfBirth)
        => new SqlQuery(
            "SELECT [Id], [Forename], [Surname], [DateOfBirth], [CustomerStatusId] FROM [Customers] WHERE [DateOfBirth] > @p0",
            minDateOfBirth);
    ...
}

To use it, you can then call

var query = CustomerQueries.FindByStatus(CustomerStatus.Current);

or

var query = CustomerQueries.BornAfter(new DateTime(1980, 1, 1));

NOTE: In all queries the SQL statement should always refer to the column names in the table, not the property names if they do not match.

What Next?

This page has covered the basics of configuring MicroLite, mapping classes, basic CRUD operations and Querying.

It will be worth while reading the following:

There are more advanced topics, discussions and examples on the MicroLite blog.

Clone this wiki locally