asked on

DbDataReader drops results

I have a scenario were my data clears out after connecting to an excel spreadsheet using DbDataReader. Below is the sample code. So if I mouse over datareader to the results view I see records at first but then disappears if I pause and does not return any records in the loop.

string Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=excelFile;Extended Properties=Excel 8.0;";

using (OleDbConnection conn = new OleDbConnection(Conn))
        {
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [sheet$]", conn);
            conn.Open();

            using (DbDataReader datareader = cmd.ExecuteReader())
            {

            }

Open in new window

ASKER CERTIFIED SOLUTION

Ravi Vaddadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

guru_sami

---> then disappears if I pause and does not return any records in the loop.
Where is your loop...or code you are trying to read data from?

ws11

ASKER

It will not loop it just goes through what the loop contains one time despite how many records I have. I have used this before but not with an excel file.

ws11

ASKER

If I stop my trace at using (DbDataReader datareader = cmd.ExecuteReader()) the results view shows my column count and all my records so it has the data but if I wait go back and look again it will clear the ienumerable.

ws11

ASKER

See images. Image1 is first view then Image2 is after. The ienumerable is clearing its self. But it should not do this when it loops until the loop is finish. At least that is what I would expect.
Image1.jpg
Image2.jpg

ws11

ASKER

So I guess something like this would continue to loop...

foreach (DbDataRecord record in reader)

guru_sami

to loop through the records you will need to use Read method like shown here:
while(datareader.Read())
{
//get values..
}

Check this: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson04.aspx

ws11

ASKER

Sorry I did not follow your initial post but figured it out that I needed to go row by row. I thought that was what ienumerables enumeration was all about. Any comments on the enumeration aspect would be greatly appreciated.

ws11

ASKER

Yes I have that already sorry for not posting that part.