using reader to check if a field contains null value

Question

I am wanting to know how i can check if a field contains null value and replace it with text N/A or just not display the field. But i don't want the code to break if the field contains null i want it to continue until all fields are filled with a value.

C# Code

using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT FirstName, LastName, Date FROM EOI WHERE (FormID = '13')";
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            while(reader.Read())
            {
                Label1.Text = reader["FirstName"].ToString();
                Label2.Text = reader["LastName"].ToString();
                DateTime Text = Convert.ToDateTime(reader["Date"]);
                Label3.Text = Text.ToString("d");
            }
        }
    }

Answer 1

You can either check this in C#:

if(reader["FirstName"]==null)
{
 // Do something
}

or in T-SQL with ISNULL:

SELECT ISNULL(FirstName,'N/A'), ISNULL(LastName,'N/A'), Date FROM EOI

Answer 2

I'm assuming that the null value you will have a problem with is the Date column. A DateTime in C# cannot be null since it is a struct. You would want to cast it to a nullable DateTime instead:

DateTime? date = (DateTime?)reader["Date"];

Now it's up to you to perform logic when transforming this to a string:

dateLabel.Text = date != null ? date.Value.ToString("d") : "N/A";

For the string columns just rewrite it as follows since strings are already nullable:

firstNameLabel.Text = (string)reader["FirstName"] ?? "N/A";

Answer 3

You can either check in your select statement or in code. In SQL:

SELECT IsNull(FirstName, 'N/A') as FirstName, 
        Coalesce(LastName, 'N/A') as LastName, Date FROM EOI WHERE (FormID = '13');

In .Net, you need to compare it to DbNull.Value:

Label1.Text = reader["FirstName"] == DBNull.Value ? "N/A" :  Convert.ToString(reader["FirstName"]);

Note that in the above example, Convert.ToString() will convert nulls to empty strings. This is a third example of what you can do.

Answer 4

If you know the types in advance you can use (pseudo code as I'm typing on an iPhone):

KnownType myData = reader.IsDbNull(fieldname) ? MyDefaultValue : reader.GetKnownType(fieldname)

E.g. String myData = reader.IsDbNull(fieldname) ? "" : reader.GetString(fieldname)

String dateValue = reader.IsDbNull(fieldname) ? "No date" : reader.GetDate(fieldname).ToString()

This is more efficient and minimises casting. For maximum efficiency you should also use the field index rather than the field name. Every time you use the field name, the index has to be calculated: reader("Date") is reader.GetValue(reader.GetOrdinal("Date"))