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
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.
string ColumnValue; if (dr["ColumnName"] != null) ColumnValue = dr["ColumnName"].
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.
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)
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));
}
}
// ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With