check the dataset for null values

Question

i want to check all my data set for null values and replace them. in order to do it i wrote this code:

public static DataSet Validator(DataSet dataSet)
{
    foreach (DataTable dataTable in dataSet.Tables)
    foreach (DataRow dataRow in dataTable.Rows)
    foreach (DataColumn dataDataColumn in dataTable.Columns)
    if (dataRow[dataDataColumn] == DBNull.Value)
    {
        if (dataRow[dataDataColumn].GetType() == typeof(string)) 
            dataRow[dataDataColumn] = "";
        else if (dataRow[dataDataColumn].GetType() == typeof(DateTime))
            dataRow[dataDataColumn] = DateTime.MinValue;
        else if (dataRow[dataDataColumn].GetType() == typeof(int) ||
                 dataRow[dataDataColumn].GetType() == typeof(short) ||
                 dataRow[dataDataColumn].GetType() == typeof(long) ||
                 dataRow[dataDataColumn].GetType() == typeof(float) ||
                 dataRow[dataDataColumn].GetType() == typeof(byte) ||
                 dataRow[dataDataColumn].GetType() == typeof(double)) 
            dataRow[dataDataColumn] = 0;
    }

    return dataSet;
}

but it doesn't work!. where is the problem? and if there is a better way i really appreciated to let me know. Thank you.

UPDATE but it doesn't work!. still there is null values in the dataset.

"It doesn't work" isn't a helpful error report. Does it not compile? Does it throw an exception when you run it? Does it leave nulls? Does it set things to `42` instead of the value you want? Give more details and you are more likely to get help. — Chris, Jul 22 '15 at 08:06
I'm not sure, but if `dataRow[dataDataColumn]` is `null`, then `GetType` will be also `null`... like I said, I'm not sure. Test it just to see, for example `MessageBox.Show(dataRow[dataDataColumn].GetType.ToString());`... if that is true, You get `null`, so there none of condition is satisfied and nothing will be stored into field, operation will be skipped. And, I think, after `foreach (DataRow dataRow in dataTable.Rows)` You have to put one more code `dataTable.AcceptChanges();`... even for `dataSet`, too. — nelek, Jul 22 '15 at 08:09
How about simply changing the columns in the data table to not allow null values specifying the desired default value? — Thorsten Dittmar, Jul 22 '15 at 08:18
@nelek If `dataRow[dataDataColumn]` would be `null`, `GetType()` would throw a `NullReferenceException`. — sloth, Jul 22 '15 at 08:19
It is a poor naming `Validator` when it actually doing value replacing job — Eric, Jul 22 '15 at 08:22

sloth · Accepted Answer · 2015-07-22T08:20:27.537

When you run

dataRow[dataDataColumn].GetType()

you call GetType() on the value of dataRow[dataDataColumn], which is always DBNull.value. So you always get the type DBNull.

Check for dataDataColumn.DataType instead, which will return the actual datatype of the column.

You could use something like:

public static DataSet Validator(DataSet dataSet)
{
    foreach (DataTable dataTable in dataSet.Tables)
        foreach (DataRow dataRow in dataTable.Rows)
            foreach (DataColumn dataDataColumn in dataTable.Columns)
                if (dataRow.IsNull(dataDataColumn))
                    dataRow[dataDataColumn] = GetDefaultValue(dataDataColumn.DataType);

    return dataSet;
}

static object GetDefaultValue(Type t)
{
    // get the default value for value types
    if (t.IsValueType)
        return Activator.CreateInstance(t);

    // in case of a string, we want an empty one instead of null
    if (t == typeof(string))
        return String.Empty;

    return null;
}

score 2 · Answer 2 · edited May 23 '17 at 11:58

2

You are using DBNull.Value to check for null values, try testing it against Null as well.

There is a difference between DBNull.Value and Null as previously explained here What is the difference between null and System.DBNull.Value?

edited May 23 '17 at 11:58

Community

1
1

answered Jul 22 '15 at 08:11

Joe

221
2
11

That's not the problem. A `null` value is represented by `DBNull.Value` in a `DataTable`. – sloth Jul 22 '15 at 08:16

check the dataset for null values

2 Answers2