C# 从 DataReader 对象中查找 Field 的数据类型

Find the datatype of Field from DataReader object

提问by ghanshyam.mirani

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?

假设我想知道 field 的数据类型employee_id。我如何使用SqlDataReader?

回答by Arran

.GetDataTypeNamemaybe what you are after:

.GetDataTypeName可能是你所追求的：

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

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

回答by C Sharper

Use .GetFieldType(colnameIndex)as:

使用.GetFieldType(colnameIndex)如：

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

}

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

或者它可以只是： reader.GetFieldType(0)

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

根据您的进一步逻辑，您可以将此功能塑造成简单的文本或条件形式。

回答by Jon G

reader.GetFieldType(int ordinal)

reader.GetFieldType(int ordinal)

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

将返回字段的 .NET 类型，而：

reader.GetDataTypeName(int ordinal)

reader.GetDataTypeName(int ordinal)

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

将返回一个字符串，表示数据源中字段的数据类型（例如varchar）。GetFieldType鉴于您描述的用例，可能对您更有用

回答by Neil Mountford

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

您可以使用该GetFieldType方法，传入您希望检索其类型的列的序号。它返回字段的 System.Type。

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

例如，如果您想获取第一列的类型，您可以这样做 var firstColType = reader.GetFieldType(0);

回答by devinbost

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 hasRowsvariable in an if statement or for exception handling.

这有点矫枉过正，但我​​无法想象您需要比这更多的类型信息。您还可以在 if 语句或异常处理中使用hasRows变量。

回答by JRrelyea

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).

当然，这是一个旧帖子，但我只是偶然发现了它。所以，这是我的方法，因为您绝对可以绝对地使用 DataReaders 的强类型，而不用 # 引用列（当列更改顺序时，它与不强类型一样好）。

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