Category Archives: Tutorial

Matching Database Types

In order for SQL queries to perform as fast as possible, it is important to ensure that the types you use in your .NET code match the types you use in your database. This is especially important when you have indexes because if the data type of the SQL parameter doesn’t match the data type of the column, the index for that column won’t be used!

Consider the following table and class:

CREATE TABLE [dbo].[Customers]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Forename] NVARCHAR(50) NOT NULL,
    [Surname] NVARCHAR(50) NOT NULL,
    [DateOfBirth] DATETIME2 NOT NULL,
    [CustomerStatusId] INT NOT NULL
)
public class Customer
{
    public int Id { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public DateTime DateOfBirth { get; set; }
    public CustomerStatus Status { get; set; }
}

The default configuration of MicroLite is to map string to DbType.String (NVARCHAR) since .NET strings are unicode. If you don’t use unicode columns in your database, you should change the default type mapping for strings as follows:

// Map strings as ANSI instead of unicode
TypeConverter.RegisterTypeMapping(typeof(string), DbType.AnsiString);

Also, the default mapping for DateTime is to DbType.DateTime so if you use DATETIME2 columns, you should change the default mapping as follows:

// Always use DbType.DateTime2 instead of DbType.DateTime for System.DateTime
TypeConverter.RegisterTypeMapping(typeof(DateTime), DbType.DateTime2);

Note, in MicroLite 7 we plan to change the default DateTime mapping to DbType.DateTime2.

You can see the full set of default mappings Here

Advertisements

Comparing which is better

I received an email asking

I am comparing “MicroLite 6.2.5” with EF & nhibernate for PostgreSQL.

May I ask you in two lines, why “MicroLite 6.2.5” is better?

Unfortunately it’s not actually possible to answer that question without actually defining how are we measuring “better”.

What would have been more sensible would have been to ask “How are they different?” and then compare the features each offers and the pros and cons to decide which is better for you.

MicroLite OData update

The latest release of MicroLite.Extensions.WebApi (6.1) and Net.Http.WebApi.OData (3.1) now provide support for nearly the entire OData 3 spec that can be supported by MicroLite.

It is now possible to compare 2 properties:

$filter=FirstName eq LastName

And call nested functions:

$filter=concat(concat(City, ', '), Country) eq 'Berlin, Germany'

Decimal/Double and Single values don’t require .0 for whole values:

$filter=Price eq 5M can now be used as well as $filter=Price eq 5.0M

Method parameters no longer require a space after the comma:

$filter=endswith(CompanyName,'Futterkiste’) can now be used as well as
$filter=endswith(CompanyName, 'Futterkiste')

Literal strings (text wrapped in single quotes) can now contain a single quote by escaping it with another single quote:

$filter=LastName eq ‘O’’Brien’

Grouping is now supported in queries:

$filter=LastName eq 'Smith' and (Title eq 'Mr' or Title eq 'Mrs')

Without the grouping, the query would be translated as:

LastName = Smith and Title = Mr
Or
Title = Mrs

There is no concept of an “in list” in OData so a grouped set of “or”s is the way to achieve the same result.

endswith, startswith, and substringof now no longer require eq true at the end:

startswith(CompanyName,’Alfr’) is the same as startswith(CompanyName,’Alfr’) eq true

The not operator has also been implemented which means you can negate function calls:

not startswith(CompanyName,’Alfr’)

MicroLite 5.3 upcoming features

MicroLite 5.3 is currently in development, you can see the progress by checking the GitHub 5.3 Milestone

Sequence Columns

The main change in this release is the support for sequence columns as identifiers, this will be supported in the Firebird dialect and also the MS SQL Server dialect.

In order to support the new features in MS SQL Server 2012, we are changing the existing MS SQL Server dialect. The existing dialect will be renamed to MsSql2005 and a new MsSql2012 will be added. As far as the configuration goes, if you use MS SQL Server you will need to change the following method call:

.ForMsSqlConnection("connection name")

to one of the following:

.ForMsSql2005Connection("connection name")
// or
.ForMsSql2012Connection("connection name")

MsSql2005 will work with MS SQL Server 2005 or later, the new MsSql2012 will work with MS SQL Server 2012 or later. In addition to using supporting sequences (as well as still supporting identity), the MsSql2012Dialect also uses the new OFFSET FETCH syntax instead of common table expressions which the 2005 dialect uses.

SQL Builder

The In and NotIn methods on the SqlBuilder now support multiple sub queries which means you can use it like this:

var subOne = new SqlQuery("SOME SQL");
var subTwo = new SqlQuery("SOME SQL");

var parentQuery = SqlBuilder
    .Select("Id", "Firstname", "Surname", "Username")
    .From("Users")
    .Where("Id").In(subOne, SubTwo)
    .ToSqlQuery();

The aim is to have MicroLite 5.3 finished by 17th September and we will hopefully have a beta build out in the next day or two, keep an eye out for it on nuget.org.

MicroLite 5.0 – String Handling

In .NET, strings are Unicode, however some databases have specific data types for strings which differentiate between ASCII and Unicode (e.g. in MS SQL Server VarChar being ASCII and NVarChar being Unicode).

If the database makes the distinction and the wrong type is used in a SQL command, it can result in poor performing queries (table scans over index lookups or unnecessary type conversion).

In MicroLite 5.0, the database connection/command handling has been exposed via a ‘database driver’. The new IDbDriver interface defines a HandleStringsAsUnicode property which is set to true by default but allows explicit control over whether strings are sent to the database as ASCII or Unicode (the default).

If you use MS SQL Server and the columns are NVarChar, HandleStringsAsUnicode should be left as true (the default value). If they are VarChar, HandleStringsAsUnicode should be set to false.

For other databases, refer to the documentation and if in doubt, leave it set to true.

If you need to, you can set HandleStringsAsUnicode to false when the session factory is created:

var sessionFactory = Configure.Fluently()
    .ForMsSqlConnection("Connection")
    .CreateSessionFacory();

// Only change this if you are sure you need to.
sessionFactory.DbDriver.HandleStringsAsUnicode = false;

MicroLite 5.0 – New Features

The last few posts have covered some of the changes coming in MicroLite 5.0, now that the major ones are covered let’s take a look at at some of the new features:

ObjectDelta

The ObjectDelta class and the ISession.Advanced.Update(ObjectDelta) add a neat way to perform partial updates against a database row.

// Firstly we need to instantiate a new ObjectDelta instance and 
// provide it with the class the update is targeted against and 
// the identifier value of the instance to update.
var objectDelta = new ObjectDelta(
    type: typeof(Customer), identifier: 12823);

// Then we can add a number of updates.
objectDelta.AddChange(propertyName: "Locked", newValue: 0);
...

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        // Apply the update and confirm if a record was updated.
        bool wasUpdated = session.Advanced.Update(objectDelta);

        transaction.Commit();
    }
}

