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

Question

I have a SQL Server database containing a table that includes smalldatetime and bit columns.  These columns are nullable.

I add a DataSet to my project, drag the table from the Database Explorer window to the DataSet designer surface, and in a matter of moments I have a shiny new FooDataTable class.  The BirthDate column, I see, has AllowDBNull set to True, and <DBNull> as its default value.  All is well.

I now execute this code:

            FooDataSet.FooDataTable t = new FooDataSet.FooDataTable();
            FooDataSetTableAdapters.FooTableAdapter a =
                new TestApp.FooDataSetTableAdapters.FooTableAdapter();
            a.Fill(t);

            FooDataSet.FooRow r = (FooDataSet.FooRow)t.Rows[0];
            Console.WriteLine(r.BirthDate);

This throws a StrongTypingException:

    The value for column 'BirthDate' in table 'Foo' is DBNull.

Well, yes, it's DBNull.  It's allowed to be DBNull.  It's expected to occasionally be DBNull.  That's why the DDL for the column reads

    BirthDate smalldatetime NULL

So okay, I just need to edit the DataSet so that it won't throw an exception if the column contains DBNull.  Not so fast!  The DataSet designer only lets you do that for columns of type System.String.  This column's of type System.DateTime.  And of course you can't set a System.DateTime variable to DBNull, or even null, since it's a value type.

Wait just a second there.  This is a SQL Server database.  SQL.  Of course.  I should be using System.Data.SqlDateTime as my column type.  Stupid Robert.  I'll just open up the DataSet designer and change the data type.  Funny that the designer didn't do that automatically. 

So I open up the designer, and...what the hell?  The only types it supports are system types?  I can't even type in System.Data.SqlDateTime?

And here my little story ends, with me sitting in a box canyon wondering if I'm going to hand-code my own strongly-typed DataTable class.  Please tell me there's a way to make use of this fundamental feature of all SQL databases in ADO.NET.

Answer 1

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.

Answer 2

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.

Answer 3

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;

}

Answer 4

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?

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

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.

Answer 11

I will go ask the DataTable PM to see what he says.

Answer 12

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

Answer 13

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.

Answer 14

Thanks, Matt.

Answer 15

 

ive been fighting this for so long.. its such a simple fix, if ms would just do it in a patch etc.. JUST allow settings a datatable to return null if the value is null, FOR OTHER TYPES other than just string! the nullable types all exist, in fact you can hand-edit the xsd file and make this work, but the designer doesnt let you. JUST DO IT please.. if i have to write another MyDataRow.IsColumnNull() im going to go nuts. my code is littered with these. let me return a nullable value of this type if it is null. Not that tought, guys..

 

ive even considered writing a post-processor to hack this on my xsd files and force it.. but that would be such a hack.

 

Answer 16

you can extract the interface from the typed datarow and change the nullable property to a nullable type. you need to implement the nullable property in a partial class of the data row, and use the extracted interface when accessing the columns.

 

Another approach is to write a MSDataSetGenerator replacement. Maybe you can simply create a prebuild event step that calls Xsd.exe /dataset to generate the designer.cs file and do some text replacing.

Answer 17

 

Thanks for the reply Sheng. Yes, i know there are a few solutions but again these are a lot of work and pretty hackish to correct something that the Dataset Designer could sooo easily implement. Even if i had to manually set a property on each column in the table designer to allow it to be nullable, it would save me so much effort...

 

I think the problem is that most people just use datatables for binding to grids etc... while I am using them as a way to access and manipulate data via code (non-bound). So while a datagrid knows to call IsColumnNull for each value it tries to bind, I have to manually call this for every since column access i do in my data processing code. I literally thing my code size for one of my apps would be 1/3 smaller if it didnt have all this extra junk in there...

 

so anyway, yes maybe ill try hacking together a custom datasetgenerator etc... and then enjoy the challenge of making sure any other developers who work on our software have this same generator installed. or just keep calling IsXXXNull until 2008 comes out which i HOPE has corrected this.

 

 

 

Answer 18

 

one more note, the other big headache with this is that it creates bugs that are not caught at compile time. It only crops up ar runtime if your code is missing an IsNull call, and then only shows if you run across a null value in a datatable. If they used nullable types, much of these issues would be caught at compile time (if not all?)

 

in fact, this is the #1 runtime error I encounter.

