Update MS Access db in C# best way

Question

My update code:

 for (int i = this.MedaxilGridView1.CurrentCell.RowIndex; i < this.MedaxilGridView1.RowCount; i++)
 {
     // KartsifarishiGridView-dən id götürüb ona uyğun sorğumuzu yazırıq.
     sqlSorgu = "UPDATE customer set medaxil_status = '0' WHERE id = " + 
                 this.MedaxilGridView1.Rows[i].Cells["id"].Value;
     //Sorğunu icra edirk.
     Program.esas.sqlSorguCommand.CommandText = sqlSorgu;
     Program.esas.sqlSorguCommand.Connection = Program.esas.bazayaQosul;
     Program.esas.sqlSorguCommand.ExecuteNonQuery();
     MedaxilGridView1.Rows[i].DefaultCellStyle.BackColor = Color.Empty;

 }

Row count : 18286

This version takes 5 minutes

How do I make it faster?

you mean you have to update all the row with medaxil_status = '0' ? — Felice Pollano
– Felice Pollano, Commented Jun 19, 2012 at 7:09
You can try using a stored procedure. It will work a bit faster. Before that you can try executing the single query "UPDATE customer set medaxil_status = '0' WHERE id = " + this.MedaxilGridView1.Rows[i].Cells["id"].Value;" with some real value and check how long it takes. This will help you realise which part takes too much time.the loop or the sql itself. — Ozgur Dogus
– Ozgur Dogus, Commented Jun 19, 2012 at 7:10
You should also look into parameterized queries to make sure you're code isn't vurnerable to sql-injection. — albertjan
– albertjan, Commented Jun 19, 2012 at 7:10

Felice Pollano · Accepted Answer · 2012-06-19 08:41:30Z

1

You should think which query you use to extract the id's and use it directly into the update, so you call the database once:

"UPDATE customer set medaxil_status = '0' WHERE id in (select xxx xxx xxx)"

if you want to update all the rows, just remove the where clause and call the statement just once. If you have just an id list, maybe chunking the calls by using alwais the IN clause will reduce the number of queryes and hopefully the overall execution time.

edited Jun 19, 2012 at 8:41

answered Jun 19, 2012 at 7:10

Felice Pollano

33.4k10 gold badges77 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1205115 Over a year ago

@Ozgur Dogus Abi bir misal koy bakim

user1205115 Over a year ago

All interlinear not. Selected row - last row

Shai · Accepted Answer · 2012-06-19 07:07:38Z

0

You can use BeginExecuteNonQuery for that, that will save you the connection initiation overhead, by opening one connection, and batch executing all of your queries.

A full example from MSDN can be found here

Furthermore, I would advise you to start using SQL Parameters

answered Jun 19, 2012 at 7:07

Shai

25.7k9 gold badges48 silver badges69 bronze badges

1 Comment

user1205115 Over a year ago

@FelicePollano Yes you can. All I want to 0

Community · Accepted Answer · 2017-05-23 12:17:04Z

0

Have you tried to enclose your loop in a Transaction ? This speeds up MS Access update operations.

Take a look on this sample here that shows how to use MS Access Transaction in C#

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Jun 19, 2012 at 7:28

Larry

18.1k9 gold badges83 silver badges108 bronze badges

Collectives™ on Stack Overflow

Update MS Access db in C# best way

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related