我如何检查看看一个列是否存在于一个SqlDataReader对象?在我的数据访问层,我创建了一个为多个存储过程调用构建相同对象的方法。其中一个存储过程具有其他存储过程不使用的附加列。我想修改方法以适应各种情况。

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


当前回答

Use:

if(Enumerable.Range(0,reader.FieldCount).Select(reader.GetName).Contains("columName"))
{
     employee.EmployeeId= Utility.ConvertReaderToLong(reader["EmployeeId"]);
}

你可以从can You get the column names from a SqlDataReader获取更多详细信息。

其他回答

这对我来说很管用:

public static class DataRecordExtensions
{
    public static bool HasColumn(IDataReader dataReader, string columnName)
    {
        dataReader.GetSchemaTable().DefaultView.RowFilter = $"ColumnName= '{columnName}'";
        return (dataReader.GetSchemaTable().DefaultView.Count > 0);
    }
}

在您的特定情况下(所有过程都有相同的列,只有一个过程有额外的一列),检查阅读器的FieldCount属性来区分它们会更好、更快。

const int NormalColCount = .....
if(reader.FieldCount > NormalColCount)
{
    // Do something special
}

您还可以(出于性能原因)将此解决方案与解决方案迭代解决方案混合使用。

Use:

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

在循环中它可能没有那么有效。

虽然没有公开的方法,但是在内部类System.Data.ProviderBase.FieldNameLookup中确实存在一个方法,这个方法是SqlDataReader所依赖的。

In order to access it and get native performance, you must use the ILGenerator to create a method at runtime. The following code will give you direct access to int IndexOf(string fieldName) in the System.Data.ProviderBase.FieldNameLookup class as well as perform the book keeping that SqlDataReader.GetOrdinal()does so that there is no side effect. The generated code mirrors the existing SqlDataReader.GetOrdinal() except that it calls FieldNameLookup.IndexOf() instead of FieldNameLookup.GetOrdinal(). The GetOrdinal() method calls to the IndexOf() function and throws an exception if -1 is returned, so we bypass that behavior.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Reflection.Emit;

public static class SqlDataReaderExtensions {

   private delegate int IndexOfDelegate(SqlDataReader reader, string name);
   private static IndexOfDelegate IndexOf;

   public static int GetColumnIndex(this SqlDataReader reader, string name) {
      return name == null ? -1 : IndexOf(reader, name);
   }

   public static bool ContainsColumn(this SqlDataReader reader, string name) {
      return name != null && IndexOf(reader, name) >= 0;
   }

   static SqlDataReaderExtensions() {
      Type typeSqlDataReader = typeof(SqlDataReader);
      Type typeSqlStatistics = typeSqlDataReader.Assembly.GetType("System.Data.SqlClient.SqlStatistics", true);
      Type typeFieldNameLookup = typeSqlDataReader.Assembly.GetType("System.Data.ProviderBase.FieldNameLookup", true);

      BindingFlags staticflags = BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Static;
      BindingFlags instflags = BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;

      DynamicMethod dynmethod = new DynamicMethod("SqlDataReader_IndexOf", typeof(int), new Type[2]{ typeSqlDataReader, typeof(string) }, true);
      ILGenerator gen = dynmethod.GetILGenerator();
      gen.DeclareLocal(typeSqlStatistics);
      gen.DeclareLocal(typeof(int));

      // SqlStatistics statistics = (SqlStatistics) null;
      gen.Emit(OpCodes.Ldnull);
      gen.Emit(OpCodes.Stloc_0);
      // try {
      gen.BeginExceptionBlock();
      //    statistics = SqlStatistics.StartTimer(this.Statistics);
      gen.Emit(OpCodes.Ldarg_0); //this
      gen.Emit(OpCodes.Call, typeSqlDataReader.GetProperty("Statistics", instflags | BindingFlags.GetProperty, null, typeSqlStatistics, Type.EmptyTypes, null).GetMethod);
      gen.Emit(OpCodes.Call, typeSqlStatistics.GetMethod("StartTimer", staticflags | BindingFlags.InvokeMethod, null, new Type[] { typeSqlStatistics }, null));
      gen.Emit(OpCodes.Stloc_0); //statistics
      //    if(this._fieldNameLookup == null) {
      Label branchTarget = gen.DefineLabel();
      gen.Emit(OpCodes.Ldarg_0); //this
      gen.Emit(OpCodes.Ldfld, typeSqlDataReader.GetField("_fieldNameLookup", instflags | BindingFlags.GetField));
      gen.Emit(OpCodes.Brtrue_S, branchTarget);
      //       this.CheckMetaDataIsReady();
      gen.Emit(OpCodes.Ldarg_0); //this
      gen.Emit(OpCodes.Call, typeSqlDataReader.GetMethod("CheckMetaDataIsReady", instflags | BindingFlags.InvokeMethod, null, Type.EmptyTypes, null));
      //       this._fieldNameLookup = new FieldNameLookup((IDataRecord)this, this._defaultLCID);
      gen.Emit(OpCodes.Ldarg_0); //this
      gen.Emit(OpCodes.Ldarg_0); //this
      gen.Emit(OpCodes.Ldarg_0); //this
      gen.Emit(OpCodes.Ldfld, typeSqlDataReader.GetField("_defaultLCID", instflags | BindingFlags.GetField));
      gen.Emit(OpCodes.Newobj, typeFieldNameLookup.GetConstructor(instflags, null, new Type[] { typeof(IDataReader), typeof(int) }, null));
      gen.Emit(OpCodes.Stfld, typeSqlDataReader.GetField("_fieldNameLookup", instflags | BindingFlags.SetField));
      //    }
      gen.MarkLabel(branchTarget);
      gen.Emit(OpCodes.Ldarg_0); //this
      gen.Emit(OpCodes.Ldfld, typeSqlDataReader.GetField("_fieldNameLookup", instflags | BindingFlags.GetField));
      gen.Emit(OpCodes.Ldarg_1); //name
      gen.Emit(OpCodes.Call, typeFieldNameLookup.GetMethod("IndexOf", instflags | BindingFlags.InvokeMethod, null, new Type[] { typeof(string) }, null));
      gen.Emit(OpCodes.Stloc_1); //int output
      Label leaveProtectedRegion = gen.DefineLabel();
      gen.Emit(OpCodes.Leave_S, leaveProtectedRegion);
      // } finally {
      gen.BeginFaultBlock();
      //    SqlStatistics.StopTimer(statistics);
      gen.Emit(OpCodes.Ldloc_0); //statistics
      gen.Emit(OpCodes.Call, typeSqlStatistics.GetMethod("StopTimer", staticflags | BindingFlags.InvokeMethod, null, new Type[] { typeSqlStatistics }, null));
      // }
      gen.EndExceptionBlock();
      gen.MarkLabel(leaveProtectedRegion);
      gen.Emit(OpCodes.Ldloc_1);
      gen.Emit(OpCodes.Ret);

      IndexOf = (IndexOfDelegate)dynmethod.CreateDelegate(typeof(IndexOfDelegate));
   }

}

下面的方法很简单,对我来说很有效:

 bool hasMyColumn = (reader.GetSchemaTable().Select("ColumnName = 'MyColumnName'").Count() == 1);