What is the quickest and most effective way to edit a "List<>" that has already been filled with data from a "DataGrid.ItemsSource" or synchronize "DataGrid" changes with the "Access" database?

Reza Jaferi 331

Hi,
I have a DataGrid that is related to the book specifications. Inventory is one of these specifications. To avoid user error, I automatically calculate the Inventory of that book based on its ISBN and save these changes in the Access database after removing each row.
The value of the IsReadOnly property of the Inventory column in the DataGrid is set to True.
So, if I'm not mistaken, after deleting each row, we have two choices: reload the database data in the BookDataGrid.ItemsSource or change the changes made, which are saved in a List<>, and then transfer them into the BookDataGrid.ItemsSource.
The following codes are related to removing the row from BookDataGrid and then saving it to the database:

    OleDbConnection OleDbConnect = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Windows.Forms.Application.StartupPath + @"\Database.accdb");  
    DataView BookDataView = new DataView();  
    public MainWindow()  
    {  
        InitializeComponent();  
        BookDataView = BookDataGrid.ItemsSource as DataView;  
    }  
    private void DataGridDeleteMenu_PreviewMouseLeftButtonDown(object sender, MouseButtonEventArgs e)  
    {  
        List<object> Rows = new List<object>();  
        for (int i = 0; i < BookDataGrid.Items.Count; i++)  
        {  
            Rows.Add(BookDataGrid.Items[i]);  
        }  
        uint[] BookCodeSelectedItems = new uint[BookDataGrid.SelectedItems.Count];  
        string[] ISBN_Value = new string[BookDataGrid.SelectedItems.Count];  
        int j = 0;  
        foreach (DataRowView DRV in BookDataGrid.SelectedItems)  
        {  
            BookCodeSelectedItems[j] = uint.Parse(BookDataView.Table.Rows[BookDataView.Table.Rows.IndexOf(DRV.Row)][3].ToString());  
            ISBN_Value[j] = BookDataView.Table.Rows[BookDataView.Table.Rows.IndexOf(DRV.Row)][14].ToString();  
            j++;  
        }  
        for (int i = 0; i < BookDataGrid.SelectedItems.Count; i++)  
        {  
            Rows.Remove(BookDataGrid.SelectedItems[i]);  
        }  
        OleDbCommand OleDbCommand_Delete = new OleDbCommand();  
        OleDbCommand_Delete.Connection = OleDbConnect;  
        OleDbConnect.Open();  
        for (int i = 0; i < BookCodeSelectedItems.Length; i += 210)  
        {  
            OleDbCommand_Delete.CommandText = string.Join(null, "Delete From BookTable Where BookCode In (", string.Join(",", BookCodeSelectedItems.Skip(i).Take(210)), ")");  
            OleDbCommand_Delete.ExecuteNonQuery();  
        }  
        OleDbConnect.Close();  
        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();  
        BookDataGrid.ItemsSource = Rows;  
    }

