question

RezaJaferi avatar image
2 Votes"
RezaJaferi asked RezaJaferi edited

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?

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



dotnet-csharpwindows-wpfdotnet-wpf-xamldotnet-adonet
· 13
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.

Hi,@RezaJaferi. 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.
1. 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).

0 Votes 0 ·

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

0 Votes 0 ·

Can anyone tell me exactly what to do?

0 Votes 0 ·

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


0 Votes 0 ·

I updated my question.

0 Votes 0 ·

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

0 Votes 0 ·

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


0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·
Show more comments

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();
0 Votes 0 ·

1 Answer

AgaveJoe avatar image
0 Votes"
AgaveJoe answered RezaJaferi edited

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.


· 7
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.

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.
0 Votes 0 ·

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;
0 Votes 0 ·

Thank you very much for your feedback, Miss Joe.

0 Votes 0 ·

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

0 Votes 0 ·

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?

0 Votes 0 ·

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.
(◡‿◡✿)❤❤❤❤(◡‿◡✿)
0 Votes 0 ·

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.

0 Votes 0 ·