none
ADO.NET: How to handle null values on a DataTable or DataReader? RRS feed

  • Question

  • Hello there.

    On the documentation it seems that the right way to handle a null value from a DataTable is something like:
    Dim row As DataRow = dt.Rows(rowIndex)
    If Not row.IsNull(fieldIndex) Then
       Return row.Item(fieldIndex)
    End If
    

    And when using a DataReader it would be something like:
    If Not dr.IsDBNull(fieldIndex) Then
       Return dr.Item(fieldIndex)
    End If
    

    But the DbDataReader.IsDBNull() method accepts only an ordinal index as parameter, so, if you want to use the fields names (which is much more common, I guess) you have to do something like:
    Dim ordinal As Integer
    If TypeOf fieldIndex Is String Then
       ordinal = dr.GetOrdinal(fieldIndex)
    Else
       ordinal = fieldIndex
    End If
    If Not dr.IsDBNull(fieldIndex) Then
       Return dr.Item(fieldIndex)
    End If
    

    But it would be much simpler to use the same solution for both cases, like:
    DataTable:
    If DBNull.Value.Equals(dt.Rows(rowIndex).Item(fieldIndex)) Then
    
    DataReader:
    If DBNull.Value.Equals(dr.Item(fieldIndex)) Then
    

    or:
    DataTable:
    If Convert.IsDBNull(dt.Rows(rowIndex).Item(fieldIndex)) Then
    
    DataReader:
    If Convert.IsDBNull(dr.Item(fieldIndex)) Then
    

    Don't those last options have the same effect? Why we should not use this approach?

    Thanks in advance.
    • Edited by LoboFX Friday, January 27, 2012 2:13 PM
    • Moved by Paul Zhou Monday, January 30, 2012 3:29 AM move for better support (From:.NET Base Class Library)
    Friday, January 27, 2012 2:07 PM

All replies

  • I think using IsDbNull() and comparing DBNull.Value equality is the same. Another solution is using Nullable types.

    int? a = rows[rowIndes].Item[fieldIndex];

    if (a.HasValue)

    ... // use the vale

    else

    ... the field was null

     

    Friday, January 27, 2012 3:40 PM
  • Thanks for the reply Mansoor.

    It's good to remember about nullable types, thanks, but I think I can't use them in this case, because I'm writing a wrapper class and so the property that returns the field value has to be of type Object, and it seems that you can't create a nullable object, isn't it?

    Friday, January 27, 2012 3:50 PM
  • It seems that you are writing a general purpose wrapper. Returning object seems to be the only choice, but you should remember that there will be a cast operation required on the consumer side. Maybe you can use generics in writing the wrapper. If you could, please explain a little about the wrapper class, maybe I can help.
    Friday, January 27, 2012 6:18 PM
  • Yes, you guessed right Mansoor, it is a general purpose wrapper. We are just migrating some applications from old VB6+DAO to VB.NET+ADO.NET, so I did a wrapper class for Database and a wrapper class for Recordset, mimicking the old DAO Recordset interface, to speed up our migration path.

    So, as the Recordset object from DAO, and ADO, had a collection of fields, and the value for each field was returned as a variant data-type, to embody all the possible data-types, I did the same here, created a default property named FieldVal that returns the value as an Object.

    I'm not using typed datasets in this case, so I don't know beforehand which are the fields data-types, so I guess I could not use generics here. Could I?

    Friday, January 27, 2012 6:30 PM
  • I think you can still use generics. I've created a sample wrapper for a single ADODB.Record (in C# however). You'll get the idea.

    class Field
    {
        public string Name { get; private set; }
        public Field(string name)
        {
            Name = name;
        }
    }
    class Field<T>: Field
    {
        public T Value;
        public Field(string name, T value): base(name)
        {
            Value = value;
        }
    }
    class RecordWrapper
    {
        public Dictionary<string, Field> Fields { get; private set; }
        public RecordWrapper(ADODB.Record r)
        {
            Fields = new Dictionary<string, Field>();
            foreach(ADODB.Field fld in r.Fields)
            {
                Field f = null;
                switch (fld.Type)
                {
                    case ADODB.DataTypeEnum.adInteger: f = new Field<int>(fld.Name, fld.Value); break;
                    case ADODB.DataTypeEnum.adSingle: f = new Field<float>(fld.Name, fld.Value); break;
                    case ADODB.DataTypeEnum.adDouble: f = new Field<double>(fld.Name, fld.Value); break;
                    //...
                }
                Fields.Add(fld.Name, f);
            }
        }
    }

    You can add more properties and fields to the base Field class such as Type, Size, Precision, etc.

    Saturday, January 28, 2012 9:03 AM
  • I'm moving this thread to ADO.NET DataSet forum to get better support.

    Have a nice day.


    Paul Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 30, 2012 3:30 AM
  • To Mansoor Omrani:
    Well, it could work like this... but it is not always that I have the information about fields data-type. You see, maybe you've misunderstood me, I'm trying to mimic the main DAO-ADO Recordset interface, but underneath the surface I'm really using ADO.NET, not the old DAO or ADO via interoperability, and it can be a DataTable (mimicking the old Dynaset/Table/Snapshot Recordset type) or it can be a DataReader (mimicking the old Forward-only Recordset type).

    For example, when I open a "recordset" as forward-only, in reality I start a DataReader, and I think it doesn't bring data-type information on the fields. In a similar way, when I open a DataTable, I don't fire the DbDataAdapter.FillSchema() method, so I guess I don't have the information even for the DataTable (but that could be fixed).

    Anyway, I think I'll just use the original simpler solution, as it works also. But thanks again for your help!

    Monday, January 30, 2012 5:16 PM
  • Thanks Paul Zhou. I swear I've searched for a specific ADO.NET forum before posting, but couldn't find one. I even searched for some typical keyword, such as ADO.NET, and the first and second posts I've found were on that forum, so I posted there.
    Monday, January 30, 2012 5:20 PM