Answer 19

Oh, I wouldn't go that far.  If I don't remember to check IsColumnNameNull(), I'm probably not going to remember to check if int? i is null either.

Answer 20

 

Wrong - two scenarios with usin the nullable fields would be to 1. assign the value to some other nullable, in which case we dont care if its null or not (not possible with current impl ) or 2. Assign the value to a non-nullable, which forces you to use HasValue/Value combo or preferably the GetValueOrDefault method..This forces compile-time checking of null handling and wont allow a random unhandled null check throw an exception in your code, assigning a nullable to a non-nullable is not even allowed by the compiler.

 

 

Answer 21

Half a year later... is there any word on this issue related to .NET 3.5 and VS2008? A lot of our projects still use ADO.NET datasets and we can't sell migrating to Linq to our customers just because we like it. It would be great if the nullable support would be updated in SP1, but I'm not able to find any information on this.

 

If anyone knows something about this, please share it

Answer 22

 

Not speaking for MS, I think this wont ever change at this point so it's going to be a custom DataTable generator you will need to build, or switch to linq Im using linq now and so far really like it, but am still scared that the lack of alternative db dlinq implementations is going to bite me hard before long (yes DBLinq is out there, I havent tried it yet.. might be good enough for what I need... see linq.to for info)

 

Actually I woudl be interested in seeing if anyone else has implemented their own typed table adapter builders... and might be willing to share?

Answer 23

I sent another ping to DataSet PM, no response yet.  I asked him to respond to this thread.

 

 

 

 

Answer 24

 

Matt is correct in that nullable support didn't make it into 2.0, and just missed making it into 3.5. However, there are a number of alternatives of how to deal with null values. Please see the following blog post on the topic.

 

http://blogs.msdn.com/erickt/archive/2008/05/15/datasets-and-nullable-types.aspx

 

However, I totally understand that it's not a great solution, especially for Typed DataSets. I wish that we had a better solution today, but unless you are willing to work with the partial classes that the Typed DataSet designer generates, it's best that there is. If nullable types on a Typed DataSet is a useful feature, please comment on the blog, as community interest has a big impact on how we determine what work gets in future versions.

 

Thanks,

Erick

Answer 25

"If nullable types on a Typed DataSet is a useful feature"

 

Is that really in question?

 

If I can set the thing to NULLABLE and set its default value to NULL, then that thing must never, ever, ever, ever, ever, result in an exception when its value is set to NULL. That's just a bug. Regardless of how it came to be, it is a bug. It can be described as no other thing.

 

thing.nullablefield = otherthing.nullablefield;

 

That needs to work as transparently as any other assignment.

Answer 26

 

more buzz on the subject here http://barelygenius.wordpress.com/2008/07/01/nullable-datatables-still-missin/

 

 

Seriously guys... Let me contribute the fixes. I'll do it free. I've lost manmonths to this bug.

 

 

Answer 27

With the amount of requests for this, will this finally be resolved in Visual Studio 2010?

 

If this affects you, please vote for the issue on Connect if you haven't already done so:

 

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=105927

Answer 28

First off I'm sorry if I posted an answer that was not correct for what was asked.. Dataset has some difficulties dealing with these types because in V1 we returned DBNull and nullable types didn't exist. What I was trying to point out was that in Visual Studio 2008, when a typed dataset is created different code is generated based on how the developer wants to support nullable in the database.

 

Here is a code snippet of two properties, one that supports nullable and the other does not

 

Code Snippet

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

public string myString

{

get

{

return ((string)(this[this.tableTestTable.myStringColumn]));

}

set

{

this[this.tableTestTable.myStringColumn] = value;

}

}

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

public string myStringNullable {

get

{

if (this.IsmyStringNullableNull()) {

return string.Empty;

}

else {

return ((string)(this[this.tableTestTable.myStringNullableColumn]));

}

}

set {

this[this.tableTestTable.myStringNullableColumn] = value;

}

}

 

 

 

What I also missed with my example was that I happened to use a string which supports nullable, and not use int instead. I would have seen this. Here is a similar code snippet for how typed Dataset is generated for int

 

Code Snippet

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

public int myInt {

get {

return ((int)(this[this.tableTestTable.myIntColumn]));

}

set {

this[this.tableTestTable.myIntColumn] = value;

}

}

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

