SqlDataReader null handling

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;
	}
}

Leave a Reply

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