Why doesn't the null-coalescing operator work in this case?

Question

I am trying to fetch record into listarray as follows:

List<Car> lst = new List<Car>();

string str = "select * from Inventory"; 

using(SqlCommand cmd = new SqlCommand(str,this.sqlcon))
{
    SqlDataReader rdr = cmd.ExecuteReader();

    while (rdr.Read())
    {
        lst.Add(new Car 
                    {
                        CarId = (int)rdr["CarId"],
                        Make = (string)(rdr["Make"] ?? ""),
                        Color= (string)(rdr["Color"] ?? ""),
                        PetName = (string)(rdr["PetName"] ?? "")

                    });
    }

    rdr.Close();
}

Make,color and petname may have null values and thus I used the ?? operator. I get the following error

Unable to cast object of type system.dbnull' to 'system.string'.

What is the correct way of checking for null in this scenario?

@Code a great answer doesn't mean it can't be closed. – Patrick Hofman May 19 '17 at 10:04 — Patrick Hofman, May 19 '17 at 10:04

fubo · Answer 1 · 2017-05-19T11:02:12.240

8

DBNull isn't the same as null, so you can't use the ?? operator. You have to handle this case separately.

Replace:

Make = (string)(rdr["Make"] ?? ""),

with:

Make =  (rdr["Make"] == System.DBNull.Value ? "" : (string)rdr["Make"]),

edited May 19 '17 at 11:02

answered May 19 '17 at 09:57

fubo

44,811
17
103
137

System.DBNull throws compile error . Used dbnull.value instead and it worked fine. tx! – Poongodi May 19 '17 at 10:42
@Poongodi thank you i've updated the answer – fubo May 19 '17 at 11:02

Gerino · Answer 2 · 2017-05-19T14:37:22.773

1

SqlDataReader returns a DBNull object, which is not a C# null - it is an object representing a NULL value in the database.

You have a number of options, and creating a method to handle that might be most readable and save you on repeating code:

private static string GetStringOrEmpty(object dbValue){
    return dbValue == System.DBNull.Value ? string.Empty : (string)dbValue;
}

and then

Make = GetStringOrEmpty(rdr["Make"]),

Alternatively look into Dapper which is a very small but powerful ORM that will handle a lot of this stuff for you.

edited May 19 '17 at 14:37

answered May 19 '17 at 10:02

Gerino

1,943
1
16
21

System.DBNull throws compile error . Used dbnull.value instead and it worked fine. tx! – Poongodi May 19 '17 at 10:43

Why doesn't the null-coalescing operator work in this case?

2 Answers2