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 ?

Bambibambie

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).

Conversable

Answer 2

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).

Ghat

Both yours and Ken's solutions are good. However, if the value is null, Ken's solution's exception message is more descriptive. SqlNullValueException - Data is Null. This method or property cannot be called on Null values VS InvalidCastException - Specified cast is not valid. – Strephon

To answer BishopBarber comment if the column can be nullable you should convert it to TimeSpan? nullable type and check for null. TimeSpan? span = reader["tsfield"] == DBNull.Value ? (TimeSpan?) null : (TimeSpan?) reader["tsfield"] – Heterotaxis

I found this solution better because I often don't access my data reader by index. I do however have an intermediate method that checks for null. – Lindgren

Answer 3

Here's my take:

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

Cleaner, perhaps!

Ludeman

The database layer throws an InvalidCastException for me when it returns a TIME type but I try to read with GetDateTime(). – Romeu

Answer 4

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);

Saprogenic