How do I retrieve null values in my database to a view

Question

I have a simple Employee Database which allows me to add new a employee, with some of my textbox fields left empty (I'm using winform). It currently works ok in the database. It registers NULL values in place of the empty textboxes. My problem now is how to display all the employees from the database in a gridview: When I run the program, it pops up a messagebox saying "object cannot be cast from DBNull to other types". Is it because 'age' and 'married' columns in my database contain some null values? Please help, I'm trying to understand how to use nullable types in c# with mssql database.

   public static List<Employee> GetEmployees()
    {
        List<Employee> empListToReturn = new List<Employee>();
        SqlConnection conn = GetConnection();
        string queryStatment = "SELECT * FROM Employee";
        SqlCommand sqlCmd = new SqlCommand(queryStatment, conn);
        try
        {
            conn.Open();
            SqlDataReader reader = sqlCmd.ExecuteReader();
            while (reader.Read())
            {
                Employee emp = new Employee();
                emp.FirstName = reader["firstname"].ToString();
                emp.LastName = reader["lastname"].ToString();
                emp.Age = Convert.ToInt32(reader["age"]);
                emp.Married = (reader["married"]);

                empListToReturn.Add(emp);
            }
            reader.Close();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
        return empListToReturn;

    }

http://stackoverflow.com/questions/1772025/sql-data-reader-handling-null-column-values — Tim Schmelter, Nov 10 '15 at 13:22
@TimSchmelter I used emp.Age = reader[2] as int? ?? default(int); It is able to display age column but it displays 0 inplace of null in my gridview — FaithN, Nov 10 '15 at 13:53
you can do that but this one liner is almost code obfuscation — Tim Schmelter, Nov 10 '15 at 13:57

David · Accepted Answer · 2015-11-10T13:30:55.547

2

As the error states, you can't convert null to other types. Specifically in this case an int:

emp.Age = Convert.ToInt32(reader["age"]);

(Since value types can't be null.)

Simply check for null before converting:

if (reader["age"] == DBNull.Value)
    emp.Age = 0;
else
    emp.Age = Convert.ToInt32(reader["age"]);

Edit: As pointed out in a comment, there are multiple ways to do this. Some may be more efficient than others. For example:

if (reader.IsDBNull(reader.GetOrdinal("age")))
    emp.Age = 0;
else
    emp.Age = reader.GetInt32(reader.GetOrdinal("age")));

Unless the performance difference is significant, I'd probably prefer the former when using named columns instead of the ordinal, since this requires an extra method call to get the ordinal. (Unless there's a string overload that I'm just not seeing on MSDN?)

Additionally, if you don't want 0 as an option, you might change the .Age property from int to Nullable<int> (or int? for short). This would allow you to set a null value on that object:

if (reader.IsDBNull(reader.GetOrdinal("age")))
    emp.Age = null; // <-- here
else
    emp.Age = reader.GetInt32(reader.GetOrdinal("age")));

This is often very useful when there's a clear semantic difference between 0 (which is a valid value) and null (which is a lack of a value).

It's also worth noting that this code is bad practice:

catch (SqlException ex)
{
    throw ex;
}

Not only is your catch block not actually handling the exception at all, it's actually overwriting the stack trace. Which would make debugging more difficult. Since the catch block isn't doing anything, just remove it entirely. You don't need a catch in a try/finally.

edited Nov 10 '15 at 13:30

answered Nov 10 '15 at 13:22

David

208,112
36
198
279

2

Instead of comparing with `DBNull.Value` you can use `reader.IsDBNull` and instead of `Convert.ToInt32` you can use `reader.GetInt32` – Tim Schmelter Nov 10 '15 at 13:24
1

I prefer the use of `int.TryParse()` myself. – gmiley Nov 10 '15 at 13:32
I tried this: if (reader.IsDBNull(reader.GetOrdinal("age"))) { emp.Age = null; it retuns the list with empty data in place of null values } else { emp.Age = reader.GetInt32(reader.GetOrdinal("age")); } – FaithN Nov 10 '15 at 14:03
@FaithN: What do you mean by "empty data in place of null values"? `null` values *are* empty data. Is this working for you? – David Nov 10 '15 at 14:05
@David I mean the gridview is populated with firstname, lastname and age, but some of the cells for age is blank or empty in the gridview. These cells contain null values in the database. I want to show null on my gridview just as it is in the database – FaithN Nov 10 '15 at 14:13
@FaithN: What exactly do you think `null` means? If there is no value to show, then why do you expect it to show a value? If you want to display some default in place of `null` then set that default on the object instead of setting it to `null`. – David Nov 10 '15 at 14:15
@David thanks, I appreciate. – FaithN Nov 10 '15 at 14:21
@FaithN you can check for null and replace with `"[NULL]"` or something to indicate null as a string. – gmiley Nov 10 '15 at 14:35

How do I retrieve null values in my database to a view

1 Answers1