.net – Using Dapper-dot-net, how to map SQL Time column to a .Net type

dappernettsql

I have an existing database that uses the SQL time(7) type, which does not map directly to a .Net type.

Question: Using Dapper, how do I map a SQL time column to a .Net type?

Issue: When I attempt to implicitly map a SQL time(7) column to DateTime using Dapper-dot-net, I get the following exception:

An exception of type 'System.Data.DataException' occurred in
Dapper.dll but was not handled in user code

Additional information: Error parsing column 5
(CheckInTime=08:54:43.1470000 – Object)

I assumed it would map to DateTime automatically, but it does not appear to do so

Best Answer

I've had some success mapping SQL time(7) columns to System.TimeSpan.

As Dan pointed out, a TimeSpan is "semantically" less appropriate if your intent is to represent a time of the day.

But I think that, technically, there is no loss of information going from time(7) to TimeSpan, so the data is there, and it's a matter of meaning.

One way I've been using to alleviate the ugliness is to use a private TimeSpan property just for the sake of querying and expose the data in a custom type public property.

I prefer that over using a DateTime in combination with the call to CONVERT() in the SQL, because even with the DateTime you still have a somehow "imperfect" representation of you data (the date part is bogus) and you introduce ugliness in the SQL as well.

But, at this point, I think it can be considered a matter of personal preference.

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Best Answer

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

Related Topic