How to return a data from while c# using oracle 11g

user_4090733 0
Viewed 71

I need to return all values from my table. How to write a code inside while?

var stringConnection = "Data Source = X; User Id = X; Password = X";

var sql = "SELECT * FROM TABLE";

OracleConnection _oracleConnection = new OracleConnection(stringConnection);

_oracleConnection.Open();

OracleCommand cmd = new OracleCommand(sql, _oracleConnection);

var dr = cmd.ExecuteReader();

var list = new List<dynamic>();

while(dr.Read())
{
    // my doubt is here           
}
return list;
asp.net c#
2 Answers
var stringConnection = "Data Source = X; User Id = X; Password = X";

var sql = "SELECT * FROM TABLE";

OracleConnection _oracleConnection = new OracleConnection(stringConnection);

_oracleConnection.Open();

OracleCommand cmd = new OracleCommand(sql, _oracleConnection);

var dr = cmd.ExecuteReader();

var list = new List<dynamic>();

while(dr.Read())
{
    // **** read column name data from table ****
    string Id = (string)dr["Id"];
    string company = (string)dr["company"];
    string city    = (string)dr["City"];
    
    var objItem = new { Id = Id, company = company, city = "city" };
    list.Add(objItem);
    
}
return list;
user_162035790

You have several options. 1 - load DataSet from OracleDataReader. There you will have all your data.

2 - you can still use select *... but you need a model. Then create List<SomeModel> instead of List<dynamic> with

while (reader.Read())
{
     model.Property = reader["columnName"]; // will need convert type and take care of DB null. Can use existing extnsions
     . . . . 
}

3 - For arbitrary number of columns use OracleDataReader.FieldCount and some storage like List<object[]>

var data = new List<object[]>();
var fCnt = reader.FieldCount;
while (reader.Read())
{
    var arr = new Object[fCnt];
    for(int i = 0; i < fCnt; i++)
        arr[i] = reader[i];
    data.Add(arr);
}

The unfortunate part with #3 is that in the end you can get jagged array and not 2-dimentional one but you now have enough info to convert it. But I don't remember when I needed to do #3. So think about #1 and #2

And one more thing - absolutely no need for dynamic here. Stay away.

user_17044580
Related