How to handle null values in the database in c#

[Automation23]

Hello! I have a method that retrieves the first value of the first row in the first column from the database. Now I need to assert if it's a null value or not. In the database the value is NULL. Even if I assert is if it doesn't equal to null and then make it true. The code is passing. Here is what I have so far:

public static void TestAppointmentIsCancelled(int apptId)
        {
            using (SqlConnection connection = new SqlConnection(Config.connectionStringBH))
            {
                connection.Open();
                string query = $"select CancellationTypeId, * from hcAppointments where appointmentId = '{apptId}'";
                SqlCommand command = new SqlCommand(query, connection);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Assert.IsTrue(reader.GetValue(0) != null);
                    }
                }
            }
        }

The question is, how null values are stored in the database? I tried with the String.Empty but it still represents an empty value, so I don't think it's the right thing. How can I handle null values in the database in the c# assertions?

 

[jmcilhinney]

Firstly, if you only want a single value from a query then don't use ExecuteReader. This is an example of why you read the documentation. If you had read the documentation for the SqlCommand class then you'd have seen that it has an ExecuteScalar method that does exactly that.

 

[jmcilhinney]

You should also have done some reading about ADO.NET instead of making assumptions. ADO.NET was designed before nullable value types existed, so null could not be used to represent no value. For that reason, they created a DBNull class. Whenever using ADO.NET to get a NULL value from a database or pass a NULL value to a database, you use the DBNull.Value property. Also, various types have dedicated methods for null checks, with the SqlDataReader being one of them. Yet again, reading the documentation would have shown you that. If you were going to use a data reader then you could do this:

Assert.IsFalse(reader.IsDBNull(0));

If you were to call ExecuteScalar, as you should, then you could do this:

Assert.IsTrue(command.ExecuteScalar() != DBNull.Value);