Check for column name in a SqlDataReader object

Question

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

User · Answer

this works for me   bool hasColumnName   reader GetSchemaTable   AsEnumerable   Any c   gt  c  ColumnName       YOUR COLUMN NAME

User · Answer

In one line  use this after your DataReader retrieval   var fieldNames   Enumerable Range 0  dr FieldCount  Select i   gt  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   gt  string Equals dr GetName i   fieldName  StringComparison OrdinalIgnoreCase

User · Answer

if Enumerable Range 0 reader FieldCount  Select reader GetName  Contains  columName           employee EmployeeId  Utility ConvertReaderToLong reader  EmployeeId         you can get more details from here   Can you get the column names from a SqlDataReader

User · Answer

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.

User · Answer

TLDR   Lots of answers with claims about performance and bad practice  so I clarify that here     The exception route is faster for higher numbers of returned columns  the loop route is faster for lower number of columns  and the crossover point is around 11 columns   Scroll to the bottom to see a graph and test code   Full answer   The code for some of the top answers work  but there is an underlying debate here for the  better  answer based on the acceptance of exception handling in logic and it s related performance   To clear that away  I do not believe there is much guidance regarding CATCHING exceptions   Microsoft does have some guidance regarding THROWING exceptions   There they do state       DO NOT use exceptions for the normal flow of control  if possible    The first note is the leniency of  if possible    More importantly  the description gives this context    framework designers should design APIs so users can write code that does not throw exceptions    What that means is that if you are writing an API that might be consumed by somebody else  give them the ability to navigate an exception without a try catch   For example  provide a TryParse with your exception-throwing Parse method   Nowhere does this say though that you shouldn t catch an exception   Further  as another user points out  catches have always allowed filtering by type and somewhat recently allow further filtering via the when clause   This seems like a waste of language features if we re not supposed to be using them   It can be said that there is SOME cost for a thrown exception  and that cost MAY impact performance in a heavy loop   However  it can also be said that the cost of an exception is going to be negligible in a  connected application    Actual cost was investigated over a decade ago  https   stackoverflow com a 891230 852208 In other words  cost of a connection and query of a database is likely to dwarf that of a thrown exception   All that aside  I wanted to determine which method truly is faster   As expected there is no concrete answer   Any code that loops over the columns becomes slower as the number of columns exist   It can also be said that any code that relies on exceptions will slow depending on the rate in which the query fails to be found   Taking the answers of both Chad Grant and Matt Hamilton  I ran both methods with up to 20 columns and up to a 50  error rate  the OP indicated he was using this two test between different procs  so I assumed as few as two    Here are the results  plotted with LinqPad    The zigzags here are fault rates  column not found  within each column count   Over narrower result sets  looping is a good choice   However  the GetOrdinal Exception method is not nearly as sensitive to number of columns and begins to outperform the looping method right around 11 columns   That said I don t really have a preference performance wise as 11 columns sounds reasonable as an average number of columns returned over an entire application   In either case we re talking about fractions of a millisecond here   However  from a code simplicity aspect  and alias support  i d probably go with the GetOrdinal route   Here is the test in linqpad form   Feel free to repost with your own method   void Main         var loopResults   new List lt Results gt         var exceptionResults   new List lt Results gt         var totalRuns   10000      for  var colCount   1  colCount  lt  20  colCount                  using  var conn   new SqlConnection   Data Source  localdb  MSSQLLocalDb Initial Catalog master Integrated Security True                           conn Open                   create a dummy table where we can control the total columns             var columns   String Join                       new int colCount   Select  item  i    gt      i   as col i                               var sql     select  columns  into  dummyTable               var cmd   new SqlCommand sql conn               cmd ExecuteNonQuery                 var cmd2   new SqlCommand  select   from  dummyTable   conn                var reader   cmd2 ExecuteReader                reader Read                 Func lt Func lt IDataRecord  String  Boolean gt   List lt Results gt  gt  test   funcToTest   gt                                var results   new List lt Results gt                     Random r   new Random                    for  var faultRate   0 1  faultRate  lt   0 5  faultRate    0 1                                        Stopwatch stopwatch   new Stopwatch                        stopwatch Start                        var faultCount 0                      for  var testRun   0  testRun  lt  totalRuns  testRun                                                  if  r NextDouble    lt   faultRate                                                        faultCount                                if funcToTest reader   colDNE                                    throw new ApplicationException  Should have thrown false                                                      else                                                       for  var col   0  col  lt  colCount  col                                                                  if  funcToTest reader    col col                                         throw new ApplicationException  Should have thrown true                                                                                                      stopwatch Stop                        results Add new UserQuery Results                          ColumnCount   colCount                           TargetNotFoundRate   faultRate                          NotFoundRate   faultCount   1 0f   totalRuns                           TotalTime stopwatch Elapsed                                                           return results                             loopResults AddRange test HasColumnLoop                 exceptionResults AddRange test HasColumnException                           Loop  Dump        loopResults Dump          Exception  Dump        exceptionResults Dump         var combinedResults   loopResults Join exceptionResults l   gt  l ResultKey  e  gt  e ResultKey   l  e    gt  new ResultKey   l ResultKey  LoopResult l TotalTime  ExceptionResult e TotalTime        combinedResults Dump        combinedResults          Chart r   gt  r ResultKey  r   gt  r LoopResult Milliseconds   1 0   totalRuns  LINQPad Util SeriesType Line           AddYSeries r   gt  r ExceptionResult Milliseconds   1 0   totalRuns  LINQPad Util SeriesType Line           Dump      public static bool HasColumnLoop IDataRecord dr  string columnName        for  int i   0  i  lt  dr FieldCount  i                  if  dr GetName i  Equals columnName  StringComparison InvariantCultureIgnoreCase               return true            return false     public static bool HasColumnException IDataRecord r  string columnName        try               return r GetOrdinal columnName   gt   0            catch  IndexOutOfRangeException                return false           public class Results       public double NotFoundRate   get  set        public double TargetNotFoundRate   get  set        public int ColumnCount   get  set        public double ResultKey  get   gt  ColumnCount   TargetNotFoundRate       public TimeSpan TotalTime   get  set