public int myIntNullable {

get {

try {

return ((int)(this[this.tableTestTable.myIntNullableColumn]));

}

catch (global::System.InvalidCastException e) {

throw new global::System.Data.StrongTypingException("The value for column \'myIntNullable\' in table \'TestTable\' is DBNull.", e);

}

}

set {

this[this.tableTestTable.myIntNullableColumn] = value;

}

}

 

 

 

DataSet does not support nullable types in the Row setter, but it does have some support that was added in 3.5 for nullable types. However this is not codegenerated by the typed dataset code. You would have to change it yourself as Matt has indicated.

 

In system.Data.DatasetExtensions dll we have Field and SetField

Code Snippet

int? val = row.Field<int?>("datacolumnName");
row.SetField<int?>("datacolumnName", val);

 

 

This will do the right thing with nullable.

 

Also can someone  please mark the specific post with the answer, sorry again for not giving the right information.

 

Thanks

Chris Robinson

Program Manager- DataSet

Answer 29

Hi Chris,

 

What we're saying is, we want the DataSet designer and generator to directly support nullable types.  In your example above, we want the myInt column property to return "int?" and to *not* throw an exception if it is set to null.

 

Regarding the workaround of using...

Code Snippet

int? val = row.Field<int?>("datacolumnName");
row.SetField<int?>("datacolumnName", val);

 

 

... this really isn't a great workaround because now we're back to using strings and have lost compile time type checking, which is the whole point of having a typed dataset in the first place!

 

Please read the comments in response to Erick Thompson's blog post on this issue:

 

http://blogs.msdn.com/erickt/archive/2008/05/15/datasets-and-nullable-types.aspx

 

Erick also mentions earlier in this thread that nullable support "didn't make it into 2.0, and just missed making it into 3.5".  So the big question is - will this finally be addressed in .NET 4.0?  We've all got our fingers and toes crossed here :-)

Answer 30

 

...and now im looking at the entity framework as my next alternative to typed datasets... maybe datasets will just wither up and disappear.

 

I can tell from the MS posts on here that it'll never be fixed. The scary part is, most of the MS repliers on this post apparently don't even understand what we're asking for. So how the heck can we expect it to be fixed.

 

Let me try one last time - *** Strongly Typed NULLABLE Datasets ****

 

And let me repeat - row.Field(<int?>("columnname")  IS NOT strongly typed. I dont want to use a string name to reference the field Im after.

 

I just want to add my nullable int field to the strongly typed dataset, and then be able to:

 

int? myInt = row.myIntField; 

 

and not have it be a ticking timebomb on my hands.

 

 

Answer 31

 

I'd love to see a resolution to this problem.  I have to designate my integer columns as strings in my datasets in order to support the NULL database values.

 

All these workarounds take away from the ease of creating typed datasets in Visual Studio.  If I start adding all this custom code to all my integer columns I might as well just create all this stuff manually...

Answer 32

I pinged the DataSet team for a response.

 

Answer 33

There are two components here, DataSet and Typed Dataset. I own the untyped Dataset and Young Joo owns the Typed Dataset. From the discussion it is clear that there is a major ask for a Typed Dataset that supports nullable types. In fact there is a connect bug tracking this specific ask. It is located here

 

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=284009

 

Please go to this url and vote that this is a very important feature to you. We view connect feedback as very important piece of data when considering which features to add in to an existing component. When Young comes back from paternity leave I will specifically raise this issue with him and we will discuss our options.

 

If anyone wants to have a further information please feel free to email me.

 

Thanks

Chris Robinson

Program Manager - Dataset

Answer 34

This connect bug was set to "closed" earlier this year.

Answer 35

I should have anticipated this, I have created a new connect bug that is opened for people to vote up.

 

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=383175

 

Thanks

Chris Robinson

Program Manager - Dataset

Answer 36

Thanks, Chris. As others have said, DataSets are some of the grooviest code in all of .Netland and it's frustrating to encounter such a stumbling block.

 

I just don't understand why so few of us are clamoring for the fix. All you have to do is set a System.Int32 column to "AllowDBNull=true", and I dare you to set the "NullValue" property to anything other than "Throw exception".

 

Clearly, a nullable type needs to accept an assignment of null without detonating.

 

Makes you wonder what the heck everyone else is doing that they haven't noticed and/or mentioned it.

Answer 37

 

Scott, I'm guessing that everyone is dragging a grid into a webform and attaching to dataadapter and wham, you have a web app. The adapters and binding logic hides most of this (calls isnull/setnull behind the scenes), so it's just us "weirdos" that try to use them for for something more than bare basic UI stuff that run into the problems.

Just a guess. But, does make you wonder.

 

Answer 38

FINALLY - A topic on this problem! This has been bugging me for ages, please resolve this, its massively inconvenient and I'm on the verge of getting rid of strongly typed datasets entirely

Answer 39

The check that prevents 'null' from being assigned to a column with a value type was my fault.  That check was put in place before ((object)default(int?) == null) was true for Nullable<T> during development of V2.0.

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 40

Hi Mark,

Thanks for the info.  The Connect issues (of which there are many!) for this issue keep getting closed with promises of "next release", or "just missed out making it into the upcoming release" and nobody seems willing to take charge of this and get it resolved.

Bearing in mind that he who fixes this shall surely gain eternal glory and the praise of developers across the globe, are there any plans for .NET 4.0 / VS2010? :-)

