Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DataReader.GetString() via columnname

Tags:

sql

datareader

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

like image 933
KellCOMnet Avatar asked Jul 12 '10 13:07

KellCOMnet


People also ask

How to read data from DataReader?

To retrieve data using a DataReader, create an instance of the Command object, and then create a DataReader by calling Command. ExecuteReader to retrieve rows from a data source.

How do I check if a DataReader has a column?

string ColumnValue; if (dr["ColumnName"] != null) ColumnValue = dr["ColumnName"].

How to use DataReader in vb net?

DataReader is a readonly, forward only and connected recordset from the database. In DataReader, database connection is opened until the object is closed unlike DataSet. Using DataReader we can able to access one row at a time so there it is not required storing it in memory.


2 Answers

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)

like image 50
Yaron Binder Avatar answered Sep 19 '22 04:09

Yaron Binder


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));
    }
}
// ...
like image 28
LukeH Avatar answered Sep 22 '22 04:09

LukeH