Querying

MicroLite contains a class called SqlQuery which is used to represent the command text and argument values for a command.

A basic query with no parameters.

var query = new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [Customers]");

A parameterised query.

var query = new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [Customers] WHERE [Dob] > @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).

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.Active);

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 reduce duplication. 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)
    {
        return new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [Customers] WHERE [StatusID] = @p0", customerStatus);
    }

    ...
}

To use it, you can then call

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

This article was written for version 1.2.2 of the MicroLite framework.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s