User · Answer

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

}

User · Answer

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            lt summary gt          Checks if a column s value is DBNull          lt  summary gt           lt param name  dataReader  gt The data reader lt  param gt           lt param name  columnName  gt The column name lt  param gt           lt returns gt A bool indicating if the column s value is DBNull lt  returns gt      public static bool IsDBNull this IDataReader dataReader  string columnName                return dataReader columnName     DBNull Value                  lt summary gt          Checks if a column exists in a data reader          lt  summary gt           lt param name  dataReader  gt The data reader lt  param gt           lt param name  columnName  gt The column name lt  param gt           lt returns gt A bool indicating the column exists lt  returns gt      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   gt   0                    catch  IndexOutOfRangeException                        return false                      Usage       public static bool CanCreate SqlDataReader dataReader                return dataReader ContainsColumn  RoleTemplateId                 amp  amp   dataReader IsDBNull  RoleTemplateId              Calling r GetSchemaTable   Columns on a DataReader returns BS columns

User · Answer

How about   if  dr GetSchemaTable   Columns Contains  accounttype       do something else    do something   It probably would not be as efficient in a loop

User · Answer

this work to me  public static class DataRecordExtensions           public static bool HasColumn IDataReader dataReader  string columnName                        dataReader GetSchemaTable   DefaultView RowFilter     ColumnName    columnName                 return  dataReader GetSchemaTable   DefaultView Count  gt  0

User · Answer

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

User · Answer

Here the solution from Jasmine in one line     one more  tho simple     reader GetSchemaTable   Select  ColumnName  MyCol    Length  gt  0

User · Answer

Here is a working sample for Jasmin s idea   var cols   r GetSchemaTable   Rows Cast lt DataRow gt    Select      row   gt  row  ColumnName   as string  ToList      if  cols Contains  the column name

User · Answer

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

User · Answer

The following is simple and worked for me    bool hasMyColumn    reader GetSchemaTable   Select  ColumnName    MyColumnName    Count      1

