如何在C#中更新MDB文件中的数千条记录 [英] How to UPDATE thousands of records in an MDB file in C#

问题描述

我最近问了一个

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.

现在我在更新类似数据库时遇到问题.在这种情况下，我实际上不想更新100,000条记录，但是从已经创建的具有100,1000条记录的MDB文件中更新大约10,000条记录.

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.

这是我的代码:

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));

更新10000条记录(仅一个字段)花费了大约24秒！

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));

我发现当我使用上面的代码时，我能够在不到一秒钟的时间内(0.4秒)更新整个表(100,000条记录).但是在此版本中，我不知道如何进行选择，仅更新表的一部分，以及如何为每个记录分配不同的值(第1册，第2册...).我的意思是我希望能够将表中的记录从4000更新到14000，然后将Book 1，Book 2和...分配给标题字段.

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.

推荐答案

为您提供另一种考虑的方法(如4dmonster在对您先前问题的评论中所建议)，这是更新Access数据库的DAO Recordset方法.它会跳过"前4,000条记录，并将接下来的10,000条更新为"Book 1"，"Book 2"，....

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", ....

在许多情况下，DAO仍然是在Access数据库上执行逐行更新的最快方法.在我的机器上，以下代码需要2.5秒才能执行.

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));
        }
    }
}

这篇关于如何在C#中更新MDB文件中的数千条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！