0

i am using the OleDbDataReader to read a column from an Access database and i want to be able to use the same reader to read the column again in the same function because i am doing some comparison between 2 databases and some times the identical records in the databases are not in the same order so i've to loop through the database until i find the specified record then compare.

questions are

is there a function to get the current row index of an OleDbDataReader?

and how do i reset the OleDbDataReader to the first row?

FPGA
  • 3,525
  • 10
  • 44
  • 73

3 Answers3

0

According to the MSDN, the OleDbDataReader class

Provides a way of reading a forward-only stream of data rows from a data source.

That means that there is no way to go back to a previous row. All you can do is read the current row or move to the next one. If you need to be able to randomly access any row, the typical solution is to load all the rows into a list, such as a DataTable and then process the rows in the list.

Steven Doggart
  • 43,358
  • 8
  • 68
  • 105
0

You can't reset the reader to the start because it is designed for forward-only reading.

There are a couple of other ways to approach this kind of problem:

  1. If the set of data you are comparing is reasonably sized and you are comparing on one field, you could cache the data from one database in a keyed object, such as a generic dictionary. Then, as you are reading data from the second database, you could fetch the details from the dictionary based on the key and perform the comparison that way.

  2. You could issue standalone queries for each record in one database to see if there is a match in the second database rather than having nested readers.

competent_tech
  • 44,465
  • 11
  • 90
  • 113
  • will try this and let you know :) – FPGA Aug 01 '12 at 17:45
  • about the reasonably sized part..each database has about 36043 records..is that reasonabily sized? – FPGA Aug 01 '12 at 17:48
  • I think that depends on how much information you need to store and the target profile of the machine the app's going to be running on. For example, if each record is 100 bytes, then you are talking about roughly 3MB of data which is reasonable. However, if each record is 10,000 bytes, that is more along the lines of 300MB, which may be reasonable on a dedicated machine with 8GB of memory, but not so much on an end user's machine with 1GB. – competent_tech Aug 01 '12 at 17:52
  • good, what i am trying to do is to make changes to database1 based on changes done to database2 using Microsoft Access. changes include added rows, removed rows and changed data for a specific row, i've done it with much looping but its really slow, so can i use queries for this and will it be faster? main problem with loops is that rows are not in the same order – FPGA Aug 01 '12 at 18:02
  • I would definitely try to use queries instead of loops if possible. In general when faced with this kind of problem, I try to think about how I can design the solution to reduce the overall amount of code that is executed, both in the app and in the database. For example, you may find that it is just as quick to perform the first-level comparison in SQL rather than pulling the record from the second DB into memory and then comparing there. – competent_tech Aug 01 '12 at 18:08
0

I'm solving a similar problem, and using a DataTable was the easiest way around he forward-only issue with the data readers.

            DataTable table = new DataTable("DataTable");
            for (int colNum = 0; colNum < reader.FieldCount; colNum++)
            {
                DataColumn col = new DataColumn();
                col.ColumnName = reader.GetName(colNum);
                table.Columns.Add(col);
            }//colNum
            while (reader.Read())
            {
                DataRow row = table.NewRow();
                for (int colNum = 0; colNum < reader.FieldCount; colNum++)
                {
                    string columnName = reader.GetName(colNum);
                    row[columnName] = reader.GetValue(colNum);
                }//for
                table.Rows.Add(row);
            }//while
            table.AcceptChanges();

Carlos A Merighe.

Carlos A Merighe
  • 1