converting resultset from OleDbDataReader into list

Question

Consider a Winforms app connecting to a SQL Server 2008 database and running a SQL SELECT statement:

string myConnectionString = "Provider=SQLOLEDB;Data Source=hermes;Initial Catalog=qcvaluestest;Integrated Security=SSPI;";

string mySelectQuery = "SELECT top 500 name, finalconc from qvalues where rowid between 0 and 25000;";

OleDbConnection myConnection = new OleDbConnection(myConnectionString);

OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);

myCommand.Connection.Open();

OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

How can you read the results of the query into a list?

what type of list? since your returning two values from your query are looking for like an array or like a list of objects, or a list box? — Patrick, Nov 03 '10 at 17:15
As an aside, look into the `using` statement. You have some resources that implement `IDisposable`, you want to ensure those resources are properly disposed of. Best practice is to wrap such resources in `using` statements that will automatically dispose of them for you. (Your disposable resources are the `OleDb*` classes.) — Anthony Pegram, Nov 03 '10 at 17:24
@anthony thank you very much for this. can you please show me an example of what you mean — Alex Gordon, Nov 03 '10 at 17:28
@i am, actually, AJ's answer shows the `using` statement. Suffice it to say, if you have an object that implements `IDisposable`, best practice is to wrap the declaration and usage of it as such: `using (DisposableObject myObject = new DisposableObject) { \\* code that uses the object here *\ }`. The scope of the object will be limited to inside the brackets below, and the compiler will expand the code into try/finally blocks that will call `Dispose` on the object for you so that it can clean up after itself. — Anthony Pegram, Nov 03 '10 at 17:47
@i am, good question! Visual Studio doesn't naturally highlight them, after all. Check their implementation (do they have a `Dispose` method?), the documentation, etc. Rule of thumb, data access objects (`Sql*`, `OleDb*`, `Odbc*`) typically implement IDisposable. As do `Stream*` objects from the `System.IO` namespace. But ultimately, you just need to check. — Anthony Pegram, Nov 03 '10 at 18:22

score 4 · Accepted Answer · answered Nov 03 '10 at 17:16

Assume you have defined a class that is something like

class MyData
{
    public string Name {get; set;}
    public int FinalConc {get; set;} // or whatever the type should be
}

You would iterate through the results of your query to load a list.

List<MyData> list = new List<MyData>();
while (myReader.Read())
{
    MyData data = new MyData();
    data.Name = (string)myReader["name"];
    data.FinalConc = (int)myReader["finalconc"]; // or whatever the type should be
    list.Add(data);
}

// work with the list

If you just need one of the given fields, you can forego the class definition and simply have a List<T>, where T is the type of whatever field you want to hold.

pyCoder · Answer 2 · 2010-11-03T17:35:42.673

2

You can try something as (adapt it for your convenience):

public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

List<Person> dbItems = new List<Person>();

while(myReader.Read())
{
   Person objPerson = new Person();

   objPerson.Name = Convert.ToString(myReader["Name"]);
   objPerson.Age = Convert.ToInt32(myReader["Age"]);

   dbItems.Add(objPerson);
}

edited Nov 03 '10 at 17:35

answered Nov 03 '10 at 17:17

pyCoder

503
3
9

This is going to turn each row into multiple items in the list. So given two columns, the first row would be dbItems[0] and dbItems[1], the second row would be dbItems[2] and dbItems[3], etc. Is this intuitive? What if the types of the columns are not the same? – Anthony Pegram Nov 03 '10 at 17:21
@pycoder, this is good but not exactly correct, it just creates a 1 dimensional array with the first element columnname1 then the second columnname2 then the third column1, then 2, then 1...etc but i need (1, 2), (1, 2), (1,2). .... – Alex Gordon Nov 03 '10 at 17:26
The real code is something as Anthony Pegram had written in his post. Build first a class that map column one to one and then build a List of that class e memorize data in it. – pyCoder Nov 03 '10 at 17:32
@i am girl: warning about NULL fields in your query results and catch conversion exception :) – pyCoder Nov 03 '10 at 17:37

score 1 · Answer 3 · answered Nov 03 '10 at 17:19

List of what? Do you have a class setup that has properties for name and finalconc? Saying you do, and it looks like this:

public class QueryResult
{
    public string Name { get; set; }
    //not sure what finalconc type would be, so here just using string
    public string FinalConc { get; set; }
}

Then you would do something like this:

var queryResults = new List<QueryResult>();
using(var myReader = myCommand.ExecuteReader())
{
    while(myReader.Read())
    {
        queryResults.Add(new QueryResult
            { 
                Name = myReader.GetString(myReader.GetOrdinal("name")), 
                FinalConc = myReader.GetString(myReader.GetOrdinal("finalconc"))
            });
    }
}

converting resultset from OleDbDataReader into list

3 Answers3

Linked