14
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 can't seem to figure out how to use the type specfic retrieval methods by column name other than this way. please tell me there is a better way. this is specificly for DB2 but i would like the solution to work for MS Sql also if possible

KellCOMnet
  • 1,859
  • 3
  • 16
  • 27

2 Answers2

20

You're looking for the GetOrdinal method:

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

I generally cache the 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));
    }
}
// ...
LukeH
  • 263,068
  • 57
  • 365
  • 409
  • Ah, the "var ordinals" is a nice "on the fly" trick. As an alternate idea, I just create a "constants" lookup file, and I can avoid the "GetOrdinal" penalty altogether. public class EmployeeColumnLookups { public const int EmployeeKey = 0; public const int LastName = 1; public const int FirstName = 2; } << something like that. but I like this "on the fly" "but pay the price only once per run" approach. – granadaCoder Mar 29 '23 at 15:42
2

Using SqlDataReader from Assembly System.Data.SqlClient, you can do the following (example):

List<string> list = new();
string query = "SELECT * FROM [YourTable]";
using SqlConnection conn = new(YourConnectionString);
using SqlCommand cmd = new(query, conn);
conn.Open();
using SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read() && reader.HasRows)
{
    int columnNumber = reader.GetOrdinal("ColumnName")
    list.Add(reader.GetString(columnNumber));
}

The method GetOrdinal(string) from the object SqlDataReader, accept the column name you desire to get and return the column number. That number can be use for the method GetString(int)

Yaron Binder
  • 107
  • 1
  • 1
  • 9
  • You can do this, but you violate the software principle "Code to an interface, not a concrete" or the "L" of "SOLID". https://blog.knoldus.com/what-is-liskov-substitution-principle-lsp-with-real-world-examples/ Liskov Substitution Principle Simply put, the Liskov Substitution Principle (LSP) states that objects of a superclass should be replaceable with objects of its subclasses without breaking the application. In other words, what we want is to have the objects of our subclasses behaving the same way as the objects of our superclass. – granadaCoder Mar 29 '23 at 15:44