在SqlDataReader对象中检查列名

Question

在SqlDataReader对象中检查列名

c#.netsqldatareader

240

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

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

- Michael Kniskern

27个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- spaark · Answer 1

4

以下是Jasmine提供的一行代码解决方案（还有一个，虽然简单！）：

reader.GetSchemaTable().Select("ColumnName='MyCol'").Length > 0;

- spaark

仅仅为了查找列名，使用GetSchemaTable()是过度的（在分配方面）。请查看源代码 https://github.com/microsoft/referencesource/blob/24a17c0c3b23ddeac0926acc13b13c54b650d3be/System.Data/System/Data/SqlClient/SqlDataReader.cs#L422 - Chad Grant

@ChadGrant 可能的。我猜这取决于上下文和需要使用它的频率，需要明智选择... - spaark

- Deepak · Answer 2

Hashtable ht = new Hashtable();

Hashtable CreateColumnHash(SqlDataReader dr)
{
    ht = new Hashtable();
    for (int i = 0; i < dr.FieldCount; i++)
    {
        ht.Add(dr.GetName(i), dr.GetName(i));
    }
    return ht;
}

bool ValidateColumn(string ColumnName)
{
    return ht.Contains(ColumnName);
}

- Clement · Answer 3

这是已接受答案的一个一行代码的LINQ版本：

Enumerable.Range(0, reader.FieldCount).Any(i => reader.GetName(i) == "COLUMN_NAME_GOES_HERE")

- Michael B · Answer 4

为了保持代码的健壮和清晰，可以使用一个单一的扩展函数，就像这样：

C#

public static class Extensions
{
    public static bool HasColumn(this SqlDataReader r, string columnName)
    {
        return !string.IsNullOrEmpty(columnName) && r.FieldCount > 0 && Enumerable.Range(0, r.FieldCount).Select((i) => r.GetName(i)).Contains(columnName, StringComparer.OrdinalIgnoreCase);
    }
}

VB：

Public Module Extensions

    <Extension()>
    Public Function HasColumn(r As SqlDataReader, columnName As String) As Boolean

        Return Not String.IsNullOrEmpty(columnName) AndAlso r.FieldCount > 0 AndAlso Enumerable.Range(0, r.FieldCount).Select(Function(i) r.GetName(i)).Contains(columnName, StringComparer.OrdinalIgnoreCase)

    End Function

End Module

- NeoH4x0r · Answer 5

这段代码纠正了Levitikon在他们的代码中遇到的问题：（改编自：[1]: http://msdn.microsoft.com/en-us/library/system.data.datatablereader.getschematable.aspx）

public List<string> GetColumnNames(SqlDataReader r)
{
    List<string> ColumnNames = new List<string>();
    DataTable schemaTable = r.GetSchemaTable();
    DataRow row = schemaTable.Rows[0];
    foreach (DataColumn col in schemaTable.Columns)
    {
        if (col.ColumnName == "ColumnName") 
        { 
            ColumnNames.Add(row[col.Ordinal].ToString()); 
            break; 
        }
    }
    return ColumnNames;
}

获取所有无用的列名而不是来自您表格的列名的原因是因为您正在获取模式列的名称（即模式表的列名）。

注意：这似乎只返回第一列的名称...

编辑：更正了代码，可以返回所有列的名称，但不能使用SqlDataReader来执行此操作。

public List<string> ExecuteColumnNamesReader(string command, List<SqlParameter> Params)
{
    List<string> ColumnNames = new List<string>();
    SqlDataAdapter da = new SqlDataAdapter();
    string connection = ""; // your sql connection string
    SqlCommand sqlComm = new SqlCommand(command, connection);
    foreach (SqlParameter p in Params) { sqlComm.Parameters.Add(p); }
    da.SelectCommand = sqlComm;
    DataTable dt = new DataTable();
    da.Fill(dt);
    DataRow row = dt.Rows[0];
    for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
    {
        string column_name = dt.Columns[ordinal].ColumnName;
        ColumnNames.Add(column_name);
    }
    return ColumnNames; // you can then call .Contains("name") on the returned collection
}

- Kom N · Answer 6

这是一个相当古老的帖子，但我想提供我的意见。

大多数提出的解决方案的挑战在于，每次检查每个列时，需要枚举所有字段的每一行。