UriTypeConverter

The type converter support has been increased by adding support for the System.Uri class. As before with the XDocument support in MicroLite 3.0.3, it’s as simple as setting the property type:

public class Thing
{
    public Uri Website { get; set; }
}

The conversion is managed by the MicroLite.TypeConverters.UriTypeConverter and is registered in the TypeConverter.Converters collection by default.

ConnectionScope

We have added finer control over when the connection to the database is opened and closed. The default behaviour matches MicroLite 4.0 and earlier which is to open a connection when a transaction is started and closed it when the transaction is completed.

However there may be times where you want to open the connection and close it when a session is opened and disposed (e.g. performing multiple transactions in a single session where it is more performant to hold a connection open or where you use SQLite in-memory databases where the database is deleted when the connection is closed).

In order to use it, call the overload on ISessionFactory when opening a session:

var session = sessionFactory.OpenSession(ConnectionScope.PerSession);

MicroLite 5.0 – Changes to Type Converters

In MicroLite 5.0, we have enhanced the Type Converters which we introduced in MicroLite 3.1.

The following method has been added to the interface:

object ConvertFromDbValue(IDataReader reader, int index, Type type);

This allows you to read the value from the IDataReader at the given index in the most efficient way (e.g. as an Int32 without the value being boxed).

From MicroLite 5.0, the full interface now looks like this:

ITypeConverter MicroLite 5.0

The ConvertFromDbValue(IDataReader, int, Type) is called when building an object from the results of a query, the ConvertFromDbValue(object, Type) method still exists but is only used when using ISession.Advanced.ExecuteScalar as there is no data reader used for that operation.