You can have strongly-typed DataTables, or you can have nullable columns. Choose one.

If it's blowing up on the line where you do "Console.WriteLine" then that really isn't a problem. That doesn't mean that the column isn't allowed to be null, it just means you can't try to get it's value if it is null. Before utilizing the value in that column, you should check the "IsBirthDateNull" method to see if it is null or not-null, then act on that accordingly in your code (i.e. don't reference that column's value if it's null). If you don't see a "IsBirthDateNull" method on your datatable, then go back into the designer and set the "BirthDate" column's "AllowDBNull" property to "true", recompile, and check for that method again.

I think I know why (I've had the same problem):

Code Snippet

public decimal UnitValue {
get {
try {
return ((decimal)(this[this.tableDocumentLineKit.UnitValueColumn]));
}
catch (System.InvalidCastException e) {
throw new System.Data.StrongTypingException("The value for column \'UnitValue\' in table \'DocumentLineKit\' is DBNull.", e);
}
}
set {
this[this.tableDocumentLineKit.UnitValueColumn] = value;
}
}

The above is an example of how the strongly typed dataset works.
If you try to access a null value in the data set, it throws the exception. The reasoning is not clear, but if you change your code to the following it might help you:

Code Snippet

Console.WriteLine(r.IsBirthDateNull?new System.Date():r.BirthDate);

If you only want to display the value, you can cast the BirthDate as a string in your SQL query (yes, this is stupid too). I had a chance to work with a veteran of the .NET programming field. He mentioned wrapping the table adapter classes. I know... it should just work in the first place.

Another possibility is to write a simple utility class that converts all DbNull to null, i.e.

public object NC(object input)

{

if (null == input)

return null;

else if (input.Equals(DBNull.Value))

return null;

else

return input;

}

Wow, that's phenomenal. Thanks to ARK88 for what I guess is the right answer.

I mean, it's the the right explanation of the wrong answer. The right answer is that the properties for nullable columns should be of a datatype that can include null values. Why would you go to the trouble of defining DateTime? and int? and Decimal? types, use them in the parameter lists of the methods that wrap parameterized queries and stored procedure calls, and then, when the time comes to write the code generator for the strongly-typed DataTables, say "Hey, I know, let's generate a special utility property for every nullable column!" Why? Why would you choose two distinctly different and incompatible mechanisms for the same thing, make it a fundamental part of your architecture, and then not even highlight it with red flashing lights in your documentation?

The root cause is certain scalar types in CLR are not nullable. For example you cannot say:

int x = null;

You can say of course:

Int32 x = null;

But in .NET 1.1 the language did not support NullableT so they went with the IsField1Null method, which I agree is troublesome.

The history is discussed here:

http://blogs.msdn.com/adonet/archive/2007/02/05/type-safety-linq-to-datasets-part-2.aspx

"When the DataSet was first created, there was no concept of nullable value types in the CLR, so a new value type was defined: DBNull. This was used to represent null values for DataColumns that contain a value type, because you could not have have a null value type. The world has moved on, and we now have nullable types, and so it is now much more natural to write a query using null, as opposed to having to check for DBNull everywhere. The other feature offered by the Field<T> method is that it will convert a value type that has a value of DBNull.Value to a nullable type with a value of null. "

Well aren't you a breathe of fresh air? You are correct about me being incorrect... I did actually answer the wrong question. Sorry for setting myself up to be the butt of your sarcasm... I promise it won't happen again.
Sorry if I was unclear - I intended no sarcasm directed your way. Your answer is completely correct, full stop.

The answer that Microsoft provides is wrong. It's them I'm mad at.

The link Matt provides explains the half-assed reasoning behind the half-assed implementation of this feature. When the DataSet was created, NullableT didn't exist. Fine. Yet, strangely, in .NET 2.0 the ADO folks found the time to extend the DataSet to use NullableT. Only, just some of the DataSet's features use it.

As it stands, if I have a table with a nullable int column, and I create a TableAdapter and DataTable from it, the DataSet designer creates an InsertCommand that takes a parameter of int?, and a property accessor on the column of type int that throws an exception if I set it to null. How can that possibly be the right design?

Any why on earth go to all the trouble to define an entire library of SQL data types and then not support them? Why is the choice between using strongly-typed DataSets and SQL data types mutually exclusive? Where's the sense in that?

So much of the design of ADO.NET is deeply smart. This is a glaring exception.

Sorry I misinterpreted your previous post (and your original question). I'm completely on your side regarding this matter... it doesn't make a whole lot of sense to me either.

Sorry for the half-a**d response.

Essentially this is a impedence mismatch between database types and CLR types. Happens all the time, not just with ADO.NET. Look at other APIs like JDBC, you have ResultSet.getInt(columnIndex) and ResultSet.wasNull(columnIndex) for example. Programming languages typically have simple scalar types that are not nullable, like int. All database types are nullable, including simple scalar types like int.

So your choices are either have an API that returns some generic object type that you have to cast to the scalar type or you can return the true scalar type and have a IsFieldNull check. Now with .NET 2.0, there is NullableT support, this is the language bending to meet the database types, allowing scalar type that is nullable.

Microsoft is working very hard to solve this impedence mismatch problem. Look at LINQ, NullableT, etc... We know this is frustrating for programmers and we are working to solve these problems.

Sure. I understand the problem.

But .NET 2.0 has NullableT. ADO.NET in .NET 2.0 uses NullableT. Why doesn't the strongly-typed DataTable? That just doesn't make any sense. Was it a backwards-compatibility question? Or is there some good reason I'm not seeing why NullableT's not an appropriate solution?

Really, I wouldn't be so het up about this except that I have this application I built using generic DataTable objects, and in the middle of a big refactoring effort I thought "I know, I'll use strong typing, that'll make everything easier," and in fact it's made everything harder. Harder in the "Now I get to find out how SVN's revert works" sense.

I will go ask the DataTable PM to see what he says.
This is something I hit when I was using typed datasets in .Net 1.1, it caused a bunch of issues with data-binding amongst other things. It's also something I hit when creating my own objects in .Net 1.1, and it seemed that the MS approach with objects was to have an "IsBlahPopulated" boolean for each property. Obviously very similar to what they did with typed datasets. I was very glad to see nullable types in .Net 2.0, in fact it's one of my favourite improvements.

I suspect that typed datasets haven't been updated to make use of the new functionality because MS is moving us towards Linq-To-Sql, and for good reason IMHO. You still get to generate the code, but you get proper objects that you can extend. If it's true that MS has or will be deprecating typed datasets (and I have no information on this) it may be worth you investigating Linq-To-Sql.

An alternative to Linq-To-Sql is my own product, Foundation. It's similar, but is geared more towards making applications easier to write, rather than just loading and saving data. It only requires .Net 2.0.

Regards,

Sean

Note I talked to the DataSet PM and he indicated that they considered using NullableT in 2.0 but the Nullable types were not ready during the time that they were working on the DataSet code in 2.0 timeframe (NullableT came in late).

They are looking at it for next release but I got no hard promises.

Thanks, Matt.