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

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

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

public static class DataRecordExtensions       public static bool HasColumn this 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            Using Exceptions for control logic like in some other answers is considered bad practice and has performance costs  It also sends false positives to the profiler of   exceptions thrown and god help anyone setting their debugger to break on exceptions thrown   GetSchemaTable   is also another suggestion in many answers  This would not be a preffered way of checking for a field s existance as it is not implemented in all versions  it s abstract and throws NotSupportedException in some versions of dotnetcore   GetSchemaTable is also overkill performance wise as it s a pretty heavy duty function if you check out the source   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

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

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 lt string gt  GetColumnNames SqlDataReader r        List lt string gt  ColumnNames   new List lt string gt         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 lt string gt  ExecuteColumnNamesReader string command  List lt SqlParameter gt  Params        List lt string gt  ColumnNames   new List lt string gt         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  lt  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

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

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  amp  amp  IndexOf reader  name   gt   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

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

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

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

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 lt string gt  GetColumnNames SqlDataReader r        List lt string gt  ColumnNames   new List lt string gt         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 lt string gt  ExecuteColumnNamesReader string command  List lt SqlParameter gt  Params        List lt string gt  ColumnNames   new List lt string gt         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  lt  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

User · Answer

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

User · Answer

public static class DataRecordExtensions       public static bool HasColumn this 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            Using Exceptions for control logic like in some other answers is considered bad practice and has performance costs  It also sends false positives to the profiler of   exceptions thrown and god help anyone setting their debugger to break on exceptions thrown   GetSchemaTable   is also another suggestion in many answers  This would not be a preffered way of checking for a field s existance as it is not implemented in all versions  it s abstract and throws NotSupportedException in some versions of dotnetcore   GetSchemaTable is also overkill performance wise as it s a pretty heavy duty function if you check out the source   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

User · Answer

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  gt  0 AndAlso dr GetOrdinal  ColumnToBeChecked    lt  gt  -1 Then   obj ColumnToBeChecked   ColumnFromDb dr   ColumnToBeChecked   End If

User · Answer

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

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

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

User · Answer

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 lt string gt   lstString   public void GetValueByParameter lt T gt  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  amp  amp   parameterName     DBNull Value              returnValue    T dr parameterName             catch  Exception ex                Logger Instance LogException this  ex                 lt summary gt      Reset the global list of columns to reflect the fields in the IDataReader      lt  summary gt       lt param name  dr  gt The IDataReader being acted upon lt  param gt       lt param name  NextResult  gt Advances IDataReader to next result lt  param gt  public void ResetSchemaTable IDataReader dr  bool nextResult        if  nextResult          dr NextResult          lstString   new List lt string gt          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

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

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

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

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

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

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

To keep your code robust and clean  use a single extension function  like this       Public Module Extensions           lt Extension   gt          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

User · Answer

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

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

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

User · Answer

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

User · Answer

Hashtable ht   new Hashtable        Hashtable CreateColumnHash SqlDataReader dr                ht   new Hashtable            for  int i   0  i  lt  dr FieldCount  i                          ht Add dr GetName i   dr GetName i                      return ht             bool ValidateColumn string ColumnName                return ht Contains ColumnName

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  amp  amp  IndexOf reader  name   gt   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

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

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 lt string gt   lstString   public void GetValueByParameter lt T gt  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  amp  amp   parameterName     DBNull Value              returnValue    T dr parameterName             catch  Exception ex                Logger Instance LogException this  ex                 lt summary gt      Reset the global list of columns to reflect the fields in the IDataReader      lt  summary gt       lt param name  dr  gt The IDataReader being acted upon lt  param gt       lt param name  NextResult  gt Advances IDataReader to next result lt  param gt  public void ResetSchemaTable IDataReader dr  bool nextResult        if  nextResult          dr NextResult          lstString   new List lt string gt          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

User · Answer

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

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

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