我如何检查看看一个列是否存在于一个SqlDataReader对象?在我的数据访问层,我创建了一个为多个存储过程调用构建相同对象的方法。其中一个存储过程具有其他存储过程不使用的附加列。我想修改方法以适应各种情况。
我的应用程序是用c#编写的。
我如何检查看看一个列是否存在于一个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获取更多详细信息。
其他回答
以下是Jasmin想法的工作示例:
var cols = r.GetSchemaTable().Rows.Cast<DataRow>().Select
(row => row["ColumnName"] as string).ToList();
if (cols.Contains("the column name"))
{
}
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);
}
}
列。Contains不区分大小写。
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);
}
虽然没有公开的方法,但是在内部类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);