Object cannot be cast from DBNull to other types Error-CodePudding

I am getting an error as object cannot be cast from DBNull to other types when i add pic1 in this code. The pic1 is from img table in database.

public List<RentModel>GetData()
{
    List<RentModel> dataList = new List<RentModel>();
    SqlCommand command = new SqlCommand("SELECT * FROM rent FULL OUTER JOIN img ON rent.id= img.r_id",con);
     
    SqlDataAdapter sqlda = new SqlDataAdapter(command);
    
    DataTable dtdata= new DataTable();
    con.Open();
    sqlda.Fill(dtdata);
    
    con.Close();
    foreach(DataRow dr in dtdata.Rows)
    {
        dataList.Add(new RentModel
        {
            id = Convert.ToInt32(dr["ID"]),
            tittle = dr["tittle"].ToString(),
            descrip = dr["descrip"].ToString(),
            area = Convert.ToInt32(dr["area"]),
            areaUnit = dr["areaUnit"].ToString(),
            bathroom = dr["bathroom"].ToString(),
            bedroom = dr["bedroom"].ToString(),
            province= dr["province"].ToString(),
            city= dr["city"].ToString(),
            stadress = dr["stadress"].ToString(),
            furnished = dr["furnished"].ToString(),
            p_type = dr["p_type"].ToString(),
            price = dr["price"].ToString(),
            pic1 = dr["pic1"].ToString(),
        });
    }

CodePudding user response：

You can use the as keyword to try casting to the expected type. Since DBNull is its own type the casting will fail and null will be returned. E.g.

descrip = dr["descrip"] as string,

But of course you must use the right type. E.g. if the image is stored as bytes (e.g. in a VARBINARY column), pic1 must be of type byte[] and you must write:

pic1 = dr["pic1"] as byte[],

If columns of some value type are nullable, make the field nullable as well. E.g., int? area

 area = dr["area"] as int?,

or use int area and write

 area = (dr["area"] as int?) ?? 0,

If a column is declared as NOT NULL in SQL then simply cast:

area = (int)dr["area"],

Note that the static return type of dr["xy"] is object, but the values already have the right type (the type corresponding to the column type) and you must only cast to get the typed value. You must actually not convert the value, unless the field or property type is different from the column type.

CodePudding user response：

you can try comparing the pic1 column to dbnull:

        dataList.Add(new RentModel
        {
            ....,
            pic1 = (dr["pic1"] == DBNull.Value)? string.Empty : dr["pic1"].ToString()
        });