I don't mean convenience when I say quickest and most efficient, but rather quick execution and low RAM usage.
I can reload the database in BookDataGrid.ItemsSource using the following method, but I think changing the List<object> (I'm referring to Rows, which is defined in line 10) is faster and uses less RAM than restoring the database, but I don't know exactly how to do it.
If the database is large, the following method will take a long time and will consume a lot of RAM:

    public void BookDatagridRefresh()  
    {  
        DatabaseDataSet Database_DataSet = ((DatabaseDataSet)TryFindResource("Database_DataSet"));  
        DatabaseDataSetTableAdapters.BookTableTableAdapter BookTable_TableAdapter = new DatabaseDataSetTableAdapters.BookTableTableAdapter();  
        BookTable_TableAdapter.Fill(Database_DataSet.BookTable);  
        BookDataGrid.ItemsSource = Database_DataSet.Tables["BookTable"].DefaultView;  
    }

I think that something like this would be quicker than reloading:

To view the link, click here

However, the following link may also assist in answering this question:

To view the link, click here

I use the following tools:
.NET Framework 4.5.2, WPF
Thanks

Hui Liu-MSFT 38,251 Reputation points Microsoft Vendor

2022-07-25T10:02:57.867+00:00
Hi,@Reza Jaferi . I read the two links above. LINQ is similar to SQL queries. You could try to refer to the following two methods according to your needs.

Select the filter criteria (search box/combo box), re-query the original data source and filter out the results as needed.
2, Keep the entire data source in memory and create a new list from it every time you filter (basically two lists -> 1=original source; 2=filtered/copied => bind to datagrid).
Reza Jaferi 331 Reputation points

2022-07-25T14:23:47.253+00:00

According to my codes, can you provide a code?
I don't know exactly how to do it.
Reza Jaferi 331 Reputation points

2022-07-25T14:35:36.193+00:00

Can anyone tell me exactly what to do?
AgaveJoe 26,191 Reputation points

2022-07-25T14:48:51.94+00:00

Your question makes assumptions. There's nothing to convert. The DataGrid.ItemsSource can be assigned to a List<T>. I think you need to refactor the design and either use Generics (List<T>) or DataTables and DataViews.

I prefer strong types and recommend going with List<T>. Use LINQ for sorting. Even the reference documentation uses List<T>.

DataGrid.ItemsSource Property
DataGrid Class
Reza Jaferi 331 Reputation points

2022-07-25T15:28:48.587+00:00

I updated my question.
Reza Jaferi 331 Reputation points

2022-07-25T15:34:18.793+00:00

Tell me exactly what I should do now, preferably by providing the output.
Thanks.

Reza Jaferi 331

The following method works correctly, but before the For loop begins, I only need to quickly add the Rows data to the BookDataView.

        for (int i = 0; i < UniqueISBN.Length; i++)  
        {  
            int Inventory = BookDataView.Table.AsEnumerable().Count(Row => Row.Field<string>("ISBN") == UniqueISBN[i]);  
            EnumerableRowCollection<DataRow> Query = from Row in BookDataView.Table.AsEnumerable() where Row.Field<string>("ISBN") == UniqueISBN[i] select Row;  
            foreach (DataRow Row in Query)  
            {  
                Row["Inventory"] = Inventory;  
            }  
        }

I don't mean the below method, because as far as I know, DataTable.Rows.Add() is slow for large amounts of data.

To view the link, click here

AgaveJoe 26,191 Reputation points

2022-07-26T13:10:25.82+00:00
In my opinion, it's simply not a good design. Write a SQL to query and update tables not C#. The following is a basic example where the Books table contains the all the Books in the inventory.

CREATE TABLE dbo.Books ( ISBN INT ) INSERT INTO dbo.Books (ISBN) VALUES(1),(1),(1),(1),(1),(2),(3),(3),(3),(3);

The inventory query by ISBN is simply.

SELECT ISBN, COUNT(ISBN) AS [Count] FROM dbo.Books GROUP BY ISBN

And the results are

ISBN Count ----------- ----------- 1 5 2 1 3 4

I'm not sure why you are using DataTable.Rows.Add() when you can simply assign a result set to the data source. It seems like you've designed a copy of the data in memory which is causing most of the performance issues. I would remove the in-memory database design and simply query the tables directly.

MS Access allows you to create queries. You can use this feature to test (and copy) the SQL.
Reza Jaferi 331 Reputation points

2022-07-26T13:38:36.24+00:00

Thank you very much for your advice.
This technique increases the size of the database since it creates a new table, unless we clear it every time, which is time-consuming.
AgaveJoe 26,191 Reputation points

2022-07-26T13:58:00.78+00:00

This technique increases the size of the database since it creates a new table, unless we clear it every time, which is time-consuming.

You misunderstand the concept. The technique does not add a new table. It simply queries an existing table to return the count of books by ISBN using standard SQL. I assume you have a table in MS Access that contains all the books by ISBN?
Reza Jaferi 331 Reputation points

2022-07-26T14:28:33.877+00:00

If I'm not mistaken, you mean I get all the ISBN from the database with a Query, transfer them to an array, and then use them, right?
AgaveJoe 26,191 Reputation points

2022-07-26T17:54:01.063+00:00

If I'm not mistaken, you mean I get all the ISBN from the database with a Query, transfer them to an array, and then use them, right?

I recommend writing a SQL GROUP BY query to fetch an aggregated result set.

Another option is LINQ but I think having two copies of the data in two different places is burning clock cycles and memory.

Reza Jaferi 331

I found the solution below, but when I delete multiple rows, it returns this error:

Managed Debugging Assistant 'RaceOnRCWCleanup' : 'An attempt has been made to free an RCW that is in use. The RCW is in use on the active thread or another thread. Attempting to free an in-use RCW can cause corruption or data loss.'

        int[] Inventory = new int[UniqueISBN.Length];  
        OleDbConnect.Open();  
        Parallel.For(0, UniqueISBN.Length, Index =>  
        {  
            OleDbCommand_Update.CommandText = string.Join(null, "Select Count(*) From BookTable Where ISBN = '", UniqueISBN[Index], "'");  
            Inventory[Index] = (int)OleDbCommand_Update.ExecuteScalar();  
            OleDbCommand_Update.CommandText = string.Join(null, "Update BookTable Set Inventory = ", Inventory[Index], " Where ISBN = '", UniqueISBN[Index], "'");  
            OleDbCommand_Update.ExecuteNonQuery();  
        }  
        );  
        Parallel.For(0, UniqueISBN.Length, Index =>  
        {  
            EnumerableRowCollection<DataRow> Query = from Row in BookDataView.Table.AsEnumerable() where Row.Field<string>("ISBN") == UniqueISBN[Index] select Row;  
            Parallel.ForEach(Query, Row =>  
            {  
                Row["Inventory"] = Inventory[Index];  
            }  
            );  
        }  
        );  
        OleDbConnect.Close();

1 answer

AgaveJoe 26,191 Reputation points

2022-07-27T17:34:50.623+00:00
I simply do not understand why you insist on making a very simply query waste as many system resources as possible.

This is literally one SQL script.

SELECT DISTINCTROW BookTable.ISBN, Count(*) AS Inventory FROM BookTable GROUP BY BookTable.ISBN;

In my opinion, there's little logical reason to update the BooKTable because the query fetches the inventory when executed. If you need more columns then do a join to the BookTable by ISBN.

You can use the query above to update the BookTable. However, updating the BookTable with aggregate Inventory values can be problematic if code exists that queries the BookTable directly to get the inventory. This is because the inventory in the BookTable could become stale as time passes. You should always execute the query first to update the table.
Please sign in to rate this answer.
Reza Jaferi 331 Reputation points

2022-07-28T07:42:01.537+00:00

I simply do not understand why you insist on making a very simply query waste as many system resources as possible.

I don't know how to append the code you wrote above to the other columns and then connect to the BookDataGrid.ItemsSource without using OleDbDataAdapter or OleDbDataReader or DataTable.Load (this one is too slow) instead of the code below.

Reza Jaferi 331 Reputation points

2022-07-28T07:50:42.187+00:00

I mean this code:

for (int i = 0; i < UniqueISBN.Length; i++) { int Inventory = BookDataView.Table.AsEnumerable().Count(Row => Row.Field<string>("ISBN") == UniqueISBN[i]); EnumerableRowCollection<DataRow> Query = from Row in BookDataView.Table.AsEnumerable() where Row.Field<string>("ISBN") == UniqueISBN[i] select Row; foreach (DataRow Row in Query) { Row["Inventory"] = Inventory; } } BookDataGrid.ItemsSource = Rows;

Reza Jaferi 331 Reputation points

2022-07-28T09:27:53.24+00:00

Thank you very much for your feedback, Miss Joe.

Reza Jaferi 331 Reputation points

2022-07-28T17:19:04.633+00:00

You clearly didn't understand my question. I'm looking for the updated rows, but your code only returns Inventory and ISBN.

AgaveJoe 26,191 Reputation points

2022-07-28T17:37:30.443+00:00

You clearly didn't understand my question. I'm looking for the updated rows, but your code only returns Inventory and ISBN.

I'm a bit confused by your slightly rude response. Your code updates only the "Inventory" field. My code does exactly the same thing.

I would have created an example with the all the fields in the BookTable but you did not share this information. The only fields in the thread are the ISBN and Inventory. Your SQL SELECT all use the *.

The problem I see with your code is it submits many SQL batches when one will work.

Are you trying to sync an in-memory cache with MS Access? For example, delete several records then from the grid's data source then update the MS Access database?

Reza Jaferi 331 Reputation points

2022-07-28T18:32:25.387+00:00

I'm a bit confused by your slightly rude response.

Please accept my heartfelt apologies. My first language is not English. I'm not sure how to put the words together.
(◡‿◡✿)❤❤❤❤(◡‿◡✿)

Reza Jaferi 331 Reputation points

2022-07-28T18:58:33.12+00:00

My entire argument is that rather than reloading all of the data from the database and putting it into the DataTable, we should just count the rows based on the ISBN, update them (I mean, updating a collection, not an Access database) using a Parallel loop, and then bind them to BookDataGrid.ItemsSource. because loading through the DataTable needs a large amount of RAM.
Sign in to comment