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

I have recently asked one to include 100,000 records in an MDB file in C #. It was possible to reduce the required time to less than 2 secs by using the number tables for 45 seconds to 10 seconds.

Now I have a problem updating the same database. I do not really want to update 100,000 records in this case, but about 10,000 records from the previously created MDB file with 100,1000 records.

Here's 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, conn); Da Fill (DS, "Books table"); For (Int i = 0; I <10000; i ++) {ds.Tables [0]. Rove [i] [1] = "book" + i.ToString (); } OleDbCommandBuilder cb = New OleDbCommandBuilder (da); Da.UpdateCommand = cb.GetUpdateCommand (); Da.Update (DS, "Books Table"); Con.Close (); Sw.Stop (); Console.lightline (string.format ("{0: 0.0} seconds", sw.Elapsed millisecond / 1000.0));  

Updating 10000 records (only one field) took about 24 seconds!

I have another code that does 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; Olebey Commands CMD = New Oleadby Commands (); Cmd.Connection = con; Con.Open (); Cmd.CommandText = "update tblBooks SET [title] = @title"; Cmd.Parameters.AddWithValue ("Title", "Book"); Cmd.ExecuteNonQuery (); Con.Close (); Sw.Stop (); Console.lightline (string.format ("{0: 0.0} seconds", sw.Elapsed millisecond / 1000.0));  

I came to know that when I use the above code, I am able to update the entire table (100,000 records) in less than a second (0.4 seconds). But in this version, I do not know how to be selective and just update the part of the table and how to provide different values ​​in each record (book 1, book2 ...). I mean that I want to be able to update from table 4000 to 14000 records for example and assign to book 1, book 2 and ... for title field.

Just to consider you a second option (as 4 months in a comment on your first question Has been suggested) Here is the DAO Recordset for updating an Access Database. It first "releases" 4,000 archives and updates the next 10,000 as "Book 1", "Book 2" .... ....

DAO still in many cases -Repair update on an Access database takes 2.5 seconds to execute the following code on my machine.

  using the system; Using System.Collections.Generic; Using System.Linq; Using System.Text; Using DAO; Namespace daoConsoleApp {class} program {static zero main (string [] args) {var sw = new system. Diagnostics .Topwatch (); 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: \ user \ guard \ desktop \ speed.mdb"); Recorset RST = DB OpenRexSetset ("Select Top 4001 ID from TBI Books", Records Type ANIM DB OpenSnapSpot); Rst.MoveLast (); Int startID = rst.fields ["id"]. Values; Rst.Close (); Rst = db.OpenRecordset (String.Format ("Select the top 10000 headings from TblBooks where id => {0} order according to 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.lightline (string.format ("{0: 0.0} seconds", sw.Elapsed millisecond / 1000.0)); }}}