-Dan

Answer 41

So from what I'm reading here it seems to me that MS is not going to patch the DataSetGenerator so that it will recognize nullable types having a NULL value as being valid.

The other part I'm not liking is that it was mentioned that this issue does not exist in Linq.  So, unless I'm missing something, it would seem that MS is hinting that Linq will eventually replace ADO?  As powerful as Linq is, to me it's just a reinvention of the "ADO wheel."

Answer 42

Just to repond to what some people have been saying about DataSets being a redundant technology - I don't think this is really the case.  LINQ to SQL and the Entity Framework are *alternatives* not direct replacements.  DataSets are so widespread that they're going to be in the framework for a long, long time.  For example, out in the real world:

• Many applications have data access layers based on DataSets.
• Sometimes web services return DataSets (including typed DataSets).  Sometimes these services aren't even within your control so changing them is impossible.
• Many applications contain reports that are linked to DataSets.  It's quick and easy to link up reports this way rather than generate models for such things.
• Many components and third party controls have dependencies on DataSets.

I'd love to move completely over to the Entity Framework, but even that is just in its infancy and still evolving (although v2 looks like it's coming along nicely!).  There's just so much existing code out there that relies on DataSets that it's going to be a lot longer than many think before DataSets are marked as obsolete and eventually removed.

I really just wish we had true nullable columns at the peak of DataSet's popularity instead of all this pain.  It's been one massive battle to get support for this since 2004.

Answer 43

I don't know if this is a related issue. I don't think I am trying to do anything as hardcore as most of you, but I am having some weird issues with making parameters for my stored procedures nullable. I am sort of a noob to using strongly typed datasets, so forgive me if this is a completely different issue.

Here is a link to a post on the asp.net forums with some screenshots of the issue im having. My Post

Basically, I just want to know why Visual Studio is setting some of these parameters in the get and fill methods as nullables and others not, or if I am implementing this in the wrong way. So far for me, I cant seem to get these parameters nullable if they are strings, but if they are int's than they're nullable. All of the parameters for the store procedure have AllowDBNull = true but only the int parameters show up as nullable in the methods. Doesn't make sense to me.

Answer 44

Not sure. I am just trying to read through this whole topic which seems to date back 2 years. A string type is allowed to be set to null but not DBNull.Value. As for the nullable types they are set as nullable because by default they are not allowed to be null. For example this you cannot do:

int a = null;
int b = DBNull;
String s1= DBNull.Value;

But this you can do:

Nullable<int> c = null;
Int32 d = null;
String s2 = null;

I don't know if this will help but its all I got at the moment. There seem to be a lot of problems translating between MS SQL Server datatypes and the System data types used by the strongly typed datasets. There are System.data.SqlTypes which match what MS SQL Server uses. However, for some reason these are not the data types used in the strongly typed datasets...  Seems silly to me. There are simple conversions between the SQL types and the standard data types.

Answer 45

And here we are in march 2010.

Anyone tried out 2010 RC yet to see if it's resolved?

Im back on another legacy application project and fighting this issue once more.

It's so silly.

Johnathan - go create a strongly typed datatable with an int column, and one which is nullable in the database.
Then in the designer, go change the property setting on the int column called "NullValue" - try changing it from (Throw Exception) to one of the others, specifically "(null)". The designer will pop an error saying this is an invalid option for int columns blah blah.

