question

RezaJaferi avatar image
0 Votes"
RezaJaferi asked RezaJaferi commented

How to delete tens of thousands of rows using different IDs (array) in an "MS-Access" query, with or without using the "LINQ" commands?

Hi,
I want to delete rows from an MS-Access database using an array of different IDs.
The Length of this array might vary based on DataGrid.SelectedItems.
The values of this array may also vary based on DataGrid.SelectedItems.
I tried numerous different codes, but each one has its own problems.
Note: in the following solutions, BookCodeSelectedItems is an array of uint type.
First solution (This code works correctly when the array length is not large, but for example, if the array length is 30,000, it displays the error Query is too complex):

 OleDbCommand OleDbCommand_Delete = new OleDbCommand(string.Join(null, "Delete From BookTable Where BookCode In (",string.Join(",",BookCodeSelectedItems),")"), OleDbConnect);

Second solution (When the array length is large, this code runs very slowly):

 for (int i = 0; i < BookCodeSelectedItems.Length; i++)
 {
     OleDbCommand OleDbCommand_Delete = new OleDbCommand(string.Join(null, "Delete From BookTable Where BookCode = @BookCodeSelectedItems"), OleDbConnect);
     OleDbCommand_Delete.Parameters.AddWithValue("@BookCodeSelectedItems", BookCodeSelectedItems[i]);
     OleDbCommand_Delete.ExecuteNonQuery();
 }

Third solution (This code does not work properly if the user selects random rows):
223258-selected-rows.png

 OleDbCommand OleDbCommand_Delete = new OleDbCommand(string.Join(null, "Delete From BookTable Where BookCode Between @BookCodeSelectedItemsFirst And @BookCodeSelectedItemsLast"), OleDbConnect);
 OleDbCommand_Delete.Parameters.AddWithValue("@BookCodeSelectedItemsFirst", BookCodeSelectedItems.First());
 OleDbCommand_Delete.Parameters.AddWithValue("@BookCodeSelectedItemsLast", BookCodeSelectedItems.Last());

Thanks


dotnet-csharpsql-server-transact-sqlwindows-wpfdotnet-wpf-xamldotnet-adonet
selected-rows.png (31.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
3 Votes"
Viorel-1 answered RezaJaferi commented

Check an approach:

 for( int i = 0; i < BookCodeSelectedItems.Length; i += 20 )
 {
     string codes = string.Join( ",", BookCodeSelectedItems.Skip( i ).Take( 20 ) );
     OleDbCommand OleDbCommand_Delete = new OleDbCommand( "Delete From BookTable Where BookCode In (" + codes + ")", OleDbConnect );
     // execute the command 
     // . . .
 }

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much
It seems to work correctly and quickly, but please explain i + = 20 and BookCodeSelectedItems.Skip(i).Take (20)?

0 Votes 0 ·

Skip(i) skips the codes that were already examined; Take(20) takes the next 20 codes; i += 20 increments i by 20, just examined. Therefore, it considers chunks of 20 codes, and the last incomplete one. (To increase the performance, you can try longer chunks that are accepted by Access).

1 Vote 1 ·

Is it possible to increase performance again?

0 Votes 0 ·
Show more comments

I rated your answer again.

0 Votes 0 ·

Following a thorough investigation, I arrived at the following conclusions:
This analysis was carried out using .Net Framework 4.5.2.
The largest array that I can use in an In query in MS-Access 2007 without getting the Query is too complex error is 0 to 20,575.
In contrast to my belief, if the number of query arrays is too large in comparison to the For loop in C#, the program's execution speed will suffer.
However, i += 20575 and Take(20575) take about 10 seconds to complete, whereas i += 210 and Take(210) take about 4 seconds, whereas I had anticipated the app to operate much more quickly.
In my opinion, the loop should not be too long, and neither should the Query array. I believe the best approach is to balance C# and the Query array.

0 Votes 0 ·