Find the datatype of Field from DataReader object

I have following query:

SqlCommand cmd = new SqlCommand("Select employee_id, 
          lastname, firstname from Employees", conn);

// Execute reader
SqlDataReader reader = cmd.ExecuteReader();

Suppose I want to know the datatype of field employee_id. How do I determine this using the SqlDataReader?

reader.GetFieldType(int ordinal)

will return the .NET type of the field, while:

reader.GetDataTypeName(int ordinal)

will return a string representing the data type of the field in the data source (e.g. varchar). GetFieldType is likely to be more useful to you given the use case you describe

You can get all the relevant metadata with this:

var metaDataList = new List<IDictionary<String, Object>>();

using (SqlDataReader reader = cmd.ExecuteReader())
{
    var hasRows = reader.HasRows;
    while (reader.Read())
    {
        for (int i = 0; i < reader.FieldCount; i++)
        {
            dynamic fieldMetaData = new ExpandoObject();
            var columnName = reader.GetName(i);
            var value = reader[i];
            var dotNetType = reader.GetFieldType(i);
            var sqlType = reader.GetDataTypeName(i);
            var specificType = reader.GetProviderSpecificFieldType(i);
            fieldMetaData.columnName = columnName;
            fieldMetaData.value = value;
            fieldMetaData.dotNetType = dotNetType;
            fieldMetaData.sqlType = sqlType;
            fieldMetaData.specificType = specificType;
            metaDataList.Add(fieldMetaData);
        }
    }
}

It's slightly overkill, but I can't imagine you would need more type information than that. You could also use the hasRows variable in an if statement or for exception handling.

Use .GetFieldType(colnameIndex) as:

If (reader.GetFieldType(0) Is GetType(String) Or reader.GetFieldType(0) Is
GetType(Date) )
{
...

}

or it can be just: reader.GetFieldType(0)

According to your further logic you can mold this function into simple text or conditional form.

.GetDataTypeName may be what you are after:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getdatatypename.aspx