THIS is specifically the problem this thread is discussing.

Answer 46

Yes I just tried out the " Version 10.0.30319.1 RTMRel " (VB project with .NET 4) and it acts exactly like VS 2005.

 

On another note, who knows how to use the generated ".Is*Null()" methods properly?

Mine always return false.

I think it's because my values are System.DBNull.Value, but [based on looking at the corresponding "Set*()" method] it is comparing against Global.System.Convert.DBNull (whatever that is).

 

 

  
  <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()> _
  Public Function Iser_corp_charge_dateNull() As Boolean
   Return Me.IsNull(Me.tablealer_expense_export_header.er_corp_charge_dateColumn)
  End Function
  
  <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()> _
  Public Sub Seter_corp_charge_dateNull()
   Me(Me.tablealer_expense_export_header.er_corp_charge_dateColumn) = Global.System.Convert.DBNull
  End Sub

 

Answer 47

How on earth is this not fixed yet?

 

The framework totally supports nullable types and yet this remains an issue. I first encountered it about 3 years ago in VS 2005 and eventually decided to just not use nullable database columns.

 

Just tried in VS 2010 and this bug is STILL present?! Unreal.

 

Guess it's back to using time-sucking workarounds...

Answer 48

And here we are in march 2010.

Anyone tried out 2010 RC yet to see if it's resolved?

Im back on another legacy application project and fighting this issue once more.

It's so silly.

Johnathan - go create a strongly typed datatable with an int column, and one which is nullable in the database.
Then in the designer, go change the property setting on the int column called "NullValue" - try changing it from (Throw Exception) to one of the others, specifically "(null)". The designer will pop an error saying this is an invalid option for int columns blah blah.

THIS is specifically the problem this thread is discussing.

I am sure you have given up. I guess we have to change all our datatype on our xsd to String.

I am still using .net 2.0 VS 2005, and haven't touched 3.0, 3.5 or 4.0. I have a feeling datasets will eventually die in the new MS .net in 2+ years time.

Just like we still have legacy systems in asp and vb6.0, but not supported that much anymore.

Answer 49

Hi guys, just to a quick update from my experience - nullable integers in a database table are a no-no, integer fields should always default to 0.

 

Going forward, I've dropped table adapters completely. I've now written a custom (and very simple) DAL which just calls stored procedures and I then use the returned DataTables to populate by system classes.

 

TableAdapters and DataSets have caused me nothing but headaches and I wish I never touched them in the first place.

Answer 50

cusimar, nullable fields of all kinda are a fact of life, whether you like them or not. And since non-string fields have the possibility of being nullable, so should datasets (or rather, st datatables)

Answer 51

Ok question - since MS will never fix this, would there be enough interest out there if I put together a solution for this?

Answer 52

I was having a similar problem this morning trying to do a tableadaptor.Insert from strongly typed datarow values.

I figured out a solution to my problem.

http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/thread/16a42de5-2c9d-449a-8e86-692ed37b16d1

Another solution I found was here in the first post which worked perfectly....That is, until I made a change to the dataset and it all reverted to the old code. Not something I prefer to keep updating over and over....

http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/827c6bdb-c87c-44a0-8631-bae4b5a3c562/

I just wish MS would change the dataset to process like the second link and we would all have no more problems...

Answer 53

Ok question - since MS will never fix this, would there be enough interest out there if I put together a solution for this?

Have a look here, down near the end of the thread:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/407cca6d-2364-47ca-821e-77a1cad574cf/

Chris has been working on a T4 template which should give us the flexibility we need in order to generate DataSets with proper nullable columns.  Maybe if Chris is able/willing to release the template or put it up on CodePlex or something, we could lend a hand.

Answer 54

I have had some issues with the winform databinding of strongly typed datasets that had a typed datatable with nullable columns.  Found the only way around the issues with DBNull was to not use the winform design type binding with BindingSource and to just handling it all in code.

Environment:
.NET 3.5
SQL Server 2005
WinForms
Typed DataSets
BindingSource

Created a strongly typed dataset with a typed DataTable that has some columns where
AllowDBNull=True and its for various DataType such as String, Boolean, Int, Decimal and DateTime.
NullValue set at "Throw exception" which is the default setting.
Other settings are "null" or "empty" and only applies when DataType is String.
Try setting "null" for Decimal or Boolean and get an error about not allowed to do that.

