Mapping database value to TimeSpan using dbml
I need to store the time offset in a database column (e.g. 12:25 AM, just time, no date).
I would like to use the good visual data modeling capabilities in Visual Studio 2008 to generate dbml and code for this. Database - Sql Server 2005.
Since the TimeSpan is essentially an Int64 (aka long) containing the number of ticks, I tried to store this in the database for a long time.
If, however, I specify that the column should display a property of type TimeSpan, Visual Studio reports:
Warning. Failed to execute custom tool "MSLinqToSQLGenerator". Unspecified error
To get around this, I mapped the database value to an intrinsic property and I added a partial class that implements property attributes that convert an integer value from database to TimeSpan and back.
Is there a way to include the correct code for this property? Or is it better to store the timeline in a SqlServer 2005 database which allows for easier access with dbml?
a source to share
Also ran into this problem a while ago.
As I understood, there is no direct permission from SQL Server 2005
, so I used the trick:
there is a column Duration
(actually needs to store the .NET datatype TimeStamp
) in the type database BigInt NOT NULL
. Here is the dbml definition:
<Column Name="Duration"
Member="DurationInternal"
AccessModifier="Protected"
Type="System.Int64"
DbType="BigInt NOT NULL"
CanBeNull="false" />
I have set this property of the class to protected
, which means that I don't want the user of my class to access this property directly and make a wrapper public
for that property in a 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
Thus, the user of my class is a simple-to-use property, but not a lot long
- TimeSpan
.
Hope this helps.
a source to share
I've tried this before, with epic failures. I found that even though I was not using the Date part of the DateTime in the database, I found that sending the entire date and time to the database allowed me to provide all the functionality at the right time.
You can use ToShortTimeString () to only display the time in the application. Having a complete date with time allows you to perform real-time calculations if you ever need to do this, such as determining the time interval between start time and stop time. Since the ticks associated with a time interval include a date, there shouldn't be any reason to get past the additional problem to just keep the time in the database.
I know this is actually not what you were hoping for, but it is probably the simplest solution for your needs, and this is due to me banging myself in the head trying to do the same.
a source to share
You can map the TimeSpan datatype to the 'Time' Sql datatype (thus avoiding any nasty implementation issues). However, the following requirements must be met:
- Your codebase should be running .Net 3.5 sp1
- Sql database must work on Sql 2008 (TIME datatype is only available in 2008).
For clarification read the following: http://msdn.microsoft.com/en-us/library/bb882662.aspx
a source to share