Reading from a Select Statement

Reading from a Select Statement

• Tony B

  You are doing a double cast. GetSqlInt32 returns a SqlInt32. You then try to convert that to a int which won't work.

  Do one of the following:
  
  1) int loop2 = rdr.GetInt32(0);

  2) SqlInt32 val = rdr.GetSqlInt32(0);
  int loop2 = val.IsNull 0 : val.value;

  3) int loop2 = Convert.ToInt32(rdr(0));

  I'd lean toward #1 myself unless the column can be null. In that case use #2.

  Michael Taylor - 8/4/06
  

  
  

• B.Young

  Hi Taylor

  Thanks for your reply. Here the select statement does not contain rows from a table.The select statement is something like this:

  select Day(DateAdd(day, -1, DateAdd(Month, 1, Convert(DateTime,@Temp))))

  How the reader will read the data for the above select statement.

  Thanks!

  Santhosh

  
  
  

• JohnDB

  You can use either a DataReader or a DataSet. Data readers are faster but require more code on your part. Given the following select statement:
  
  SELECT id, name, createDate FROM someTable
  
  You can use a data reader as follows (assuming you already built the command)
  
  using(SqlDataReader rdr = cmd.ExecuteReader())
  {
  if (rdr != null)
  {

  while (rdr.Read()) //Read through each returned row of the result set
  {
  //You should probably move your parsing code to a separate function
  int id = Convert.ToInt32(rdr("id"));
  string strName = Convert.ToString(rdr("name"));
  
  DateTime dtCreated = rdr.GetDateTime(2);
  };
  };
  };

  Notice that in the above example I used two different methods for obtaining the field values. The first method used an indexer off the rdr object itself. In this version you can use either the zero-based ordinal of the column or the name of the column. The returned value is an object so you then have to use Convert to convert the value.
  
  The second method I used was the GetXYZ method of the reader. There is a version for each of the major types. This does the conversion for you but only works with ordinal values.

  It is up to you to decide which version is better. I prefer names because they convey more meaning and are independent of the order in which the columns are returned. In the case of ordinals if you change the order then you have to modify your code. Unfortunately using name for field lookups is slow (at least in v1.1) because the reader has to search for the field first. Therefore performance will suffer.

  Personally I have written a custom data reader class that wraps the SqlDataReader (actually any reader) and exposes all the standard methods plus overloads of GetXYZ that accept field names. This gives you the best of both worlds as you can use named colums without typecasting. To get over the field lookup issue I internally build a lookup table using a dictionary that maps each field name to its ordinal. This lookup table is auto-generated the first time any method is called that requires a field to be looked up. This provides the best performance possible and is only slightly slower than direct ordinal lookup. I would recommend that you create a similar class.

  Michael Taylor - 8/3/06
  
  

  
  

• p_dinesh

  When you use a SELECT statement in comes back as a result set irrelevant of whether you looked it up in a table or generated it as you did above. You would use the same logic. The only difference in your case is the fact that you really don't have a field name so you must use an ordinal to get the value.
  
  using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
  {
  if ((rdr != null) && rdr.Read())
  result = rdr.GetXYZ(0);
  };
  
  Michael Taylor - 8/3/06
  

• Janet666

  Building a select statement is different than retrieving data and therefore requires separate blocks of code. Personally I use a special query builder class for building select statements.

  As for the data reader MS made a change in v2.0 to optimize lookup of fields by name. A hash table is used behind the scenes to map field names to ordinals. This is what the class that I wrote (actually I stole the idea from the original data access block and enhanced it) did. My v2 version simply uses GetOrdinal to map a field name to an ordinal and then calls the base implementation from the data reader. Basically my class wraps an existing data reader and adds methods to get values from the reader by name in addition to by ordinal. Although SqlDataReader is extensible I did not derive from it because my class is generic. Therefore I use aggregation to nest any data reader and expose the functionality.
  
  public sealed class DataReader : DbDataReader
  {
  public DataReader ( DbDataReader reader )
  {
  if (reader == null)
  throw new ArgumentNullException("reader");
  
  m_Reader = reader.IsClosed null : reader;
  }
  
  //For each type that should be exposed create simple methods like so:
  public override bool GetBoolean ( int ordinal )
  { return GetBoolean(ordinal, false); }
  
  public bool GetBoolean ( string name )
  {
  int ordinal = GetOrdinal(name);
  if (ordinal < 0)
  throw new ArgumentException("Field does not exist.", "name");
  
  return GetBoolean(ordinal, false);
  }
  
  //The default versions are optional but I like them because I don't have to worry about NULL
  public bool GetBoolean ( int ordinal, bool defaultValue )
  {
  if (IsClosed)
  throw new InvalidOperationException("The reader is closed.");
  
  return m_Reader.IsDBNull(ordinal) defaultValue : m_Reader.GetBoolean(ordinal);
  }
  public bool GetBoolean ( int ordinal, bool defaultValue )
  {
  if (IsClosed)
  throw new InvalidOperationException("The reader is closed.");
  
  return m_Reader.IsDBNull(ordinal) defaultValue : m_Reader.GetBoolean(ordinal);
  }
  
  //Standard DbDataReader members implemented through the m_Reader field
  }

  Now the query build that I use hides the details of selecting tables and building properly formatted joins. I used it in a free-from query parser that I wrote years ago. It hides the details of including tables and whatnot. It can't handle all cases but it handles general queries. Unfortunately this class is about 400 lines long and hasn't been updated to v2.0 yet. If you would like a copy then I'll be glad to send it to you. Please e-mail me through my profile and I'll send you a copy.

  Michael Taylor - 8/6/06

  
  

• xluna

  Hi Taylor

  The return type of day is int as per SQL help. when i use the below statement inside the while loop of reading reader.

  int Loop2 = Convert.ToInt32(rdr.GetsqlInt32(0));

  Iam getting a runtime error as saying that specified cast is not valid.

  Can you please let me know the reason for this.

  Thanks in Advance!

  
  
  

• MitchPetel

  Hi Michael,
  
  thanks for the tips in this post. Can you elaborate a bit on your custom data reader class, or better yet, do you have an example I need to allow a user select from a list of field names, build a SELECT statement and come up with a DataReader. I'm not sure what is the best approach on this. Thanks in advance.
  
  Best,
  Matt
  
  
  
  
• Reading from a Select Statement