The problem as several other developers have run into is that using the BindingSource and
winform controls to bind to various typed columns, causes problems when value is DBNull.

The solution seems to be to forget about using BindingSource in the winform design mode.
Use the code-behind class of the winform to "manually" handle the binding to the control;
taking care of setting and reading from the control yourself.

Example c# code:

private void RollYourOwnBinding()
{
      //clear all controls we bind to -- set default values
      this.TxtAmount.Text = string.Empty;

      this.DtpEndDT.Value = DateTime.Today;
      this.DtpEndDT.Checked = false;

      this.ChkIsPercentage.Checked = false;


      dsTypedDataset.TypedRow row = FindRow(id);
      if (row != null)
      {
	      if (!row.IsAmountNull())
	      {
	        this.TxtAmount.Text = row.Amount.ToString();
	      }
	      if (!row.IsIsPercentageNull())
	      {
	        this.ChkIsPercentage.Checked = row.IsPercentage;
	      }
	      if (!row.IsEndDtNull())
	      {
	        this.DtpEndDT.Value = row.EndDt;
	        this.DtpEndDT.Checked = true;
	      }
      }
}

 

Answer 55

(I know this is old thread but...)

with .net 3.5 you use global::System.Nullable<type>

like global::System.Nullable<Datetime> as argument  whenever you see type? type paramenter such as DateTime?

 

of course you may still have to check if the result is null and  handle accordingly

Answer 56

I'm using VS 2010 and .Net 3.5.  Still having the DBNull problem, don't know how to solve it.

I have a WinForm application, I'm doing as much as I can using out-of-the-box, MS code-generated, typed dataset.  So I dragged my SQL Db View onto the dataset layout screen, went through the configuration, I let VS create the 'Fill' and 'GetData' methods. 

In my code I did this:

myTableAdapter.Fill(myDataSet.Myiew);

dataGridView1.DataSource = myDataSet.Myiew;

That's it.  My db view contains columns of type "DateTime".  When I run the app, and step through with the debugger, I get an error thrown from the myDataSet.Designer.cs code for that column.  The auto-generated code is attempting to cast the column to System.DateTime, but it is failing, of course, because the value returned from the Db is null. 

Is there something I need to set somewhere to allow DBNulls to be returned from the column property in that Designer.cs code?  Surely this is fixed.  I've been reading the forums and this has been around since 2004!  But I don't see a clean fix anywhere.  I don't want, or believe I should, change any .xsd, or auto-generated code to hack/fix this bug, since if I change anything with the dataset in the future, it gets wiped out during auto-generate.

Anyone have any solutions?

Thanks,

KevinHou

Answer 57

instead of directly using DateTime, look into using System.Nullable<DateTime> and manually change the relevant code (...pain? but dataset is deemed to be deprecated and is in support  mode only)

there are similar discussion in previous threads

Answer 58

Yeah, I read all the workarounds, was hoping I wouldn't need to do that.  I instead set all my column types in the dataset to string.  In this app. since I'm only displaying the data, no big deal. 

So what is the MS recommended approach for storing data retrieved from a db these days if DataSets are deprecated?

Answer 59

When I run the app, and step through with the debugger, I get an error thrown from the myDataSet.Designer.cs code for that column. 

Kevin -- what is the line *before* it hits the DataSet.Designer line of code? Your grid should be displaying your data just fine, but you must have some code somewhere else that is attempting to access the DateTime column like this:

DateTime someDate = myDataSet.Myiew[0].MyDate;

That will definitely throw an exception when your date column contains DBNull, as you've found out. The proper thing to do for columns that might be contain nulls, prior to accessing them, is to first check if they contain a null:

DateTime someDate = DateTime.Min;
if (myDataSet.Myiew[0].IsMyDateNull() == false)
    someDate = myDataSet.Myiew[0].MyDate

But again, that's only if you have to access it in code (it will display quite nicely in a grid).

Another thing you could do is forego the typed syntax and use a TryParse:

DateTime someDate;
DateTime.TryParse(myDataSet.Myiew[0]["MyDate"].ToString(), out someDate);
//

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 60

When I run the app, and step through with the debugger, I get an error thrown from the myDataSet.Designer.cs code for that column. 

