我如何检查看看一个列是否存在于一个SqlDataReader对象?在我的数据访问层，我创建了一个为多个存储过程调用构建相同对象的方法。其中一个存储过程具有其他存储过程不使用的附加列。我想修改方法以适应各种情况。
我的应用程序是用c#编写的。
当前回答
下面是接受答案的一行LINQ版本:
Enumerable.Range(0, reader.FieldCount).Any(i => reader.GetName(i) == "COLUMN_NAME_GOES_HERE")
其他回答
这对我来说很管用:
public static class DataRecordExtensions
{
public static bool HasColumn(IDataReader dataReader, string columnName)
{
dataReader.GetSchemaTable().DefaultView.RowFilter = $"ColumnName= '{columnName}'";
return (dataReader.GetSchemaTable().DefaultView.Count > 0);
}
}
下面是接受答案的一行LINQ版本:
Enumerable.Range(0, reader.FieldCount).Any(i => reader.GetName(i) == "COLUMN_NAME_GOES_HERE")
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);
}
这是一个相当老的帖子，但我想提供我的意见。
大多数提议的解决方案的挑战在于，它要求您每次都为检查的每一行和每一列枚举所有字段。
其他的则使用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();
}
}
}
如果你读了这个问题，Michael问的是DataReader，而不是DataRecord。让你的对象正确。
在数据记录上使用r.GetSchemaTable(). columns . contains (field)可以工作，但它返回BS列(见下面的截图)。
要查看一个数据列是否存在并且在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;
}
}
}
用法:
public static bool CanCreate(SqlDataReader dataReader)
{
return dataReader.ContainsColumn("RoleTemplateId")
&& !dataReader.IsDBNull("RoleTemplateId");
}
调用r.GetSchemaTable()。DataReader上的Columns返回BS列:
推荐文章
- PowerShell脚本在机器上返回。net框架的版本?
- 在哪些领域使用f#比c#更合适?
- 用c#创建一个空文件
- 如何改变列表<T>数据IQueryable<T>数据
- 您可以使用反射来查找当前正在执行的方法的名称吗?
- 流。Seek(0, SeekOrigin.Begin)或Position = 0
- 如果查询为空，则最大返回值
- "输出类型为类库的项目不能直接启动"
- 传递一个实例化的系统。类型作为泛型类的类型参数
- 从SqlCommand对象获取生成的SQL语句?
- 把内容放在HttpResponseMessage对象?
- 在c#中转换字符串为类型
- 如何比较单元测试中的列表
- 替换c#字符串中的多个字符
- 将XML字符串转换为对象
