SQL COALESCE vs. DBNull check in C#

Question

In my project, I have to retrieve a large amount of data from SQL and map the data to object fields; something like this:

cu.UnitName = dr["UnitName"].ToString().Trim();
cu.LocalId = DbUtil.RemoveNull(dr["LocalID"], "");
cu.DatabaseName = DbUtil.RemoveNull(dr["DatabaseName"], "");
cu.DatabaseServer = DbUtil.RemoveNull(dr["DatabaseServer"], "");
cu.UserName = DbUtil.RemoveNull(dr["UserName"], "");
cu.Password = DbUtil.RemoveNull(dr["Password"], "");
cu.RoleId = DbUtil.RemoveNull(dr["RoleId"], 0);

where DbUtil.RemoveNull is following (int version, the string version is similar):

public static int RemoveNull(object data, int defaultValue)
    {
        if (data is DBNull || data == null)
            return defaultValue;
        return int.Parse(data.ToString());
    }

So I wonder if SQL's COALESCE or ISNULL would do the same job faster. Did someone do similar comparison? Which way would be more efficient?

The performance is not going to be an issue either way. You should take whatever seems more maintainable. — Lucero, Aug 17 '12 at 07:04
Try `ISNULL`. See here for reasons (it's faster): http://stackoverflow.com/questions/2287642/which-is-quicker-coalesce-or-isnull — Tom Chantler, Aug 17 '12 at 07:28
If you do go down the C# route I'd recommend using String.Empty rather than "". http://stackoverflow.com/questions/151472/what-is-the-difference-between-string-empty-and-empty-string — openshac, Aug 17 '12 at 09:12
Of course by COALESCE I meant "SQL-way" of getting rid of null values. ISNULL or COALESCE is not the question here, rather is the SQL way is faster than C# way. Updated the question. — Azimuth, Sep 28 '12 at 12:42

score 0 · Answer 1 · answered Aug 17 '12 at 06:59

0

while returning data from database, you could do

select COALESCE (data ,defaultValue) from table

that would be faster, as there is no need to execute RemoveNull function from front end code

answered Aug 17 '12 at 06:59

Joe G Joseph

23,518
5
56
58

score 0 · Answer 2 · answered Aug 17 '12 at 07:01

0

cu.LocalId = dr["LocalId"] ?? "";

answered Aug 17 '12 at 07:01

James Kyburz

13,775
1
32
33

score 0 · Answer 3 · answered Aug 17 '12 at 07:02

0

Rather make use of the following statement in your sql:

SELECT ISNULL(value,0)

This will always return 0 when there is a NULL. So you do not have to convert it on the code side.

Please refer to http://msdn.microsoft.com/en-us/library/ms184325.aspx

answered Aug 17 '12 at 07:02

Conrad Lotz

8,200
3
23
27

See my last comment to the question. – Azimuth Sep 28 '12 at 12:44

score 0 · Answer 4 · answered Aug 17 '12 at 07:04

'COALESCE' returns the first nonnull expression among its arguments, so you will probably use ISNULL.

Using ISNULL in your database query will be much faster. Imagine you have 1m records and you invoke a C# method 1m times, that will certainly take some time to execute.

On the other hand SQL engine is optimized to work with big number of records. But this won't be flexible enough. You can't use some complex logic and easily change the default values.

See my last comment to the question. – Azimuth Sep 28 '12 at 12:42 — Azimuth, Sep 28 '12 at 12:42

SQL COALESCE vs. DBNull check in C#

4 Answers4