Kevin -- what is the line *before* it hits the DataSet.Designer line of code? Your grid should be displaying your data just fine, but you must have some code somewhere else that is attempting to access the DateTime column like this:

DateTime someDate = myDataSet.Myiew[0].MyDate;

That will definitely throw an exception when your date column contains DBNull, as you've found out. The proper thing to do for columns that might be contain nulls, prior to accessing them, is to first check if they contain a null:

DateTime someDate = DateTime.Min;
if (myDataSet.Myiew[0].IsMyDateNull() == false)
    someDate = myDataSet.Myiew[0].MyDate

But again, that's only if you have to access it in code (it will display quite nicely in a grid).

Another thing you could do is forego the typed syntax and use a TryParse:

DateTime someDate;
DateTime.TryParse(myDataSet.Myiew[0]["MyDate"].ToString(), out someDate);
//

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

the above code exemplifies everything i hate about datasets ;p

---

http://www.setiri.com

Answer 61

the above code exemplifies everything i hate about datasets ;p

Uh oh ... let's not start a religious war about DataSets!!  ;0)

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 62

Bonnie, this whole thread is about a major shortcoming of datasets (lack of nullable support), so no war here. Just some devs who wish MS would fix datasets to make them useable.

---

http://www.setiri.com

Answer 63

in .dot net 4, apparently one can declare

int? anullableInt;

DateTime? anullableDate = null;

and so on

have anyone tried using such type to receive value from dataset DateTime field and test for null if required?

Answer 64

in .dot net 4, apparently one can declare

int? anullableInt;

DateTime? anullableDate = null;

and so on

have anyone tried using such type to receive value from dataset DateTime field and test for null if required?

This is exactly what we want datasets to do, but they do not support. And nullable types have been around since .Net 2.0

---

http://www.setiri.com

Answer 65

My apologies if this gets repeated, somehow I hit a key that refreshed my screen, don't know if my last reply went through...

Anyway, Thanks Bonnie for the reply.  I've read others who suggested the same type of workaround code, but honestly I don't know where I should put that into my code.  I used the VS 2010 dataset layout designer, which means I just dragged my database view object onto the layout designer and let VS generate the dataset code behind for me.  Since VS creates the myDataset.Designer.cs code, and that is where the code is failing, I just assume it's a MS bug that won't handle nulls in datasets.  If I go and tweak the auto-generated code in this file with any sort of workaround, then if I have to recreate my dataset again, then I'd have to add this code again since it is recreated (auto-generated again) by VS (I've found it very easy to just delete the typed dataset from the designer and re-drop it back on the layout editor when I change my table or view in SQL, I whole lot easier than trying to fumble through the dataset html and add the new fields manually).  My only two lines of code that I wrote are:

myTableAdapter.Fill((MyDataSet.MyView);

dataGridView1.DataSource(myDataSet.MyView);

All the other code for the 'Fill' method, and everything else having to do with filling and retrieving data from the dataset was auto-generated by VS.  The error occurs on my second line of code shown above, when I'm attempting to connect the datagrid with my dataset.  When I debug and step into that line of code, I'm taken to the myDataSet.Designer.cs code.  And like I said, since I didn't write that code, and since I used only VS functionality (drag-and-drop) to create the dataset, I don't see how it becomes 'my' code to have to fix - unless it's a bug.  If my database column is 'datetime', and I drag that table into VS, then if VS auto-generates the code for the dataset for me, then if I write two lines of code as above, shouldn't it all connect nicely for me?  It works fine IF I go back into the dataset layout screen, select all columns and change their property to String.  But, I shouldn't have to do that.  Rhetorical question here - not aimed at you Bonnie: Why offer a datetime option in the property of datatype for the dataset column if it is not going to work with NULLs?  If String is the only datatype that works with dbNulls, then state so, and get rid of the other datatype options.

I've just conceded defeat and I just use the String datatype and deal with my datatime columns as needed, a pain, but at least it works.  I'm having to do some date differences between two columns within the rows that I get back.  I was hoping to be able to treat those datetime columns as datetime and not have to convert from String back into datetime before my date differencing code.

KevinHou

Answer 66

Kevin --

Since VS creates the myDataset.Designer.cs code, and that is where the code is failing, I just assume it's a MS bug that won't handle nulls in datasets.

Well, I wouldn't say it's a bug ... it's just that DataSets don't handle nullable types, as in int? or DateTime? types. Keep in mind that a nullable type (for which a value = null) isn't the same as an object which has a DBNull.Value (which is what gets returned from the database and is actually an object's value). What would be required to support nullable types in DataSets would require changing what gets generated as code when it *does* encounter a DBNull.Value to set the property to a null if the property is a nullable type. Look at it this way ... currently the generated code *has* to throw an exception when it encounters DBNull.Value. NOt everything should have a default ... NULLs in databases have meaning --- they mean that nothing has ever been entered for that column, not that someone changed their mind about an entry.

