检查 SqlDataReader 对象中的列名

Check for column name in a SqlDataReader object

提问by Michael Kniskern

How do I check to see if a column exists in a SqlDataReaderobject? In my data access layer, I have create a method that builds the same object for multiple stored procedures calls. One of the stored procedures has an additional column that is not used by the other stored procedures. I want to modified the method to accommodate for every scenario.

如何检查SqlDataReader对象中是否存在列？在我的数据访问层中，我创建了一个为多个存储过程调用构建相同对象的方法。其中一个存储过程有一个其他存储过程不使用的附加列。我想修改该方法以适应每个场景。

My application is written in C#.

我的应用程序是用 C# 编写的。

采纳答案by Chad Grant

public static class DataRecordExtensions
{
    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i=0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;
        }
        return false;
    }
}

Using Exceptions for control logic like in some other answers is considered bad practiceand has performance costs. It also sends false positives to the profiler of # exceptions thrown and god help anyone setting their debugger to break on exceptions thrown.

Exception像在其他一些答案中一样使用s 进行控制逻辑被认为是不好的做法，并且会产生性能成本。它还将误报发送到抛出异常的分析器，并且上帝帮助任何人设置他们的调试器来中断抛出的异常。

GetSchemaTable() is also another suggestion in many answers. This would not be a preffered way of checking for a field's existance as it is not implemented in all versions (it's abstract and throws NotSupportedException in some versions of dotnetcore). GetSchemaTable is also overkill performance wise as it's a pretty heavy duty function if you check out the source.

GetSchemaTable() 也是许多答案中的另一个建议。这不是检查字段是否存在的首选方法，因为它并未在所有版本中实现（它是抽象的，并且在某些 dotnetcore 版本中抛出 NotSupportedException）。GetSchemaTable 在性能方面也是过度的，因为如果您查看源代码，它是一个非常繁重的功能。

Looping through the fields can have a small performance hit if you use it a lot and you may want to consider caching the results.

如果您经常使用这些字段，循环遍历这些字段可能会对性能造成很小的影响，并且您可能需要考虑缓存结果。

回答by Matt Hamilton

I think your best bet is to call GetOrdinal("columnName")on your DataReader up front, and catch an IndexOutOfRangeException in case the column isn't present.

我认为最好的办法是预先在 DataReader 上调用GetOrdinal("columnName")，并在列不存在的情况下捕获 IndexOutOfRangeException。

In fact, let's make an extension method:

其实我们来做一个扩展方法：

public static bool HasColumn(this IDataRecord r, string columnName)
{
    try
    {
        return r.GetOrdinal(columnName) >= 0;
    }
    catch (IndexOutOfRangeException)
    {
        return false;
    }
}

Edit

编辑

Ok, this post is starting to garner a few down-votes lately, and I can't delete it because it's the accepted answer, so I'm going to update it and (I hope) try to justify the use of exception handling as control flow.

好吧，这篇文章最近开始获得一些反对票，我无法删除它，因为它是公认的答案，所以我将更新它并（我希望）尝试证明使用异常处理是合理的控制流。

The other way of achieving this, as posted by Chad Grant, is to loop through each field in the DataReader and do a case-insensitive comparison for the field name you're looking for. This will work really well, and truthfully will probably perform better than my method above. Certainly I would never use the method above inside a loop where performace was an issue.

正如Chad Grant所发布的，实现此目的的另一种方法是遍历 DataReader 中的每个字段，并对您要查找的字段名称进行不区分大小写的比较。这将非常有效，并且说实话可能会比我上面的方法表现得更好。当然，我永远不会在性能有问题的循环中使用上述方法。

I can think of one situation in which the try/GetOrdinal/catch method will work where the loop doesn't. It is, however, a completely hypothetical situation right now so it's a very flimsy justification. Regardless, bear with me and see what you think.

我可以想到一种情况，在这种情况下，try/GetOrdinal/catch 方法将在循环不起作用的情况下起作用。然而，现在这是一个完全假设的情况，所以这是一个非常脆弱的理由。无论如何，请耐心等待，看看您的想法。

Imagine a database that allowed you to "alias" columns within a table. Imagine that I could define a table with a column called "EmployeeName" but also give it an alias of "EmpName", and doing a select for either name would return the data in that column. With me so far?

想象一个数据库，它允许您“别名”表中的列。想象一下，我可以定义一个包含名为“EmployeeName”的列的表，但也给它一个别名“EmpName”，并且对任一名称进行选择都会返回该列中的数据。陪我到此为止？

Now imagine that there's an ADO.NET provider for that database, and they've coded up an IDataReader implementation for it which takes column aliases into account.

现在假设该数据库有一个 ADO.NET 提供程序，并且他们已经为它编写了一个 IDataReader 实现，它将列别名考虑在内。

