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 Reputation points
2022-07-24T21:43:43.483+00:00

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

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,184 questions
Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,635 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,975 questions
XAML
XAML
A language based on Extensible Markup Language (XML) that enables developers to specify a hierarchy of objects with a set of properties and logic.
744 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AgaveJoe 25,761 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.