How to UPDATE thousands of records in an MDB file in C#

Question

I recently asked a question about how to insert 100,000 records in an MDB file in C#. The given answers reduced the required time from 45 secs to 10 secs. It was even possible to be reduced to 2~3 secs using Number Tables.

Now I have problem updating a similar database. I don't want to actually update 100,000 records in this case but around 10,000 records from the already created MDB file with 100,1000 records.

Here is my code:

Stopwatch sw = new Stopwatch();
sw.Start();
OleDbConnection con = new OleDbConnection();
string dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;";
string dbSource = "Data Source = D:/programming/sample.mdb";
con.ConnectionString = dbProvider + dbSource;
con.Open();
string query = "SELECT * FROM tblBooks";

DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(query, con);
da.Fill(ds,"Books Table");
for (int i = 0; i < 10000; i++)
{
    ds.Tables[0].Rows[i][1] = "Book" + i.ToString();    
}


OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(ds, "Books Table");

con.Close();
sw.Stop();
Console.WriteLine(String.Format("{0:0.0} seconds", sw.ElapsedMilliseconds / 1000.0));

Updating 10000 records (only one field) took around 24 secs!

I have another code that performs well:

Stopwatch sw = new Stopwatch();
sw.Start();
OleDbConnection con = new OleDbConnection();
string dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;";
string dbSource = "Data Source = D:/programming/sample.mdb";
con.ConnectionString = dbProvider + dbSource;
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText = "UPDATE tblBooks SET [Title] = @title";
cmd.Parameters.AddWithValue("@title", "Book");
cmd.ExecuteNonQuery();
con.Close();
sw.Stop();
Console.WriteLine(String.Format("{0:0.0} seconds", sw.ElapsedMilliseconds / 1000.0));

I found out that when I use the above code I'm able to update the whole table (100,000 records) in less than a second (0.4 sec). But in this version, I don't know how to be selective and only update part of the table and how to assign different values to each record (Book 1, Book 2...). I mean I want to be able to update for example from record 4000 to 14000 in the table and assign Book 1, Book 2 and ... to title field.

Answer 1

Just to give you another option to consider (as suggested by 4dmonster in a comment to your earlier question) here is the DAO Recordset way of updating an Access database. It "skips" the first 4,000 records and updates the next 10,000 as "Book 1", "Book 2", ....

In many cases DAO is still the fastest way to perform row-by-row updates on an Access database. On my machine the following code takes 2.5 seconds to execute.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAO;

namespace daoConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            var sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            // This code requires the following COM reference in your project:
            //     Microsoft DAO 3.6 Object Library
            //
            var dbe = new DBEngine();
            Database db = dbe.OpenDatabase(@"C:\Users\Gord\Desktop\speed.mdb");
            Recordset rst = db.OpenRecordset(
                    "SELECT TOP 4001 ID FROM tblBooks ORDER BY ID",
                    RecordsetTypeEnum.dbOpenSnapshot);
            rst.MoveLast();
            int startID = rst.Fields["ID"].Value;
            rst.Close();
            rst = db.OpenRecordset(
                    String.Format(
                        "SELECT TOP 10000 Title FROM tblBooks WHERE ID >= {0} ORDER BY ID", 
                        startID),
                    RecordsetTypeEnum.dbOpenDynaset);
            int i = 1;
            while (!rst.EOF)
            {
                rst.Edit();
                rst.Fields["Title"].Value = String.Format("Book {0}", i++);
                rst.Update();
                rst.MoveNext();
            }
            rst.Close();

            sw.Stop();
            Console.WriteLine(String.Format("{0:0.0} seconds", sw.ElapsedMilliseconds / 1000.0));
        }
    }
}