Share via

DataSets and nullable types

  • Article

Let's say you want to have a DataSet with an Integer column, and this column can have no values, which for argument's sake we'll call NULL values (there is a whole argument about NULL really means, but we'll punt on that). The DataSet has been around since the first version of the .NET Framework, and this problem has always existed, as has it's solution, DBNull. This works ok, but leads to a bunch of ugly code like the following.

int value = -1; // or some const value that represents null

if (dataRow.IsNull("myIntColumn") == false)

{

    value = (int)dataRow["myIntColumn"];

}

Fast forward to the current day. We've had nullable types for a couple of years now, so you can now write the following code.

int? value = null;

if (dataRow.IsNull("myIntColumn") == false)

{

    value = (int?)dataRow["myIntColumn"];

}

While we have nullable Integers, we’re still left with a bunch of ugly code that we shouldn’t need to write. However, if you are using Visual Studio 2008, you do have a new option, the Field<T> method that lives in the new System.Data.DataSetExtensions assembly. With this method, you can specify the type, and for nullable types, we handle the null conversions for you.

int? value = dataRow.Field<int?>("myIntColumn");

Does that mean we’re done, and we don’t need true nullable columns? I don’t think so, but we just finished Visual Studio 2008, so please stay tuned, and if this is an important feature to you, please let me know.

Comments

  • Anonymous
    May 16, 2008
    People have been crying out for this for ages!  Having true nullable columns would be such an awesome feature and reduce the amound of tedious and unnecessary plumming work no end. If you're worried about backward compatability issues, even if you could add a property each data column that we could manually set to specify it can be nullable that would be great. Is there any chance you could sneak this into SP1? :-)

  • Anonymous
    May 16, 2008
    The comment has been removed

  • Anonymous
    May 20, 2008
    No, Field<T> doesn't fix the problem.  Yes, it's nice that I can write:   int? value = r.Field<int?>("Column") instead of:   int? value = (r.IsColumnNull()) ? null : r.Column; But while using the first formulation is marginally less code, it's not less work.  Because now I've replaced something verbose that gets checked at compile-time with something terse that doesn't. The whole point of using typed DataSets is that they push checking data types and column names down to the compiler.  This fixes entire classes of software defect the best way possible:  by not allowing them to be made in the first place. I for one would sure like it if the typed DataSet had a UseNullableTypes property, which told the generator to a) create columns of Nullable<T> when AllowDBNull is true, b) to make the property accessors handle the null-to-System.DBNull.Value mapping for nullable columns and c) to stop generating the IsColumnNull() and SetColumnNull() methods.   That would fix everything.  It wouldn't break existing code unless the developer chose to set the property.  It would make using nullable columns as idiomatic in .Net as it is in SQL Server.  (Well, almost; it'd be nice if Nullable<T>.GetValueOrDefault() had been named IsNull(), but what are you gonna do?)  It would reduce the complexity of all code that deals with nullable columns of typed data sets.  Those are all upsides, and I can't see a downside, except that someone has to bell the cat.

  • Anonymous
    May 28, 2008
    The comment has been removed

  • Anonymous
    June 21, 2008
    Any updates on this? Im now in a project using both linq and typed datasets... i must have over a thousand lines of IsColumnNull() checks, and yet i still get the occasional exception thrown on some that were missed. I may need therapy after this. Can I pay one of your developers to spend the 15 minutes needed to fix this?

  • Anonymous
    June 30, 2008
    I posted my gripes about this problem via the blog link. Please guys, this is a daily headache for me, just get it fixed!

  • Anonymous
    June 30, 2008
    One more comment - Erick, can you let us know if there are any plans for this being fixed? I'm considering spending the time and building a tool to produce nullable ST datasets for me if MS has no plans to address it, but I don't want to do this and have it show up from MS a week later. Thanks!

  • Anonymous
    July 10, 2008
    While your at it how about an Add<ColumnName>Row method that has nullable parameters? At the moment if you want one of your columns to be null then this method is completely useless. The alternative of creating a NewRow and populating each field individually is very long winded. It also means that if you add a new column to the data table that is not nullable then you'll only find out that you've forgotten to update the code that adds the row at run-time. If you could use the Add<ColumnName>Row method then this would be pickup by the compiler straight away. How about releasing the Typed Dataset Designer code so that we can modify it to our hearts content? :)

  • Anonymous
    July 11, 2008
    Yes, it's VERY strange that we have no ability to set column type as int? not int. Please, fix this, 3 years passed since we have nullable types and still typed datasets don't support them properly.