Monday, December 07, 2009

Visual Studio 2008 SP1: LINQ to SQL with SQL Server 2008 Date Time Types

One of the pillars of Visual Studio 2008 and .Net Framework 3.5 Service Pack 1 is the support for SQL Server 2008 in Visual Studio 2008 tools and the related data access technologies. I decided to check out how LINQ to SQL works with SQL Server 2008 Date and Time types in Visual Studio 2008 and .Net Framework 3.5 Beta.

I created a simple database with a single table called friends, that holds my friends names and birth dates in several formats according the existing and new types.

create table Friends


    FriendID int identity(1,1) not null primary key,

    [Name] nvarchar(50) not null,

    BirthDate date,

    BirthTime time,

    BirthDateTime datetime,

    BirthDateTime2 datetime2,

    BirthSmallDateTime smalldatetime,

    BirthDateTimeOffset datetimeoffset



When I dragged the Friends table from the Server Explorer to the OR/M Designer, I got a visual representation of the Friend class, with types corresponding to the table columns. This is the columns types mapping:image

Column Server Data Type CLR Type
BirthDate Date System.DateTime
BirthTime Time System.TimeSpan
BirthDateTime DateTime System.DateTime
BirthDateTime2 DateTime2 System.DateTime
BirthSmallDateTime SmallDateTime System.DateTime
BirthDateTimeOffset DateTimeOffset System.DateTimeOffset


Working with this class was just as usual as with any other type, both for querying and for updating. The result was a GridView full with details of this table:


No comments: