Object cannot be cast from DBNull to other types i

问题:

I need to add into the list one object CompanyDetails, so I get data from my database, and load it into constructor.

result.Add(new CompanyDetails() { 
    Name = dr["name"].ToString(), 
    City = dr["city"].ToString(), 
    StreetName = dr["streetName"].ToString(), 
    StreetNr = Convert.ToInt32(dr["apartmentNr"]), 
    Tax = int.Parse(dr["TAX"].ToString() )});

StreetNr and Tax can have null value. And when I'm trying to run it i get the error:

Object cannot be cast from DBNull to other types

How can I fix it? I know that normally I should check if tax or streetNr equals DBNull or not, but I don't know how I can do it in this case.

this is class CompanyDetails:

public class CompanyDetails
{
    public string Name { get; set; }
    public string City { get; set; }
    public string StreetName { get; set; }
    public int? StreetNr { get; set; }
    public int? Tax { get; set; }

}

回答1:

Tax = dr["TAX"] == DBNull.Value ? 0 : (int)dr["TAX"]

That will check if it's null, if it is, it sets the int value to 0, otherwise it assigns the integer value.

Applying that to your code:

result.Add(new CompanyDetails() { 
    Name = dr["name"].ToString(), 
    City = dr["city"].ToString(), 
    StreetName = dr["streetName"].ToString(), 
    StreetNr = dr["apartmentNr"] == DBNull.Value ? 0 : (int)dr["apartmentNr"]
    Tax = dr["TAX"] == DBNull.Value ? 0 : (int)dr["TAX"]
    )});

EDIT:
In case StreetNr and Tax are already of type int?, then just assign null instead of 0.

result.Add(new CompanyDetails() { 
    Name = dr["name"].ToString(), 
    City = dr["city"].ToString(), 
    StreetName = dr["streetName"].ToString(), 
    StreetNr = dr["apartmentNr"] == DBNull.Value ? (int?)null : (int)dr["apartmentNr"]
    Tax = dr["TAX"] == DBNull.Value ? (int?)null : (int)dr["TAX"]
    )});

回答2:

how about:

object tmp = dr["apartmentNr"];
int? apartmentNr = tmp is DBNull ? (int?)null : (int)tmp;

or easier - use a tool like "dapper" to avoid having to do all this stuff...

var row = conn.Query<CompanyDetails>("select * from ...", args).FirstOrDefault();

or:

var rows = conn.Query<CompanyDetails>("select * from ...", args).ToList();

回答3:

You should do like this;

dr["TAX"] is DBNull ? string.Empty : dr["TAX"].ToString();

回答4:

You can probably do like this:

StreetName = (dr["streetName"] + "")

This should give you an empty string if its null, and give you the string from the DB if it isn't.