2

SQLServer int field. Value sometimes null. DataAdapter fills dataset OK and can display data in DatagridView OK.

When trying to retrieve the data programmatically from the dataset the Dataset field retrieval code throws a StronglyTypedException error.

 [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    public int curr_reading {
        get {
            try {
                return ((int)(this[this.tableHistory.curr_readingColumn]));
            }
            catch (global::System.InvalidCastException e) {
                throw new global::System.Data.StrongTypingException("The value for column \'curr_reading\' in table \'History\' is DBNull.", e);
            }

Got past this by checking for DBNull in the get accessor and returning null but... When the dataset structure is modified (Still developing) my changes (unsurprisingly) are gone.

What is the best way to handle this situation? It seems I am stuck with dealing with it at the dataset level. Is there some sort of attribute that can tell the auto code generator to leave the changes in place?

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92

5 Answers5

6

In the typed dataset designer there is the nullvalue property. By default its value is throw exception (hence your generated code) You can set it to the desired default value.. ie. 0. Then it will return 0 instead of an exception. (other code is generated)

VS2008 :This works directly in the dataset designer.

VS2005 : It only works for strings in the designer but you can directly edit the XSD an set the property msprop:nullValue="0"

Julian de Wit
  • 3,084
  • 2
  • 29
  • 29
3
  1. Leave the auto-generated code alone. There's no way to "intercept" it getting generated so any changes you do make are guaranteed to get blown away sooner or later.

  2. .NET (well at least the .NET 2.0 system.data bits) will not convert from DBNull into anything else. This sucks but you can't do anything about it.

  3. Write an extension method called ToNullable() or similar: it can do this:

.

public static Nullable<T> ToNullable(this object x){
    if(x == DBNull.Value)
       return default(T); // return null thing
    else
       return (T)x;
}

then you can do

int? thing = DataRow["column"].ToNullable<int>();
Orion Edwards
  • 121,657
  • 64
  • 239
  • 328
  • This really works. Sad though, that I will loose my strongly typing by using the indexer with the string. – Marcel Apr 30 '10 at 11:26
  • Realize this is old, but just wondering: how did you get this to work? I can't seem to get it working as an extension. I get "The type or namespace name 'T' could not be found". If I add to the static class, VS2010 tells me "Extension method must be defined in a non-generic static class" – transistor1 Jul 04 '12 at 02:13
  • I got it. I think the first line should read: `public static Nullable ToNullable(this object x) where T : struct` – transistor1 Jul 05 '12 at 01:50
1

The dataset will have a boolean property to indicate null.

int curr_reading = ( Iscurr_readingColumnNull) ? 
                   <default_value> : row.curr_readingColumn;
MikeW
  • 5,702
  • 1
  • 35
  • 43
  • Spot on. No Experience with Extension methods but believe it would have failed as trying to use the Dataset Get accessor triggers the error. This 'internal' Datarow test works. Thanks. Bob –  Mar 02 '09 at 10:07
  • Found another field in the parent table, for this field there was no internal null test. Wrote extension method. It worked. Thanks to you all. –  Mar 05 '09 at 03:37
0
if(row["curr_reading"] is DBNull){

}else{
    row.curr_reading;
}
chiesa
  • 186
  • 2
  • 6
0

If memory serves, you need to mark the row as being edited - using .BeginEdit() or similar - then make your edits and save the row, probably using .EndEdit() or similar. You may want to do a little bit of reading into these methods (they may be on the DataSet, DataTable or DataRow) - my memory is a little hazy.

Hope this helps at least a little bit.

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
Fritz H
  • 3,539
  • 2
  • 26
  • 37