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

12 במאי 2008

no comments

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


)


You can download the SQL 2008 Friends Schema.


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:


image


LINQ to SQL support for  Table Valued Patameters


When I tried to drag a stored procedure that takes a table valued parameter and inserts multiple rows from it in a single db access, the designer has created a corresponding method that takes a System.Object type. When I saved the model to work with this method, I got the following compilation error:



DBML1005: Mapping between DbType 'Structured' and Type 'System.Object' in Parameter 'FriendsTable' of Function 'dbo.FriendsInsertMany' is not supported.


I am not sure whether the LINQ to SQL will support Table Valued Patameters in the RTM of the service pack.


Enjoy!

Add comment
facebook linkedin twitter email