0

I have added 4 new fields. A country, State, City, Age, Language to my table and these all have null values. if I comment these fields. I am able to show all data but with these fields, I am getting an error

Object cannot be cast from DBNull to other types

Code:

public List<UserModel> listall() {
 List <UserModel> lst = new List <UserModel>();
 int i = 0;
 try {
  using(con = new SqlConnection(connStr)) {
   cmd = new SqlCommand("Usp_CrudOperation", con);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.AddWithValue("@Action", "View");
   con.Open();
   SqlDataReader rdr = cmd.ExecuteReader();
   while (rdr.Read()) {
    i++;
    lst.Add(new UserModel {
     Id = Convert.ToInt32(rdr["Id"]),
      Name = rdr["Name"].ToString(),
      Gender = rdr["Gender"].ToString(),
      Email = rdr["Email"].ToString(),
      Password = rdr["Password"].ToString(),
      DoB = Convert.ToDateTime(rdr["DoB"]),
      Country = rdr["Country"].ToString(),
      State = rdr["State"].ToString(),
      City = rdr["City"].ToString(),
      Age = Convert.ToInt32(rdr["Age"]),
      Language = rdr["Language"].ToString(),
    });
   }
  }
 } 
 catch (SqlException se) 
 {
  throw se;
 } 

 finally 
 {
  con.Close();
  con.Dispose();
 }
 return lst;
}
Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
Lalit Yadav
  • 21
  • 5

1 Answers1

1

I Suppose that you are getting error on Age column. then you can do this in order to surpass the error

Age = Convert.IsDbNull(rdr["Age"]) ? (int?)null : Convert.ToInt32(rdr["Age"]),

Extension Method approach

public static T GetSafeValue<T>(this DbDataReader reader, int index)
{
    if (reader.IsDBNull(index))
        return default(T);

    return (T)reader.GetValue(index);
}

public static T GetSafeValue<T>(this DbDataReader reader, string name) 
{
    var col = reader.GetOrdinal(name);

    if ( reader.IsDBNull(col) )
        return default(T);

    return (T)reader.GetValue(col);
}
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72