How to read DataReader Columns by their names?
This code snippet will help you to read a value in a column by its name.ADO.NET exposes methods to read the values by their position only.
It is uses extension methods of the .NET framework to attach these additional methods to instance of the data reader object.
Once the code (described in Description section) is implemented the data reader gets the additional methods of the format GetXxxx (columnName), where Xxxx is name of a simple type in Framework like string, int, long etc.
How to read DataReader Columns
I am sure most of the programmer would has encountered this situation where they wished to read the coulombs in the data reader by their name, but ended up using the ordinal position or repeated code logic.
The below given extension method will let you read the values in the Data reader using their names, something likeDataReader.GetString (columnName)
Usage:
1. Add the below class to your project with a proper name space and import the namespace wherever such methods are to be used.
2. Compile the below calls into an assembly, and reuse the assembly where you need this feature by importing the namespace of the class.
Once you have done either of the above, the DataReader instances will get these methods automatically.
public static class IDataReaderExtension
{
///
/// this method will return the value of the specified columnName, cast to
/// the type specified in T. However, if the value found in the reader is
/// DBNull, this method will return the default value of the type T.
///
///
/// The reader in which columnName is found.
/// The columnName to retrieve.
///
public static T GetValueOrDefault
{
T returnValue = default(T);
if (reader.HasColumn(columnName))
{
object columnValue = reader[columnName];
if (!(columnValue is DBNull))
{
returnValue = (T)Convert.ChangeType(columnValue, typeof(T));
}
}
return returnValue;
}
public static object GetValue(this IDataReader reader, string columnName)
{
object returnValue = null;
if (reader.HasColumn(columnName))
{
returnValue = reader[columnName];
if (returnValue is DBNull)
{
returnValue = null;
}
}
return returnValue;
}
///
/// Determines whether a datareader has the given column name
///
/// Datareader in which to find the column
/// Column to find
///
public static bool HasColumn(this IDataReader reader, string columnName)
{
try
{
reader.GetOrdinal(columnName);
return true;
}
catch
{
return false;
}
}
public static bool GetBoolean(this IDataReader reader, string fieldName)
{
int fieldsIndex;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return reader.GetValue(fieldsIndex).ToString() == "0" ? false : true;
}
}
return default(bool);
}
public static byte GetByte(this IDataReader reader, string fieldName)
{
int fieldsIndex;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToByte(reader.GetValue(fieldsIndex));
}
}
return default(byte);
}
public static long GetBytes(this IDataReader reader, string fieldName, long fieldOffset, byte[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}
public static char GetChar(this IDataReader reader, string fieldName)
{
int fieldsIndex;
fieldsIndex = reader.GetOrdinal(fieldName);
if (CheckColumn(reader, fieldName))
{
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToChar(reader.GetValue(fieldsIndex));
}
}
return default(char);
}
public static long GetChars(this IDataReader reader, string fieldName, long fieldoffset, char[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}
public static DateTime GetDateTime(this IDataReader reader, string fieldName)
{
int fieldsIndex;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToDateTime(reader.GetValue(fieldsIndex));
}
}
return default(DateTime);
}
public static DateTime? GetDateTimeNullable(this IDataReader reader, string fieldName)
{
int fieldsIndex;
fieldsIndex = reader.GetOrdinal(fieldName);
if (CheckColumn(reader, fieldName))
{
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToDateTime(reader.GetValue(fieldsIndex));
}
}
return null;
}
public static decimal GetDecimal(this IDataReader reader, string fieldName)
{
int fieldsIndex = 0;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToDecimal(reader.GetValue(fieldsIndex));
}
}
return default(decimal);
}
public static double GetDouble(this IDataReader reader, string fieldName)
{
int fieldsIndex;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToDouble(reader.GetValue(fieldsIndex));
}
}
return default(double);
}
public static float GetFloat(this IDataReader reader, string fieldName)
{
throw new NotImplementedException();
}
public static Guid GetGuid(this IDataReader reader, string fieldName)
{
throw new NotImplementedException();
}
public static short GetInt16(this IDataReader reader, string fieldName)
{
int fieldsIndex = 0;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToInt16(reader.GetValue(fieldsIndex));
}
}
return default(short);
}
private static bool CheckColumn(this IDataReader reader, string field)
{
bool columnExists;
try
{
reader.GetOrdinal(field);
columnExists = true;
}
catch (IndexOutOfRangeException)
{
columnExists = false;
}
return columnExists;
}
public static int GetInt32(this IDataReader reader, string fieldName)
{
int fieldsIndex = 0;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToInt32(reader.GetValue(fieldsIndex));
}
}
return 0;
}
public static long GetInt64(this IDataReader reader, string fieldName)
{
int fieldsIndex = 0;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return Convert.ToInt64(reader.GetValue(fieldsIndex));
}
}
return default(long);
}
public static int GetInt64AsInt32(this IDataReader reader, string fieldName)
{
return (int)GetInt64(reader, fieldName);
}
public static string GetString(this IDataReader reader, string fieldName)
{
int fieldsIndex = 0;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
if (!reader.IsDBNull(fieldsIndex))
{
return reader.GetString(fieldsIndex);
}
}
return string.Empty;
}
public static int GetValues(object[] values)
{
throw new NotImplementedException();
}
public static bool IsDBNull(this IDataReader reader, string fieldName)
{
int fieldsIndex = 0;
if (CheckColumn(reader, fieldName))
{
fieldsIndex = reader.GetOrdinal(fieldName);
return reader.IsDBNull(fieldsIndex);
}
return true;
}
}