When reading values from a SqlDataReader in .NET null SQL values will cause the reader to error. I used to have an explicit null check around each column, however as this involved quite a bit of code duplication I had a look for something better and found this Stack Overflow post which suggested using extension methods.

The code below is pretty much a direct copy of the answer but expanded to allow column selection by name and to return null rather than empty values.

public static List GetTreatments(string connectionString)
{
    List treatments = new List();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("SpGetTreatment"))
        {
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Connection = connection;

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Treatment treatment = new Treatment();
                    
                    treatment.TreatmentId = (int)reader["TreatmentId"];
                    treatment.TreatmentDesc = reader["TreatmentDesc"].ToString();
                    treatment.MessageId = reader.SafeGetInt("MessageId");
                    treatment.MessageDesc = reader.SafeGetString("MessageDesc");

                    treatments.Add(treatment);
                }
            }

            connection.Close();
        }
    }

    return treatments;
}

public static string SafeGetString(this SqlDataReader reader, int colIndex)
{
    if (!reader.IsDBNull(colIndex))
    {
        return reader.GetString(colIndex);
    }
    else
    {
        return null;
    }
}

public static string SafeGetString(this SqlDataReader reader, string colName)
{
    int colIndex = reader.GetOrdinal(colName);

    if (!reader.IsDBNull(colIndex))
    {
        return reader.GetString(colIndex);
    }
    else
    {
        return null;
    }
}

public static int? SafeGetInt(this SqlDataReader reader, int colIndex)
{
    if (!reader.IsDBNull(colIndex))
    {
        return reader.GetInt32(colIndex);
    }
    else
    {
        return null;
    }
}

public static int? SafeGetInt(this SqlDataReader reader, string colName)
{
    int colIndex = reader.GetOrdinal(colName);

    if (!reader.IsDBNull(colIndex))
    {
        return reader.GetInt32(colIndex);
    }
    else
    {
        return null;
    }
}

Abbass Sharara · 2nd December 2018 at 3:03 pm

Thank you for the great idea i never thought of making them be as static methods.

Leave a Reply

Your email address will not be published. Required fields are marked *