User · Answer

Here the solution from Jasmine in one line     one more  tho simple     reader GetSchemaTable   Select  ColumnName  MyCol    Length  gt  0

User · Answer

The key to the whole problem is here   if  -1    index        throw ADP IndexOutOfRange fieldName       If the referenced three lines  currently lines 72  73  and 74  are taken out  then you can easily check for -1 in order to determine if the column doesn t exist   The only way around this while ensuring native performance is to use a Reflection based implementation  like the following   Usings   using System  using System Data  using System Reflection  using System Data SqlClient  using System Linq  using System Web Compilation     I m not sure what the  NET Core equivalent to BuildManager cs   The Reflection based extension method       Gets the column ordinal  given the name of the column       lt  summary gt       lt param name  reader  gt  lt  param gt       lt param name  name  gt The name of the column  lt  param gt       lt returns gt  The zero-based column ordinal  -1 if the column does not exist  lt  returns gt  public static int GetOrdinalSoft this SqlDataReader reader  string name        try                  Note that  Statistics  will not be accounted for in this implemenation            If you have SqlConnection StatisticsEnabled set to true  the default is false   you probably don t want to use this method            All of the following logic is inspired by the actual implementation of the framework             https   referencesource microsoft com  System Data fx src data System Data SqlClient SqlDataReader cs d66096b6f57cac74         if  name    null              throw new ArgumentNullException  fieldName             Type sqlDataReaderType   typeof SqlDataReader           object fieldNameLookup   sqlDataReaderType GetField   fieldNameLookup   BindingFlags NonPublic   BindingFlags Instance  GetValue reader           Type fieldNameLookupType          if  fieldNameLookup    null                        MethodInfo checkMetaDataIsReady   sqlDataReaderType GetRuntimeMethods   First x   gt  x Name     CheckMetaDataIsReady   amp  amp  x GetParameters   Length    0               checkMetaDataIsReady Invoke reader  null               fieldNameLookupType   BuildManager GetType  System Data ProviderBase FieldNameLookup   true  false               ConstructorInfo ctor   fieldNameLookupType GetConstructor new     typeof SqlDataReader   typeof int                  fieldNameLookup   ctor Invoke new object     reader  sqlDataReaderType GetField   defaultLCID   BindingFlags NonPublic   BindingFlags Instance  GetValue reader                        else             fieldNameLookupType   fieldNameLookup GetType             MethodInfo indexOf   fieldNameLookupType GetMethod  IndexOf   BindingFlags Public   BindingFlags Instance  null  new Type     typeof string     null            return  int indexOf Invoke fieldNameLookup  new object     name               catch                   NET Implemenation might have changed  revert back to the classic solution          if  reader FieldCount  gt  11     Performance observation by b levitt                       try                               return reader GetOrdinal name                             catch                               return -1                                  else                       var exists   Enumerable Range 0  reader FieldCount  Any i   gt  string Equals reader GetName i   name  StringComparison OrdinalIgnoreCase                if  exists                  return reader GetOrdinal name               else                 return -1

User · Answer

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            lt summary gt          Checks if a column s value is DBNull          lt  summary gt           lt param name  dataReader  gt The data reader lt  param gt           lt param name  columnName  gt The column name lt  param gt           lt returns gt A bool indicating if the column s value is DBNull lt  returns gt      public static bool IsDBNull this IDataReader dataReader  string columnName                return dataReader columnName     DBNull Value                  lt summary gt          Checks if a column exists in a data reader          lt  summary gt           lt param name  dataReader  gt The data reader lt  param gt           lt param name  columnName  gt The column name lt  param gt           lt returns gt A bool indicating the column exists lt  returns gt      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   gt   0                    catch  IndexOutOfRangeException                        return false                      Usage       public static bool CanCreate SqlDataReader dataReader                return dataReader ContainsColumn  RoleTemplateId                 amp  amp   dataReader IsDBNull  RoleTemplateId              Calling r GetSchemaTable   Columns on a DataReader returns BS columns

User · Answer

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