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
