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


Avoiding SQL injection

During a code review the other day, I encountered an example of how it is still trivial to write queries which are susceptible to SQL injection.

Take the following example:

var query = new SqlQuery(
      "SELECT * FROM [Documents] WHERE DocumentName = '{0}' AND [Searched] = 1",

The fact that you are doing string.Format is a strong indication that you are making a mistake which could lead to SQL injection.

You should always use parameterised queries, all the examples for MicroLite are shown in this way and we provide a powerful fluent SQL Builder to allow you to easily construct more complex queries. There will be cases where using the SQL Builder, but even in those situations do not concatenate inline SQL.

The correct way to create the above query would be:

var query = new SqlQuery(
      "SELECT * FROM [Documents] WHERE DocumentName = @p0 AND [Searched] = @p1",

The lesson here is “just because you are using an ORM, you are not guaranteed to be safe from SQL injection”.

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.

Looking ahead to MicroLite 7.0

You may have noticed that there hasn’t been a lot going on recently with MicroLite, we’ve had a few bug fixes since version 6.2 was released but nothing big. There are 2 main reasons behind that, the first is that I’ve been busy with some other things and the second is that I’ve been thinking about where to take MicroLite next.

There’s nothing concrete at the moment, but the main things I would like to do are:

  • Move to .NET Core
  • Support multiple mapping conventions (similar to how the attribute and convention routing works in ASP.NET Web API)
  • Support immutable and private types
  • Go async only
  • Move the other database providers into separate assemblies which can reference the 3rd party libraries (instead of everything working against DbCommand and DbParameter)
  • Allow custom instance factories
  • Support out parameters from stored procedures
  • Replace the MVC and WebApi extensions with a single AspNet extension for ASP.NET 5 (vNext)

That’s by no means a complete list, if there’s anything you would like to see added or any concerns with anything on that list, please comment here or raise an issue on the GitHub site.

Upgrading to MicroLite 6.2

MicroLite 6.2.0 has been released on NuGet.

The 2 main changes in this release are:

  • The session re-uses the same command during its lifecycle so multiple operations via a session are more efficient – especially multiple operations of the same type (e.g. multiple inserts).
  • Improved the handling of TimeSpan – beware that this is a breaking change (see below)

Prior to MicroLite 6.2, MicroLite was able to map a TimeSpan to a MS SQL Time column – however it turns out there are a number of problems with the Time type:

  1. It has a maximum value of 24:00:00 since it is actually designed to represent the time of day
  2. It cannot be used in some aggregate functions such as SUM and AVERAGE which makes summarising data diffucult

In order to mitigate these issues, MicroLite 6.2 now maps TimeSpan to a BIGINT by persisting the tick count of the TimeSpan – this has the added benefit of also enabling TimeSpan to be used by any database supported by MicroLite.

If you are updating to MicroLite 6.2 and you are currently using TimeSpan -> TIME you have 2 options:

1) – Update your database schema to use a BIGINT (add a new column, populate it by casting the time as the tick count, remove the old column).

2) – Configure MicroLite 6.2 to behave like MicroLite 6.1:

// In startup code (before calling Configure.Fluently()...):

// 1. Remove the new TimeSpanTypeConverter:
var timeSpanTypeConverter = TypeConverter.Converters.OfType<TimeSpanTypeConverter>().Single();

// 2. Reset the DbType mapping for TimeSpan:
TypeConverter.RegisterTypeMapping(typeof(TimeSpan), System.Data.DbType.Time);
TypeConverter.RegisterTypeMapping(typeof(TimeSpan?), System.Data.DbType.Time);

Understanding InvalidCastException

We can sometimes get an InvalidCastException thrown when reading a record from the database, the usual reason for this is that the database column does not match the property type on the class. For example, we have a column defined in the database as:

PhoneNumber int not null

and in the class we have

public string PhoneNumber { get; set; }

The reason this will fail is that MicroLite will read the value using the most efficient method and since the property is a string and IDataReader has a method GetString that will be called, essentially doing this:

obj.PhoneNumber = dataReader.GetString(idx);

However since the actual returned value in the IDataReader is an int which cannot be implicitly cast to a string we get an InvalidCastException. To resolve this, change the property type and database column to be compatible types (e.g. change the database column to string or change the class property to int).

MicroLite 6.0 Logging/Debugging

One of the things we focused on in MicroLite 6.0 is making the logging and debugging process even nicer for developers.

Since very early on MicroLite has had support for writing to your application log via the logging extension packages. However it has never been easy to see how a class has been mapped. To make this easier to understand, we have created an extension method for IObjectInfo which can be used to write the mapping to a TextWriter. We also write this to the log if the log is capturing Debug statements.

It’s as easy as this:

// Resolve the IObjectInfo for the mapped class.
var objectInfo = ObjectInfo.For(typeof(Customer));

// Write the mappings to the console
// or write the mappings to a string
var stringWriter = new StringWriter()

// Write mappings to a TextWriter
var mappingDetails = stringWriter.ToString();

For the following class using the default conventions:

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; }

It will emit the following:

MicroLite Mapping:
Class ‘MyApplication.Customer’ mapped to Table ‘Customers’

Property ‘DateOfBirth (System.DateTime)’ mapped to Column ‘DateOfBirth (DbType.DateTime)’
Allow Insert: True
Allow Update: True
Is Identifier: False

Property ‘Forename (System.String)’ mapped to Column ‘Forename (DbType.String)’
Allow Insert: True
Allow Update: True
Is Identifier: False

Property ‘Id (System.Int32)’ mapped to Column ‘Id (DbType.Int32)’
Allow Insert: False
Allow Update: False
Is Identifier: True
Identifier Strategy: DbGenerated

Property ‘Status (ConsoleApplication5.CustomerStatus)’ mapped to Column ‘CustomerStatusId (DbType.Int32)’
Allow Insert: True
Allow Update: True
Is Identifier: False

Property ‘Surname (System.String)’ mapped to Column ‘Surname (DbType.String)’
Allow Insert: True
Allow Update: True
Is Identifier: False