Dealing with Database Reader to avoid errors when DB has null value

In this quick post you are going to learn the way to avoid errors that comes when DB passes null value to DB Reader.

Introduction

Yesterday we were working on a project with my team and we were getting an error message on our production server when I looked at the code, I found titled error. To fix this I am using following code:

string connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;

            SqlConnection conn = new SqlConnection(connectionString);

            conn.Open();

            SqlCommand command = new SqlCommand("SELECT * FROM NewReRegistration WHERE rollnumber=@RollNumber", conn);

            command.Parameters.Add(new SqlParameter("RollNumber", RollNumber));

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())

            {

                candidatename.Text = reader.IsDBNull(4) ? "" : reader.GetString(4);

                fathername.Text = reader.IsDBNull(9) ? "" : reader.GetString(9);

                guardianname.Text = reader.IsDBNull(10) ? "" : reader.GetString(10);

                relationwithguardian.Text = reader.IsDBNull(11) ? "" : reader.GetString(11);

                dob.Text = reader.IsDBNull(12) ? "" : reader.GetString(12).ToString();

                permanentaddress.Text = reader.IsDBNull(16) ? "" : reader.GetString(16);

                correspondenceaddress.Text = reader.IsDBNull(17) ? "" : reader.GetString(17);

                mobilenumber.Text = reader.IsDBNull(18) ? "" : reader.GetString(18);

                otherphonenumbers.Text = reader.IsDBNull(19) ? "" : reader.GetString(19);

                emailid1.Text = reader.IsDBNull(20) ? "" : reader.GetString(20);

               

            }

 

This is the code I am still using for that web application.

Here is the code that has issue:

otherphonenumbers.Text = reader.GetString(19);

Actually, in above code I am trying to read 4th cell values from database and unfortunately database has null value and the same it produced error.

Now by using Ternary Operator we can fix this as follows:

          otherphonenumbers.Text = reader.IsDBNull(19) ? “” : reader.GetString(19);

And this concept worked fine for us.