How to read all new rows from database?

Question

I am trying to read all new rows that are added to the database on a timer.

First I read the entire database and save it to a local data table, but I want to read all new rows that are added to the database. Here is how I'm trying to read new rows:

string accessDB1 = string.Format("SELECT * FROM {0} ORDER BY ID DESC", tableName);
setupaccessDB(accessDB1);

int dTRows = localDataTable.Rows.Count + 1;
localDataTable.Rows.Add();

using (readNext = command.ExecuteReader())
{
    while (readNext.Read())
    {
        for (int xyz = 0; xyz < localDataTable.Columns.Count; xyz++)
        {
            // Code
        }
        break;
    }
}

If only 1 row is added within the timer then this works fine, but when multiple rows are added this only reads the latest row.

So is there any way I can read all added rows.

I am using OledbDataReader.

Thanks in advance

Answer 1

Your immediate problem lies here:

while (readNext.Read())
{
    doSomething();
    break;
}

This is what your loop basically boils down to. That break is going to exit the loop after processing the first item, regardless of how many items there are.

The first item, in this case, will probably be the last one added (as you state it is) since you're sorting by descending ID.

In terms of reading only newly added rows, there are a variety of ways to do it, some which will depend on the DBMS that you're using.

Perhaps the simplest and most portable would be to add an extra column processed which is set to false when a row is first added.

That way, you can simply have a query that looks for those records and, for each, process them and set the column to true.

In fact, you could use triggers to do this (force the flag to false on insertion) which opens up the possibility for doing it with updates as well.

Tracking deletions is a little more difficult but still achievable. You could have a trigger which actually writes the record to a separate table before deleting it so that your processing code has access to those details as well.

Answer 2

The following works

using (readNext = command.ExecuteReader())
{
    while (readNext.Read())
    {
        abc = readNext.FieldCount;
        for (int s = 1; s < abc; s++)
        {
            var nextValue = readNext.GetValue(s);
        }
    }

}

The For Loop reads the current row and then the While Loop moves onto the next row

Answer 3

For most tables the primary key is based an incremental value. This can be a very simple integer that is incremented by one, but it could also be a datetime based guid.

Anyway if you know the id of the last record. You can simple ask for all records that have a 'higher' id. In that way you do get the new records, but what about updated records? If you also want those you might want to use a column that contains a datetime value.

A little bit more trickier are records that are deleted from the database. You can't retrieve those with a basic query. You could solve that by setting a TTL for each record you retrieve from the database much like a cache. When the record is 'expired', you try to retrieve it again.

Some databases like Microsoft SQL Server also provide more advanced options into this regard. You can use query notifications via the broker services or enable change tracking on your database. The last one can even indicate what was the last action per record (insert, update or delete).