Error-Specified cast is not valid

Hi Ramco,

You can handle nulls either in your SQL query or in your C# code.

If you prefer to do it in Sql Query :

Change  string query = "select empid,firstname,lastname,dept,sex from Oh ";      

To

string query = "select COALESCE(empid, 0) , COALESCE(firstname, ""), COALESCE(lastname, ""), COALESCE(dept, 0),COALESCE(sex, "") from Oh ";

What am doing above is :

If any integer columns are null, retrun 0   (  emp id, dept  ).  If any string columns are null, return empty string ( first name, last name, sex ).   

Please use COALESCE only where you can expect nulls. Using it for every column is not a good practice.

If you rather prefer to do it in C# 

while (dr.Read())  
{  
    Employee.Add(new Employee  
    {  
        EmpId = dr.IsDBNull(0) ? 0 : Convert.ToInt32(dr.GetValue(0)),  
        FirstName = dr.IsDBNull(1) ? string.Empty : dr[1].ToString(),  
        LastName = dr.IsDBNull(2) ? string.Empty : dr[2].ToString(),  
        Dept = dr.IsDBNull(3) ? 0 : Convert.ToInt32(dr.GetValue(3)),  
        Sex = dr.IsDBNull(4) ? string.Empty : dr[4].ToString(),  
    });  
}

Again, this code will store 0 in EmpId and Dept if they are null,  and Empty string in FirstName, LastName and Sex columns if they are null. You can change the values depending on your requirement.

If you prefer to keep them null as it is, you might need to mark your fields in Employee class as nullable types ( supported in C# >=8 version ). Here is an example :

public class Employee  
{    
    public int? EmpId { get; set; }    // Notice the ? symbol
    public string FirstName { get; set; }  
    public string LastName { get; set; }  
    public int? Dept { get; set; }  // Notice the ? symbol
    public string Sex { get; set; }  
}

And then,

while (dr.Read())  
{  
    Employee.Add(new Employee  
    {  
        EmpId = dr.IsDBNull(0) ? default(int?) : Convert.ToInt32(dr.GetValue(0)),  
        FirstName = dr.IsDBNull(1) ? default(string) : dr[1].ToString(),  
        LastName = dr.IsDBNull(2) ? default(string) : dr[2].ToString(),  
        Dept = dr.IsDBNull(3) ? default(int?) : Convert.ToInt32(dr.GetValue(3)),  
        Sex = dr.IsDBNull(4) ? default(string) : dr[4].ToString(),  
    });  
}

Let me tell you something very important here. Changing the model ( employee class ) to have nullable types might break exisiting code - Please be careful there