Check for column name in a SqlDataReader object

问题:

How do I check to see if a column exists in a SqlDataReader object? 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.

My application is written in C#.

回答1:

Using Exceptions for control logic like in some other answers is considered bad practice and has performance costs.

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

The more appropriate way to do this is:

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;
    }
}

回答2:

It\'s much better to use this boolean function:

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; 
}

回答3:

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.

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.

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.

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?

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.

Now, dr.GetName(i) (as used in Chad\'s answer) can only return a single string, so it has to return only one of 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.

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.

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

回答4:

In one line, use this after your DataReader retrieval:

var fieldNames = Enumerable.Range(0, dr.FieldCount).Select(i => dr.GetName(i)).ToArray();

Then,

if (fieldNames.Contains(\"myField\"))
{
    var myFieldValue = dr[\"myField\"];
    ...

Edit

Much more efficient one-liner that does not requires to load the schema:

var exists = Enumerable.Range(0, dr.FieldCount).Any(i => string.Equals(dr.GetName(i), fieldName, StringComparison.OrdinalIgnoreCase));

回答5:

Here is a working sample for Jasmin\'s idea:

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

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

}

回答6:

this works for me:

bool hasColumnName = reader.GetSchemaTable().AsEnumerable().Any(c => c[\"ColumnName\"] == \"YOUR_COLUMN_NAME\");

回答7:

The following is simple and worked for me:

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

回答8:

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

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

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

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().Columns on a DataReader returns BS columns:

回答9:

I wrote for Visual Basic users :

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

回答10:

Here the solution from Jasmine in one line... (one more, tho simple!):

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

回答11:

Here is a one liner linq version of the accepted answer:

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

回答12:

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);
    }

回答13:

This code corrects the issues that Levitikon had with their code: (adapted from: [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;
}

The reason for getting all of those useless column names and not the name of the column from your table... Is because your are getting the name of schema column (i.e. the column names for the Schema table)

NOTE: this seems to only return the name of the first column...

EDIT: corrected code that returns the name of all columns, but you cannot use a SqlDataReader to do it

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
}

回答14:

Neither did I get GetSchemaTable to work, until I found this way.

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

回答15:

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 is case-insensitive btw.

回答16:

To keep your code robust and clean, use a single extension function, like this:

    Public Module Extensions

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

            Return If(String.IsNullOrEmpty(columnName) OrElse r.FieldCount = 0, False, Enumerable.Range(0, r.FieldCount).Select(Function(i) r.GetName(i)).Contains(columnName, StringComparer.OrdinalIgnoreCase))

        End Function

    End Module

回答17:

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...

回答18:

These answers are already posted here. Just Linq-ing a bit:

bool b = reader.GetSchemaTable().Rows
                                .Cast<DataRow>()
                                .Select(x => (string)x[\"ColumnName\"])
                                .Contains(colName, StringComparer.OrdinalIgnoreCase);
//or

bool b = Enumerable.Range(0, reader.FieldCount)
                   .Select(reader.GetName)
                   .Contains(colName, StringComparer.OrdinalIgnoreCase);

The second one is cleaner, and much much faster. Even if you dont run GetSchemaTable every single time in the first approach, the lookup is going to be very slow.

回答19:

In your particular situation (all procedures has the same columns except 1 which has additional 1 column), it will be better and faster to check reader. FieldCount property to distinguish between them.

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

I know it is an old post but I decided to answer to help other in the same situation. you can also (for performance reason) mix this solution with the solution iterating solution.

回答20:

My data access class needs to be backward compatible, so I might be trying to access a column in a release where it doesn\'t exist in the database yet. We have some rather large data sets being returned so I\'m not a big fan of an extension method that has to iterate the DataReader column collection for each property.

I have a utility class that creates a private list of columns and then has a generic method that attempts to resolve a value based on a column name and output parameter type.

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());
            }
        }
    }
}

Then I can just call my code like so

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;
    }
}

回答21:

Although there is no publicly exposed method, a method does exist in the internal class System.Data.ProviderBase.FieldNameLookup which SqlDataReader relies on.

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));
   }

}

回答22:

How about

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

It probably would not be as efficient in a loop