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 *


Fatal error: Uncaught GuzzleHttp\Exception\ClientException: Client error: `POST https://dc.services.visualstudio.com/v2/track` resulted in a `400 Invalid instrumentation key` response: {"itemsReceived":1,"itemsAccepted":0,"errors":[{"index":0,"statusCode":400,"message":"Invalid instrumentation key"}]} in D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php:113 Stack trace: #0 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Middleware.php(66): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Psr7\Request), Object(GuzzleHttp\Psr7\Response)) #1 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\promises\src\Promise.php(203): GuzzleHttp\Middleware::GuzzleHttp\{closure}(Object(GuzzleHttp\Psr7\Response)) #2 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\promises\src\Promise.php(156): GuzzleHttp\Promise\Promise::callHandler(1, Object(GuzzleHttp\P in D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php on line 113