Best way to handle Datarow DBNull

Question

Possible Duplicate:
Best way to check if a Data Table has a null value in it

I want to know what should be the way to check DBNull for a DataTable - DataRow values.

Ex

I have a DataRow which fetches information from database from rows type like :

varchar, money, Int and so on.

What should be my (simple and sweet) approach to handle such situation.

such kind of work are tipically handled by orm's or micro orm, if you need something lightweight that can save you hours of work, have a look at Dapper-dot-net http://code.google.com/p/dapper-dot-net/ — Felice Pollano, Sep 20 '12 at 07:39

score 29 · Accepted Answer · answered Sep 20 '12 at 07:38

29

Try:

foreach(DataRow row in table.Rows)
{
    object value = row["ColumnName"];
    if (value == DBNull.Value)
    {

    }
    else
    {
    }
}

answered Sep 20 '12 at 07:38

4b0

21,981
30
95
142

score 7 · Answer 2 · answered Sep 20 '12 at 07:37

7

Try this

For varchar

string val = dr["name"].ToString();

For int

int? val = dr["status"] == DBNull.Value ? (int?) null : Convert.ToInt32(dr["status"]);

Do the same for Money, Decimal as done for int replacing with respective .Net types

answered Sep 20 '12 at 07:37

codingbiz

26,179
8
59
96

Perfect for me: Address.Street = row["STREET"] == DBNull.Value ? "" : row["STREET"].ToString(); – Scott Aug 03 '16 at 11:57
1

@Scott `Address.Street = row["STREET"].ToString();` would work for you since DBNull implements `ToString()` and returns an empty string. – Michael Z. Jun 13 '22 at 03:59

score 1 · Answer 3 · answered Sep 20 '12 at 07:38

You can use an extension method like this;

public static T GetValue<T>(this OracleDataReader reader, string fieldName)
{
    T result = default(T);
    int index = reader.GetOrdinal(fieldName);

    if (reader.IsDBNull(index))
    {
        return default(T);
    }

    if (typeof(T) == typeof(string))
    {
        result = (T)Convert.ChangeType(reader.GetString(index), typeof(T));
    }

    if (typeof(T) == typeof(int))
    {
        result = (T)Convert.ChangeType(reader.GetInt32(index), typeof(T));
    }

    if (typeof(T) == typeof(DateTime))
    {
        result = (T)Convert.ChangeType(reader.GetDateTime(index), typeof(T));
    }

    if (typeof(T) == typeof(byte[]))
    {
        OracleLob blob = reader.GetOracleLob(index);
        result = (T)Convert.ChangeType(blob.Value, typeof(T));
    }

    return result;
}

And you can use like string title = reader.GetValue<string>("title")

That code technically works (so I won't downvote), but it's very inefficient. — Tim M., Sep 20 '12 at 07:43

score 0 · Answer 4 · answered Sep 20 '12 at 07:41

There are clearly-defined mappings for CLR and SQL types, so the question is really how to efficiently and accurately map those types. Long-term, the easiest way is probably to use an automated mapping process which maps the properties of your class to the columns in the DataRow. You can write your own or find many examples/products online (any ORM features this as core functionality).

Assuming that you still want to make manual assignments, you need to determine how you want to handle null values from the database. Do you want to assign them to a corresponding nullable type? do you want to use default(T)? do you want to use another value (default can be a poor substitute for null)? For example, a temperature of 0 degrees is perfectly valid, but default(float) == 0. If you use default(T), you might not be able to tell the difference between zero and a value that was null in the database.

Once you have your assignment strategy defined, put the code into a reusable form (extension methods, helper class, etc.) Prefer unboxing to the exact type when possible, as this will be the fastest. Following that, unbox to type, then cast. Following that, use the Convert class.

Best way to handle Datarow DBNull

Ex

4 Answers4

Linked