Mapping a database value to a TimeSpan using dbml

Question

I need to store a time offset in a database column (for example, 12:25 AM, just the time, no date).

I would like to use the nice data visual modeling capabilities in Visual Studio 2008 to generate dbml and the code to go with it. The database is Sql Server 2005.

Since a TimeSpan is essentially an Int64 (aka long) containing the number of ticks, I tried to store that long in the database.

If however I specify that the column should map to a property of type TimeSpan, Visual Studio reports :

Warning : The custom tool 'MSLinqToSQLGenerator' failed. Unspecified error

To work around this I've mapped the database value to an internal property and I've added a partial class that implements property accessors that convert the integer value from the database to TimeSpan and back.

Is there a way to enable the correct code to be generated for this property? Or is there better way to store a timespan in a SqlServer 2005 database that allows easier access using dbml?

Answer 1

I have tried to do this before, with epic failures. I found that even though I am not using the Date part of the DateTime in the database, I discovered that submitting the entire Date and Time to the database allowed me to provide all the functionality with the Time i needed.

You can use the ToShortTimeString() to display just the time in your application. Having the full date in with the time allows you to do true time calculations if you ever needed to do that, such as determine the timespan between a start time and stop time. Because the ticks involved with the time span include the date, there should be no reason to go through the extra trouble to just store the time in the database.

I know this is not really what you were hoping for, but probably the easiest solution for your needs, and this comes from hours of banging my head experience in trying to do the same thing.

Answer 2

You can map the TimeSpan data type to the Sql 'Time' data type (thus avoiding any nasty implementation issues). However the following requirements must be met:

1. Your codebase must be running on .Net 3.5 sp1
2. The Sql Database must be running on Sql 2008 (the TIME datatype is only available in 2008).

For clarification read this: http://msdn.microsoft.com/en-us/library/bb882662.aspx

Answer 3

Also faced this problem some time ago.

As I understood there is no direct resolution with SQL Server 2005, so I used a trick:

there is Duration column (which actually should store a .NET TimeStamp data type) in the database of type BigInt NOT NULL. Here is dbml definition:

<Column Name="Duration" 
    Member="DurationInternal" 
    AccessModifier="Protected" 
    Type="System.Int64" 
    DbType="BigInt NOT NULL" 
    CanBeNull="false" />

I set this class property as protected which mean I don't want the user of my class access this property directly and made a public wrapper for this property in partial class:

    #region Properties
    /// <summary>
    /// Gets or sets the audio duration.
    /// </summary>
    /// <value>The duration.</value>
    public TimeSpan Duration
    {
        get
        {
            return TimeSpan.FromTicks(this.DurationInternal);
        }
        set
        {
            this.DurationInternal = value.Ticks;
        }
    }
    #endregion

So the user of my class has a easy-to-use property instead of a lot of long to TimeSpan conversions.

Hope, this helps.