其他人使用的GetSchemaTable方法并不被全球支持。

就个人而言，我没有问题抛出和捕获异常来检查字段是否存在。事实上，从编程角度来看，我认为这可能是最直接的解决方案，也是最容易调试和创建扩展的解决方案。除了涉及其他交易或奇怪的回滚逻辑之外，我没有注意到吞咽异常会对性能产生负面影响。

使用try-catch块进行实现

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

public class MyModel {
    public int ID { get; set; }
    public int UnknownColumn { get; set; }
}


public IEnumerable<MyModel> ReadData(SqlCommand command) {
    using (SqlDataReader reader = command.ExecuteReader()) {
        try {
            while (reader.Read()) {
                // init the row
                MyModel row = new MyModel();

                // bind the fields
                row.ID = reader.IfDBNull("ID", row.ID);
                row.UnknownColumn = reader.IfDBNull("UnknownColumn", row.UnknownColumn);

                // return the row and move forward
                yield return row;
            }
        } finally {
            // technically the disposer should handle this for you
            if (!reader.IsClosed) reader.Close();
        }
    }
}

// I use a variant of this class everywhere I go to help simplify data binding
public static class IDataReaderExtensions {
    // clearly separate name to ensure I don't accidentally use the wrong method
    public static T IfDBNull<T>(this IDataReader reader, string name, T defaultValue) {
        T value;
        try {
            // attempt to read the value
            // will throw IndexOutOfRangeException if not available
            object objValue = reader[name];

            // the value returned from SQL is NULL
            if (Convert.IsDBNull(objValue)) {
                // use the default value
                objValue = defaultValue;
            }
            else if (typeof(T) == typeof(char)) {
                // chars are returned from SQL as strings
                string strValue = Convert.ToString(objValue);

                if (strValue.Length > 0) objValue = strValue[0];
                else objValue = defaultValue;
            }

            value = (T)objValue;
        } catch (IndexOutOfRangeException) {
            // field does not exist
            value = @defaultValue;
        } catch (InvalidCastException, ex) {
            // The type we are attempting to bind to is not the same as the type returned from the database
            // Personally, I want to know the field name that has the problem
            throw new InvalidCastException(name, ex);
        }

        return value;
    }

    // clearly separate name to ensure I don't accidentally use the wrong method
    // just overloads the other method so I don't need to pass in a default
    public static T IfDBNull<T>(this IDataReader reader, string name) {
        return IfDBNull<T>(reader, name, default(T));
    }
}

如果你想避免异常处理，我建议在初始化读取器时将结果保存到一个HashSet<string>中，然后再检查你想要的列。或者为了微小的优化，你可以将你的列实现为一个Dictionary<string, int>，以防止SqlDataReader对象从Name到ordinal的重复解析。

使用HashSet<string>实现。

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

public class MyModel {
    public int ID { get; set; }
    public int UnknownColumn { get; set; }
}

public IEnumerable<MyModel> ReadData(SqlCommand command) {
    using (SqlDataReader reader = command.ExecuteReader()) {
        try {
            // first read
            if (reader.Read()) {
                // use whatever *IgnoreCase comparer that you're comfortable with
                HashSet<string> columns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

                // init the columns HashSet<string, int>
                for (int i = 0; i < reader.FieldCount; i++) {
                    string fieldName = reader.GetName(i);
                    columns.Add(fieldName);
                }

                // implemented as a do/while since we already read the first row
                do {
                    // init a new instance of your class
                    MyModel row = new MyModel();

                    // check if column exists
                    if (columns.Contains("ID") &&
                        // ensure the value is not DBNull
                        !Convert.IsDBNull(reader["ID"])) {
                        // bind value
                        row.ID = (int)reader["ID"];
                    }

                    // check if column exists
                    if (columns.Contains("UnknownColumn") &&
                        // ensure the value is not DBNull
                        !Convert.IsDBNull(reader["UnknownColumn"])) {
                        // bind value
                        row.UnknownColumn = (int)reader["UnknownColumn"];
                    }

                    // return the row and move forward
                    yield return row;
                } while (reader.Read());
            }
        } finally {
            // technically the disposer should handle this for you
            if (!reader.IsClosed) reader.Close();
        }
    }
}

使用Dictionary<string，int>实现

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

public class MyModel {
    public int ID { get; set; }
    public int UnknownColumn { get; set; }
}

