Check DbNull before casting C#

Question

I am trying query the SQL Server Database and return the response in JSON. I am trying the code below

      using (SqlConnection connection = new SqlConnection(connStr))
        {
            SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.Add("@ROOM_Data", SqlDbType.VarChar);
            command.Parameters["@ROOM_Data"].Value = ROOM;
            connection.Open();
            List<DatabaseResult> records = new List<DatabaseResult>();

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var row = new DatabaseResult
                    {
                        request_id = (int)reader["request_id"],
                        room = (string)reader["room"],
                        jrs_no = (int)reader["jrs_no"],
                        submit_date = (DateTime)reader["submit_date"],
                        sample_no = (int)reader["sample_no"],
                        animal_id = (string)reader["animal_id"],
                        pen_id = (string)reader["pen_id"],
                        ped_no = (string)reader["ped_no"],
                        gender = (string)reader["gender"],
                        dob = (DateTime)reader["dob"],
                        parent_mating = (string)reader["parent_mating"],
                        generation = (string)reader["generation"],
                        allele = (string)reader["allele"],
                        status_type = (string)reader["status_type"],
                        genotype = (string)reader["genotype"],
                        comments = (string)reader["comments"],
                        completion_date = (DateTime)reader["completion_date"],
                        disposition = (string)reader["disposition"],

                    };
                    records.Add(row);
                }
                return Ok(records);

Here I am having an issue when there is a null in any field. I tried to look in to other forums can I understand we need to check each column if it is null or not before casting. But I am checking if I can do through the method which can handle all the cases?

Answer 1

You can check column value against DBNull.Value like this:

request_id = (reader["request_id"] == DBNull.Value) ? default(int) : (int)reader["request_id"];

If you want to simplify the process to all columns, create an extension method which compares against DBNull.Value like this example:

// using DBNull.Value comparison
public static T GetValue<T>(this SqlDataReader reader, string columnName)
{
    var value = reader[columnName]; // read column value

    return value == DBNull.Value ? default(T) : (T)value;
}

// alternative using GetOrdinal and IsDBNull
public static T GetValue<T>(this SqlDataReader reader, string columnName) 
{
    int index = reader.GetOrdinal(columnName); // read column index

    return reader.IsDBNull(index) ? default(T) : (T)reader.GetValue(index);
}

Usage example:

request_id = GetValue<int>(reader, "request_id");