Now, dr.GetName(i)(as used in Chad's answer) can only return a single string, so it has to return only oneof the "aliases" on a column. However, GetOrdinal("EmpName")could use the internal implementation of this provider's fields to check each column's alias for the name you're looking for.

现在，dr.GetName(i)（在 Chad 的回答中使用）只能返回一个字符串，因此它必须只返回列中的一个“别名”。但是，GetOrdinal("EmpName")可以使用此提供程序字段的内部实现来检查您要查找的名称的每个列的别名。

In this hypothetical "aliased columns" situation, the try/GetOrdinal/catch method would be the only way to be sure that you're checking for every variation of a column's name in the resultset.

在这种假设的“别名列”情况下，try/GetOrdinal/catch 方法将是确保您检查结果集中列名称的每个变体的唯一方法。

Flimsy? Sure. But worth a thought. Honestly I'd much rather an "official" HasColumn method on IDataRecord.

脆弱？当然。但值得深思。老实说，我更喜欢 IDataRecord 上的“官方”HasColumn 方法。

回答by Dave Markle

You can also call GetSchemaTable()on your DataReader if you want the list of columns and you don't want to have to get an exception...

如果您想要列列表并且不想获得异常，您也可以在 DataReader 上调用GetSchemaTable()...

回答by Jasmine

It's much better to use this boolean function:

使用这个布尔函数要好得多：

r.GetSchemaTable().Columns.Contains(field)

r.GetSchemaTable().Columns.Contains(field)

One call - no exceptions. It might throw exceptions internally, but I don't think so.

一个电话 - 没有例外。它可能会在内部抛出异常，但我不这么认为。

NOTE: In the comments below, we figured this out... the correct code is actually this:

注意：在下面的评论中，我们发现了这一点……正确的代码实际上是这样的：

public static bool HasColumn(DbDataReader Reader, string ColumnName) { 
    foreach (DataRow row in Reader.GetSchemaTable().Rows) { 
        if (row["ColumnName"].ToString() == ColumnName) 
            return true; 
    } //Still here? Column not found. 
    return false; 
}

回答by Chad Grant

I wrote for Visual Basic users :

我为 Visual Basic 用户写的：

Protected Function HasColumnAndValue(ByRef reader As IDataReader, ByVal columnName As String) As Boolean
    For i As Integer = 0 To reader.FieldCount - 1
        If reader.GetName(i).Equals(columnName) Then
            Return Not IsDBNull(reader(columnName))
        End If
    Next

    Return False
End Function

I think this is more powerful and the usage is :

我认为这更强大，用法是：

If HasColumnAndValue(reader, "ID_USER") Then
    Me.UserID = reader.GetDecimal(reader.GetOrdinal("ID_USER")).ToString()
End If

回答by David Andersson

Neither did I get GetSchemaTableto work, until I found this way.

我也没有开始GetSchemaTable工作，直到我找到了这种方式。

Basically I do this:

基本上我这样做：

Dim myView As DataView = dr.GetSchemaTable().DefaultView
myView.RowFilter = "ColumnName = 'ColumnToBeChecked'"

If myView.Count > 0 AndAlso dr.GetOrdinal("ColumnToBeChecked") <> -1 Then
  obj.ColumnToBeChecked = ColumnFromDb(dr, "ColumnToBeChecked")
End If

回答by Skadoosh

How about

怎么样

if (dr.GetSchemaTable().Columns.Contains("accounttype"))
   do something
else
   do something

It probably would not be as efficient in a loop

在循环中它可能不会那么有效

回答by RBAFF79

public static bool DataViewColumnExists(DataView dv, string columnName)
{
    return DataTableColumnExists(dv.Table, columnName);
}

public static bool DataTableColumnExists(DataTable dt, string columnName)
{
    string DebugTrace = "Utils::DataTableColumnExists(" + dt.ToString() + ")";
    try
    {
        return dt.Columns.Contains(columnName);
    }
    catch (Exception ex)
    {
        throw new MyExceptionHandler(ex, DebugTrace);
    }
}

Columns.Containsis case-insensitive btw.

Columns.Contains不区分大小写顺便说一句。

回答by Levitikon

If you read the question, Michael asked about DataReader, not DataRecord folks. Get your objects right.

如果您阅读了这个问题，Michael 会询问 DataReader，而不是 DataRecord 人员。让你的对象正确。

Using a r.GetSchemaTable().Columns.Contains(field)on a DataRecord does work, but it returns BS columns (see screenshot below.)

r.GetSchemaTable().Columns.Contains(field)在 DataRecord 上使用 a确实有效，但它返回 BS 列（请参见下面的屏幕截图。）

To see if a data column exists AND contains data in a DataReader, use the following extensions:

要查看数据列是否存在并包含 DataReader 中的数据，请使用以下扩展：

public static class DataReaderExtensions
{
    /// <summary>
    /// Checks if a column's value is DBNull
    /// </summary>
    /// <param name="dataReader">The data reader</param>
    /// <param name="columnName">The column name</param>
    /// <returns>A bool indicating if the column's value is DBNull</returns>
    public static bool IsDBNull(this IDataReader dataReader, string columnName)
    {
        return dataReader[columnName] == DBNull.Value;
    }

    /// <summary>
    /// Checks if a column exists in a data reader
    /// </summary>
    /// <param name="dataReader">The data reader</param>
    /// <param name="columnName">The column name</param>
    /// <returns>A bool indicating the column exists</returns>
    public static bool ContainsColumn(this IDataReader dataReader, string columnName)
    {
        /// See: http://stackoverflow.com/questions/373230/check-for-column-name-in-a-sqldatareader-object/7248381#7248381
        try
        {
            return dataReader.GetOrdinal(columnName) >= 0;
        }
        catch (IndexOutOfRangeException)
        {
            return false;
        }
    }
}

Usage:

用法：

    public static bool CanCreate(SqlDataReader dataReader)
    {
        return dataReader.ContainsColumn("RoleTemplateId") 
            && !dataReader.IsDBNull("RoleTemplateId");
    }

---

---

Calling r.GetSchemaTable().Columnson a DataReader returns BS columns:

调用r.GetSchemaTable().ColumnsDataReader 会返回 BS 列：

回答by Chris Ji

Here is a working sample for Jasmin's idea:

以下是 Jasmin 想法的工作示例：

var cols = r.GetSchemaTable().Rows.Cast<DataRow>().Select
    (row => row["ColumnName"] as string).ToList(); 

if (cols.Contains("the column name"))
{

}