public IEnumerable<MyModel> ReadData(SqlCommand command) {
    using (SqlDataReader reader = command.ExecuteReader()) {
        try {
            // first read
            if (reader.Read()) {
                // use whatever *IgnoreCase comparer that you're comfortable with
                Dictionary<string, int> columns = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);

                // init the columns Dictionary<string, int>
                for (int i = 0; i < reader.FieldCount; i++) {
                    string fieldName = reader.GetName(i);
                    columns[fieldName] = i;
                }

                // implemented as a do/while since we already read the first row
                do {
                    // init a new instance of your class
                    MyModel row = new MyModel();

                    // stores the resolved ordinal from your dictionary
                    int ordinal;

                    // check if column exists
                    if (columns.TryGetValue("ID", out ordinal) &&
                        // ensure the value is not DBNull
                        !Convert.IsDBNull(reader[ordinal])) {
                        // bind value
                        row.ID = (int)reader[ordinal];
                    }

                    // check if column exists
                    if (columns.TryGetValue("UnknownColumn", out ordinal) &&
                        // ensure the value is not DBNull
                        !Convert.IsDBNull(reader[ordinal])) {
                        // bind value
                        row.UnknownColumn = (int)reader[ordinal];
                    }

                    // return the row and move forward
                    yield return row;
                } while (reader.Read());
            }
        } finally {
            // technically the disposer should handle this for you
            if (!reader.IsClosed) reader.Close();
        }
    }
}

- RBAFF79 · Answer 7

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.Contains 不区分大小写。

- Tresto · Answer 8

我的数据访问类需要向后兼容，因此可能会尝试在数据库中还不存在该列的版本中访问列。我们返回一些相当大的数据集，因此我不是很喜欢对于每个属性都必须迭代 DataReader 列集合的扩展方法。

我有一个实用程序类，它创建了一个私有列列表，然后具有一个通用方法，该方法尝试根据列名和输出参数类型解析值。

private List<string> _lstString;

public void GetValueByParameter<T>(IDataReader dr, string parameterName, out T returnValue)
{
    returnValue = default(T);

    if (!_lstString.Contains(parameterName))
    {
        Logger.Instance.LogVerbose(this, "missing parameter: " + parameterName);
        return;
    }

    try
    {
        if (dr[parameterName] != null && [parameterName] != DBNull.Value)
            returnValue = (T)dr[parameterName];
    }
    catch (Exception ex)
    {
        Logger.Instance.LogException(this, ex);
    }
}

/// <summary>
/// Reset the global list of columns to reflect the fields in the IDataReader
/// </summary>
/// <param name="dr">The IDataReader being acted upon</param>
/// <param name="NextResult">Advances IDataReader to next result</param>
public void ResetSchemaTable(IDataReader dr, bool nextResult)
{
    if (nextResult)
        dr.NextResult();

    _lstString = new List<string>();

    using (DataTable dataTableSchema = dr.GetSchemaTable())
    {
        if (dataTableSchema != null)
        {
            foreach (DataRow row in dataTableSchema.Rows)
            {
                _lstString.Add(row[dataTableSchema.Columns["ColumnName"]].ToString());
            }
        }
    }
}

然后我可以像这样调用我的代码

using (var dr = ExecuteReader(databaseCommand))
{
    int? outInt;
    string outString;

    Utility.ResetSchemaTable(dr, false);        
    while (dr.Read())
    {
        Utility.GetValueByParameter(dr, "SomeColumn", out outInt);
        if (outInt.HasValue) myIntField = outInt.Value;
    }

    Utility.ResetSchemaTable(dr, true);
    while (dr.Read())
    {
        Utility.GetValueByParameter(dr, "AnotherColumn", out outString);
        if (!string.IsNullOrEmpty(outString)) myIntField = outString;
    }
}

- esenkaya · Answer 9

我建议在这个简单的问题中使用 try{} catch{}。然而，我不建议在 catch 中处理异常。

try 
{
  if (string.IsNullOrEmpty(reader["Name"].ToString())) 
  {
    name = reader["Name"].ToString();
  }
}
catch
{
  //Do nothing
}

- David Andersson · Answer 10

我也没能让 GetSchemaTable 正常工作，直到我找到了这种方法。

基本上我是这样做的：

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