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

You can use the GetFieldType method, passing in the ordinal of the column whose type you wish to retrieve. It returns the System.Type of the field.

As an example, if you wanted to get the type of the first column you could do var firstColType = reader.GetFieldType(0);

Granted this is an old post, but I just stumbled across it. So, this is my approach because you can absolutely, definitely, use strong typing with DataReaders and without referencing a column by a # (when columns change orders, it's as good as not being strongly typed).

Try something like this:

    using (dsSomeDataSet dsList = new dsSomeDataSet())
{
	using (System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand())
    	{
    
		//blah blah blah take care of parameter definitions for the stored proc
    
		using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
    		{
    			while (sqlReader.Read())
    			{
    				//populate each returning row
    				dsSomeDataSet.tATablesRow rowNote = dsList.tATable.tATablesRow();
    				
				//using the actual field name, strongly typed, required using the declared dataset variable, not the dataset object				
    				rowNote.ThisField'sName = new Guid(sqlReader[ dsList.tATable.ThisField'sName.ColumnName].ToString() );
    				
				dsList.tNotes.AddtNotesRow( rowNote );
    			}
    			sqlReader.Close();
    		}
    
    
    	}   //releases the command resources
    }   //releases the dataset resources