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

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