DataReader.GetString () via column name

Question

  Dictionary Fields = new Dictionary ();
 for (int i = 0; i <reader.FieldCount; i ++)
 {
      Fields.Add (reader.GetName (i), i);
 }

 this._MyField1 = reader.GetString (Fields ["field1"]);
 this._Myfield2 = reader.GetInt16 (Fields ["field2"]);

doing this makes me want to cry, but I cannot figure out how to use specfic type methods by column name other than this. say there is a better way. this is specifically for DB2, but I would like a solution for MS Sql to be possible as well,

Answer 1

You are looking for the GetOrdinal method:

 this._MyField1 = reader.GetString(dr.GetOrdinal("field1")); this._Myfield2 = reader.GetInt16(dr.GetOrdinal("field2")); 

I usually cache ordinals in an anonymous type for performance and readability:

 // ... using (IDataReader dr = cmd.ExecuteReader()) { var ordinals = new { Foo = dr.GetOrdinal("Foo"), Bar = dr.GetOrdinal("Bar") }; while (dr.Read()) { DoSomething(dr.GetString(ordinals.Foo), dr.GetInt16(ordinals.Bar)); } } // ...