I have a book table with the columns ISBN and Inventory. After removing a row or thousands of rows from this table to avoid user error, I automatically calculate the Inventory of that book based on its ISBN.
I use the following code to do this, but it takes a long time when the user deletes hundreds of rows and if there are thousands of unique ISBNs.
string[] UniqueISBN = ISBN_Value.Distinct().ToArray();
OleDbCommand OleDbCommand_Update = new OleDbCommand();
OleDbCommand_Update.Connection = OleDbConnect;
OleDbConnect.Open();
for (int i = 0; i < UniqueISBN.Length; i++)
{
OleDbCommand_Update.CommandText = string.Join(null, "Select Count(*) From BookTable Where ISBN = '", UniqueISBN[i], "'");
OleDbCommand_Update.CommandText = string.Join(null, "Update BookTable Set Inventory = ", (int)OleDbCommand_Update.ExecuteScalar(), " Where ISBN = '", UniqueISBN[i], "'");
OleDbCommand_Update.ExecuteNonQuery();
}
OleDbConnect.Close();
To answer this question, the following information may be required:
The array's length can range from 1 to 250 thousand, and each ISBN[i] has 13 digits and is stored in the database as a string type field.
In the following GIF image , the inventory of ISBN "0000000000000" is 3 and the inventory of ISBN "2222222222222" is 4.
Click to view
As you can see, when the two ISBNs "00000000000000" are removed, the inventory of the ISBN "00000000000000" changes automatically.
Unfortunately, when the number of unique ISBNs increases (for example, to 4,000 unique ISBNs), this solution becomes very slow.
I believe there is a quicker alternative, similar to the following code.
OleDbCommand_Update.CommandText = "Update BookTable Set Inventory = (Select Distinct Count(*) From BookTable Group By ISBN) Where ISBN IN (Select Distinct ISBN From BookTable Group By ISBN)";
I use the following tools:
.NET Framework 4.5.2
,
WPF
,
MS-Access 2007
If someone answers correctly, I will give them 5 stars.
Thanks.
What I have tried:
OleDbCommand_Update.CommandText = "Update BookTable Set Inventory = (Select Distinct Count(*) From BookTable Group By ISBN) Where ISBN IN (Select Distinct ISBN From BookTable Group By ISBN)";