And like I said, since I didn't write that code, and since I used only VS functionality (drag-and-drop) to create the dataset, I don't see how it becomes 'my' code to have to fix - unless it's a bug. If my database column is 'datetime', and I drag that table into VS, then if VS auto-generates the code for the dataset for me, then if I write two lines of code as above, shouldn't it all connect nicely for me?

Yes, it should work nicely ... a DBNull.Value in a grid does not normally cause it to crash and burn. I've tried using DateTime columns containing DBNull.Value in a DataGridView and it correctly displays an empty cell.

It works fine IF I go back into the dataset layout screen, select all columns and change their property to String. But, I shouldn't have to do that. Rhetorical question here - not aimed at you Bonnie: Why offer a datetime option in the property of datatype for the dataset column if it is not going to work with NULLs? If String is the only datatype that works with dbNulls, then state so, and get rid of the other datatype options.

You shouldn't have to do that! Something weird is going on. What's your grid definition look like, specifically for the date columns? In my testing, I am auto-generating the grid columns ... are you? If so, then I'm stumped, because that should work. If not, then what does the code look like in the form.Designer.cs for that DateTime grid column? (well, what does it look like anyway, auto-generated or not)

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 67

Ok, I've isolated the problem code.  Without the painful details, the line of code that is throwing the DBNull exception error when populating the grid is this:  myGrid.DataSource  = myDataSet.myTable.Select("1 = 1")

If I delete the "Select" filter, then it works just fine (myGrid.DataSource  = myDataSet.myTable).  Ignore my 1=1, I didn't want to put the real selector in there.  For some reason this Select filter is causing the whole check for DBNull to throw an exception when it encounters a null value in my EmpHireDate column of the dataset that I populate from a SQL db view.

I've found that if I instead use:

myDataSet.myTable.DefaultView.RowFilter = "my filter here";

myDataSet.myTable.DefaultView.Sort = "my sort here";

myGrid.DataSource = myDataSet.myTable;

Then everything works.  For some reason I can't use the "Select" method and get it to work.  It's more compact in that I can specify my filter and sort in one simple call.  But, it returns a row collection and not a DataTable.  Eventhough I can set that as the source for a datagrid without casting or a compile error, I get the runtime exception for the DBNull. 

Well, this works.  Moving on.

KevinHou

Answer 68

Hi Kevin,

Glad you found the solution ... I wish I hadn't been so busy on Wednesday so that I might have saved you some trouble finding that solution. Before you updated the above post, you hadn't yet found an answer and I would have suggested using the DefaultView right off the bat when I saw that you were trying to use a DataTable.Select. I don't think a DataRow collection plays nicely with databinding.

Sorry, but I haven't had any time to "come up for air" in the past few days, so I'm glad you've got your problem solved!  Good luck with the rest of your project! =0)

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 69

Anyone check .net 4.5 for this? ;)

---

witsml software

Answer 70

I've worked a week on a new project. Thought I would try out strongly typed datasets. The datatables looked to be wonderful during the coding. Had it in front of my analyst for a tech review and 45 mins later here I am. Great...

So, MS is not going to address this. From all I've read so far, this looks to be a given.

Any work arounds?

At this point I would even take an example on how to manually edit the files. Perhaps that would help get me past a massive rewrite. ... And what are the perils of manually editing the xsd?

Answer 71

Hi Trey,

This thread is 70 posts long, covering lots of issues ... so could you give us an indication of what exactly you need a workaround for?

No problems manually editing .xsd's ... that's the only way I ever work with Typed DataSets. In fact, you can take a look at one of my blog posts that's sort of about this:

http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

Anyway, depending on what problem you're trying to solve, there's most likely a decent workaround ... just let us know which one it is.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com