How To Check if DataReader has certain field

How To Check if DataReader has certain field

March 21, 2008 02:17 by pradeep.mishra
During development I encountered this problem. So here I am posting a solution for the same. Suppose a reader is reading result set which normally returns fields ‘A’ ‘ B’ AND ‘C’ and in some cases it also returns ‘W’.So your dataread method is likeLet’s have some object for the data to persist. 
   1:   
   2:  Public Object DemoObject
   3:  {
   4:  //string Property A
   5:  //string Property B
   6:  //string Property C
   7:  //string Property W
   8:  //Constructor
   9:  Object(string a, string b, string c)
  10:  {
  11:  A = a;
  12:  B = b;
  13:  C = c;
  14:  W = string.Empty;
  15:  }
  16:  Object(string a, string b, string c, string w)
  17:  {
  18:  A = a;
  19:  B = b;
  20:  C = c;
  21:  W = w;
  22:  }
  23:  }
  24:  private DemoObject DataRead(SqlDataReader reader)
  25:  {
  26:  //Some Code
  27:  //
  28:  returns object as Object
  29:  }
  30:  private Object DataRead(SqlDataReader reader)
  31:  {
  32:  //Some Code
  33:  //
  34:  if(reader["A"]!=null)
  35:  string a = reader.GetString("A");
  36:  if(reader["A"]!=null)
  37:  string b = reader.GetString("B");
  38:  if(reader["A"]!=null)
  39:  string c = reader.GetString("C");
  40:  //Following line of code throws IndexOutOfRangeException
  41:  if(reader["W"]!=null)
  42:  string w = reader.GetString("W");
  43:  //Some Code
  44:  //......
  45:  returns new DemoObject(a,b,c,w);
  46:  }
It has the GetOrdinal() method as well, but it throws an exception if the reader doesn't contain the field. So the solution is to use GetSchemaTable. It returns a table holding the schema of the reader. There is one row in the table for each column returned in the reader, and the columns of the schema table define properties of the reader's result set, such as the column name, size, data type and so on. We need to filter the rows in that table to just the row matching the column we want, theschema table holds 1 row per column. The easiest way to do this is with the default view. e.g. if I were looking for a row called "myrow" in a reader's results, I could do this DataView myView = reader.GetSchemaTable().DefaultView;myView.RowFilter = "ColumnName = 'myrow' ";So final set of code is 
   1:   
   2:  //Create a method which verifies if a column exists in a particular row being read by datareader
   3:  private bool ColumnExists(SqlDataReader reader, string columnName)
   4:  {
   5:  reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
   6:  return (reader.GetSchemaTable().DefaultView.Count > 0);
   7:  }
   8:  So now ReadData method will look like
   9:  private Object DataRead(SqlDataReader reader)
  10:  {
  11:  //Some Code
  12:  //
  13:  if(reader["A"]!=null)
  14:  string a = reader.GetString("A");
  15:  if(reader["A"]!=null)
  16:  string b = reader.GetString("B");
  17:  if(reader["A"]!=null)
  18:  string c = reader.GetString("C");
  19:  //Check only those columns where you have doubts
  20:  if(ColumnExists(reader, "W"))
  21:  {
  22:  if( reader["W"]!=null)
  23:  string w = reader.GetString("W");
  24:  }
  25:  //Some Code
  26:  //......
  27:  returns new DemoObject(a,b,c,w);
  28:  }

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Tags: , ,
Categories: Technical
Actions: E-mail | Permalink | Comments (1434) | Comment RSSRSS comment feed