How to Convert Datareader Result of DbType.Time to Timespan Object?

Question

I am reading a result from a MS SQL 2008 Database with a column type of dbtype.time from a datareader, using c# with DAAB 4.0 framework.

My problem is the MSDN docs say dbtype.time should map to a timespan but the only close constructor for timespan I see accepts a long, and the result returned from the datareader cannot be cast to a long, or directly to a timespan.

I found this Article whichs shows datareader.getTimeSpan() method, but the datareader in daab 4.0 does not seem to have this method.

So how do I convert the result from the datareader to a timespan object ?

Answer 1

GetTimeSpan is a method of OleDbDataReader and SqlDataReader (but not of the more generic IDataReader interface which DAAB's ExecuteReader returns). I'm assuming that the IDataReader instance which DAAB has returned to you is actually an instance of SqlDataReader. This allows you to access the GetTimeSpan method by casting the IDataReader instance appropiately:

using (IDataReader dr = db.ExecuteReader(command))
{
    /* ... your code ... */
    if (dr is SqlDataReader)
    {
        TimeSpan myTimeSpan = ((SqlDataReader)dr).GetTimeSpan(columnIndex)
    }
    else
    {
        throw new Exception("The DataReader is not a SqlDataReader")
    }
    /* ... your code ... */
}

Edit: If the IDataReader instance is not a SqlDataReader then you might be missing the provider attribute of your connection string defined in your app.config (or web.config).

Answer 2

Here's my take:

using (IDataReader reader = db.ExecuteReader(command))
{
    var timeSpan = reader.GetDateTime(index).TimeOfDay;
}

Cleaner, perhaps!

Answer 3

What is the .NET type of the column value? If it is a DateTime then you can pass the value of its Ticks property (long) to the TimeSpan constructor. E.g.

var span = new TimeSpan(colValue.Ticks);

Answer 4

Have you tried a direct cast like this?

TimeSpan span = (TimeSpan)reader["timeField"];

I just tested this quickly on my machine and works fine when "timeField" is a Time datatype in the database (SQL).