Replace DBNull with null

Question

I´m currently working on a C# WPF project that uses a MS SQL-Server.

Since working with DBNull values is somewhat annoying, I wondered if it´s possible to somehow convert those into regular c# null values.

I wrote a little wrapper to connect to the database, execute an SELECT statement and return the result as a DataTable :

public static DataTable getResultTable(string select, params DbParameter[] parameters) {
    using (OleDbConnection connection = new OleDbConnection(_connectionString)) {
        connection.Open();
        try {
            using (OleDbCommand cmd = new OleDbCommand(select, connection)) {
                for (int i = 0; i < parameters.Length; i++) {
                    cmd.Parameters.Add("?", parameters[i].type).Value = parameters[i].value;
                }
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd)) {
                    using (DataTable dt = new DataTable()) {
                        adapter.Fill(dt);

                        //Here my DataTable dt is filled with the required data
                        //I´d like to replace all DBNull Values with regular null values now

                        return dt;
                    }
                }
            }
        } finally {
            connection.Close();
        }
    }
}

I sure could loop over every single field of the DataTable and replace the value in case it´s DBNull, but I wonder if there is a better way.

EDIT:

For anyone stumbling across this question. Using the answer of @Sergiu Muresan I created two Extension Methods, that exactly fit my needs:

public static class ExtensionMethods {
    public static T? GetValue<T>(this DataRow row, string columnName) where T : struct {
        if (row[columnName] is T)
            return (T)row[columnName];
        return null;
    }

    public static T GetValue<T>(this DataRow row, string columnName, T defaultValue) {
        return (row[columnName] is T) ? (T)row[columnName] : defaultValue;
    }
}

The first Methods restricts The T-Patameter to struct only, which means you can only use value-types (int, bool, double, ...) here, but no object-types (string, customObjects, ...).

It will then return the Nullable version of given type (e.g. int? for int), which will be null if the value was DBNull.
The second method can be used for both value-types and object-types. Here you also have to give a default value, which will be returned
when the vlaue is DBNull. This method can also be used to change DBNull to null, but for object-types only, since those always can be set to null and don´t need a specail Nullable.

Do you really need to replace? The better way is not to replace. — jdweng
– jdweng, Commented Nov 21, 2018 at 14:45
@jdweng No I dont´t NEED to, but it´s more comfortable working with null since you cane use operators like ??. — Tim Schmidt
– Tim Schmidt, Commented Nov 21, 2018 at 14:47
What reason do you have to replace it? Just work with DBNull — slow
– slow, Commented Nov 21, 2018 at 14:47
I'm curious why you are using OleDb to talk to SQL Server, rather than System.Data.SqlClient; the latter is more performant. Consider migrating to a more modern data access mechanism (like Dapper or Entity Framework). If you move away from straight-up System.Data, then your DbNull problems will mostly disappear. Also, since you are using your connection, you don't need the try/finally/close - the Dispose call done by the using will close the connection. — Flydog57
– Flydog57, Commented Nov 21, 2018 at 14:59

Sergiu Muresan · Accepted Answer · 2018-11-21 15:14:39Z

4

Extension methods enable you to add methods to existing types without creating a new derived type, recompiling, or otherwise modifying the original type. An extension method is a special kind of static method, but they are called as if they were instance methods on the extended type.

public static class ExtensionMethods
{
    public static T GetValue<T>(this DataRow row, string columnName, T defaultValue = default(T))
    {
        var obj = row[columnName];

        // if obj is DbNull it will skip this and return the default value
        if (obj is T)
        {
            return (T)obj;
        }

        return defaultValue;
    }
}

And you can use it like this

var dt = getResultTable(...);

var value1 = dt.Rows[0].GetValue<int>("ColumnName");

var value2 = dt.Rows[0].GetValue<int>("ColumnName", 10); // you can also specify a different default value if needed

edited Nov 21, 2018 at 15:14

answered Nov 21, 2018 at 15:09

Sergiu Muresan

5392 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Schmidt Over a year ago

Thanks, i dind´t know you could do that.

Stringeater Over a year ago

Perfect solution

Collectives™ on Stack Overflow

Replace DBNull with null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related