'Object cannot be cast from DBNull to other types.' in stored procedure

Question

I have a method in which I am trying to read some values from the database table DependencyList. It contains columns that can be null, such as ReleaseId, TaskId and PhaseId. I am having issues when reading and trying to visualize them inside the view when one of them is NULL.

The code looks like below:

public ActionResult FullIndex(int id)
{
        List<Dependency> dependencyList = new List<Dependency>();

        string connectionString = Configuration["ConnectionStrings:DefaultConnection"];

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string sql = "ReadFullDependencies";
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter = new SqlParameter
            {
                ParameterName = "@DependencyId",
                Value = id,
                SqlDbType = SqlDbType.VarChar,
                Size = 50
            };

            command.Parameters.Add(parameter);

            using (SqlDataReader dataReader = command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    Dependency dependency = new Dependency();
                    dependency.Id = Convert.ToInt32(dataReader["Id"]);
                    dependency.ReleaseId = Convert.ToInt32(dataReader["ReleaseId"]);
                    dependency.ReleaseName = ReadReleaseName(dependency.ReleaseId);
                    dependency.PhaseId = Convert.ToInt32(dataReader["PhaseId"]);
                    dependency.PhaseName = ReadPhaseName(dependency.PhaseId);
                    dependency.TaskId = Convert.ToInt32(dataReader["TaskId"]);
                    dependency.TaskName = ReadTaskName(dependency.TaskId);

                    dependencyList.Add(dependency);
                }
            }

            connection.Close();
        }

        return View(dependencyList);
}

If all of them are not null in the database, then it works perfectly. However, let's say that ReleaseId is NULL. Then, on line:

dependency.ReleaseId = Convert.ToInt32(dataReader["ReleaseId"]);

I get this error:

Object cannot be cast from DBNull to other types.

I've tried putting question marks (?) on the side of each data type in the Model, but that didn't work out.

How can I prevent this from happening and store the null values as "None", for example, inside the Model?

Answer 1

You should check DBNull then assign value accordingly, like this

dependency.ReleaseId = dataReader["ReleaseId"] != System.DBNull.Value ? Convert.ToInt32(dataReader["ReleaseId"]) : 0;