SafeDataReader and Sql 2008 new datatypes + nullables

SafeDataReader and Sql 2008 new datatypes + nullables

Old forum URL:

xal posted on Tuesday, June 14, 2011

Hi Rocky,

Would you consider adding getters for the new datatypes in sql 2008 (DateTimeOffset, TimeSpan, etc...) to the SafeDataReader?

Also, at this stage, it wouldn't be bad to add nullable support as well.

I understand that the safedatareader works against a standard interface that doesn't have those getters, but considering it already supports a non-standard SmartDate I don't see why we couldn't have this better, up to date support for these features.

It could easily detect in the constructor if it's a sqldatareader, set a flag and if it's not you'd get a not supported exception when calling those methods.

The original purpose of the SafeDataReader was to handle null values gracefully (by getting rid of them), back when nullables weren't supported in .net. But today they are fully supported, so having a GetNullableInt32, would fit nicely into the "handling gracefully philosophy".



RockfordLhotka replied on Wednesday, June 15, 2011

That would tie the type directly to SQL Server though right? So it wouldn't work against other IDataReader types? I won't do that - I personally use it against non-SQL data reader sources...

The only feature you are really looking for (I am guessing) is the name/ordinal conversion so you can do dr.GetInt32("column") instead of dr.GetInt32(5) right? Especially if you are using nullables?

I am not against the idea of a more flexible implementation of a "safe data reader" family of types - a base type that only does the name/ordinal conversion, then a de-nulling subclass that does what the current one does, and maybe a SQL-specific type with these other database types.

I can add this to the wish list, but without someone contributing the work, I can say that it probably won't happen any time soon.

xal replied on Wednesday, June 15, 2011

It's not just about the name/ordinal conversion. If you attempt to get a nullable Int32, the current function returns just Int32, not Int32?, plus if the value is null, it actually returns DBNull.Value instead of null. So when you're reading the column value, you'll get an exception.

So in short, this is what I'm proposing gets added (aside from the sql specific types).


        public virtual int? GetNullableInt32(int i)
            if (DataReader.IsDBNull(i))
                return null;
                return DataReader.GetInt32(i);


I've been working on such a change based on your comments and I now have the following structure:

public class SafeDataReaderBase<T> : IDataReader where T : IDataReader

This is the old safe datareader, unchanged, except for the type definition.

NullableSafeDataReaderBase<T> : SafeDataReaderBase<T>

This implements the GetNullableXYZ methods.

SafeDataReader : NullableSafeDataReaderBase<IDataReader>

No code in this one, just leaves SafeDataReader for anyone to use as before, plus adding support for nullable types.

This could also be changed into 2 classes: SafeDataReader that inherits from SafeDataReaderBase and NullableSafeDataReader that inherits from NullableSafeDataReaderBase, but I don't see any real benefit from doing this.

SqlSafeDataReader : NullableSafeDataReaderBase<SqlDataReader>

This initially has support for just DateTimeOffset and TimeSpan and their nullables.

Would you consider incorporating such changes?

RockfordLhotka replied on Wednesday, June 15, 2011

Is there any value in having this be a generic type? The base code won't ever use anything beyond what is defined in IDataReader right?

I think the base type should be called DataReader<T> - it is directly useful for the ordinal/name conversion, and that's a common request, so naming the type something that makes it sound directly useful is good.

I also think this new DataReader<T> class should implement the GetNullableXYZ methods. It seems to me that they are broadly useful as well, and don't cause any issues for general use?

Then have a SafeDataReader<T> to add the de-null functionality.

Then SafeDataReader can subclass SafeDataReader<T> for backward compatibility.

The result would be three types:

Or we can drop the generics - unless there's a compelling reason where they add value?

xal replied on Wednesday, June 15, 2011

Sorry, I just saw this response. I'll send you a revised version with the suggestions.

The generics is helpful when working with the specific datareaders, for instance, if you create a MySqlSafeDataReader, the protected "DataReader" property will reflect the correct type and you won't need to do casting or store the reference to the datareader again. It's a small benefit but a benefit nonetheless.


Copyright (c) Marimer LLC