My method takes a long time to save a lot of information from DataGrid to MS Access

Question

Hi
I will give two credits to anyone who answers this question by submitting the output (I have two accounts).
I tried to save my DataGrid records (when DataGrid rows are edited) to an MS Access table, so after about 3 months I found a solution by myself, my solution works very well if there are not many rows but If there are many rows, my method takes a lot of time.
For example, if we have 50,000 rows in the DataGrid and want to edit 40 of them, we must count the rows 50,000 times and insert them into the DataTable, then count the rows of the DataTable (which is 50,000) and insert them into the database table.
XAML:

C#:

    public OleDbConnection OleDbConnect = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Windows.Forms.Application.StartupPath + @"\Database.accdb");  
    public DataTable DataGridToDataTable(DataGrid DG, DataTable DT, byte NumberOfColumns, byte VisualColumnIndex, string ControlName)  
    {  
        for (int i = 0; i < DG.Items.Count; i++)  
        {  
            DT.Rows.Add(DG.Items[i]);  
        }  
        for (int i = 0; i < DG.Items.Count; i++)  
        {  
            for (byte j = 0; j < NumberOfColumns; j++)  
            {  
                switch (j == VisualColumnIndex)  
                {  
                    case true:  
                        FrameworkElement FE = DG.Columns[j].GetCellContent((DataGridRow)DG.ItemContainerGenerator.ContainerFromIndex(i));  
                        Image Img = new Image() { Source = ((((DataGridTemplateColumn)DG.Columns[j]).CellTemplate.FindName(ControlName, FE) as Image).Source) };  
                        DT.Rows[i][j] = ImageSourceToBytes(new PngBitmapEncoder(), Img.Source);  
                        break;  
                    default:  
                        DG.ScrollIntoView((DataRowView)DG.Items[i]);  
                        DT.Rows[i][j] = ((DG.Columns[j].GetCellContent(((DataGridRow)DG.ItemContainerGenerator.ContainerFromIndex(i)))) as TextBlock).Text;  
                        break;  
                }  
            }  
        }  
        return DT;  
    }  
    private void Sync_Button_PreviewMouseLeftButtonDown(object sender, MouseButtonEventArgs e)  
    {  
        OleDbCommand OleDbCommand_Delete = new OleDbCommand();  
        OleDbCommand OleDbCommand_Insert = new OleDbCommand();  
        DataTable BDT = new DataTable();  
        BDT.Columns.Clear();  
        BDT.Columns.Add("BookName", typeof(string));  
        BDT.Columns.Add("Publisher", typeof(string));  
        BDT.Columns.Add("Category", typeof(string));  
        BDT.Columns.Add("BookCode", typeof(ulong));  
        BDT.Columns.Add("Inventory", typeof(int));  
        BDT.Columns.Add("ReleaseDate", typeof(string));  
        BDT.Columns.Add("DateTaken", typeof(string));  
        BDT.Columns.Add("ReturnDate", typeof(string));  
        BDT.Columns.Add("RecipientName", typeof(string));  
        BDT.Columns.Add("BookLanguage", typeof(string));  
        BDT.Columns.Add("Length", typeof(short));  
        BDT.Columns.Add("Form", typeof(string));  
        BDT.Columns.Add("Translator", typeof(string));  
        BDT.Columns.Add("Narrator", typeof(string));  
        BDT.Columns.Add("ISBN", typeof(string));  
        BDT.Columns.Add("Location", typeof(string));  
        BDT.Columns.Add("Price", typeof(string));  
        BDT.Columns.Add("BookImage", typeof(byte[]));  
        BDT = DataGridToDataTable(BookDataGrid, BDT, 18, 17, "BookImg");  
        OleDbConnect.Open();  
        OleDbCommand_Delete.Connection = OleDbConnect;  
        OleDbCommand_Delete.CommandText = "Delete * From [BookTable]";  
        OleDbCommand_Delete.ExecuteNonQuery();  
        OleDbCommand_Insert = null;  
        for (int i = 0; i < BookDataGrid.Items.Count; i++)  
        {  
            OleDbParameter Parameter = new OleDbParameter();  
            Parameter.OleDbType = OleDbType.Binary;  
            Parameter.ParameterName = "Image";  
            Parameter.Value = (byte[])(BDT.Rows[i][17]);  
            OleDbCommand_Insert = new OleDbCommand("Insert Into [BookTable](BookName,Publisher,Category,BookCode,Inventory,ReleaseDate,DateTaken,ReturnDate,RecipientName,BookLanguage,Length,Form,Translator,Narrator,ISBN,Location,Price,BookImage)values('" + BDT.Rows[i][0] + "','" + BDT.Rows[i][1] + "','" + BDT.Rows[i][2] + "','" + ulong.Parse(BDT.Rows[i][3].ToString()) + "','" + int.Parse(BDT.Rows[i][4].ToString()) + "','" + BDT.Rows[i][5] + "','" + BDT.Rows[i][6] + "','" + BDT.Rows[i][7] + "','" + BDT.Rows[i][8] + "','" + BDT.Rows[i][9] + "','" + short.Parse(BDT.Rows[i][10].ToString()) + "','" + BDT.Rows[i][11] + "','" + BDT.Rows[i][12] + "','" + BDT.Rows[i][13] + "','" + BDT.Rows[i][14] + "','" + BDT.Rows[i][15] + "','" + BDT.Rows[i][16] + "',@Image)", OleDbConnect);  
            OleDbCommand_Insert.Parameters.Add(Parameter);  
            OleDbCommand_Insert.ExecuteScalar();  
        }  
        OleDbConnect.Close();  
    }

I use the following tools:
Visual Studio 2017
.NET Framework 4.5.2
MS Access 2007

In addition, I use the following method to load database table information into DataGrid.
In this method, information is loaded quickly.
This method quickly loads data from the database to the DataGrid, so I need a similar method (like this) to insert data from the DataGrid to the database:

    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;  
    }

The following link may be of assistance to you:
Click here to view
I will give two credits to anyone who answers this question by submitting the output (I have two accounts).
Thanks

Accepted Answer

Hi
I developed a new method based on the methods and recommendations of Castorix3, AgaveJoe, karenpayneoregon, and my own ideas.
The following method seems to be a very good method:

Are far simpler design is updating a record when the user leaves the row. Prompt the user, "the records has changed. Do you want to commit these changes?

1- We need two rows to hold the rows in order to compare them before and after modification (don't use DataRowView to store the row values).
2- Get the row values and store them in the first row of the DataTable before editing the DataGrid cell in the DataGrid BeginningEdit event.
3- When we finish editing a row, we retrieve its values in the DataGridRowEditEnding event and insert them into the next row of the DataTable.
4- Now we compare the two rows using the function HasTheRowBeenEdited (which I wrote), and the smallest change (the space before and after the word is not considered a change) returns True.
5- Then we ask the user if they want to save the modifications.
6- If the user agrees to the change, we save the values from the DataTable's second row in the database.

So:
XAML:

C#:

    DataTable BDT = new DataTable();  
    public MainWindow()  
    {  
        InitializeComponent();  
        BDT.Columns.Clear();  
        BDT.Columns.Add("BookName", typeof(string));  
        BDT.Columns.Add("Publisher", typeof(string));  
        BDT.Columns.Add("Category", typeof(string));  
        BDT.Columns.Add("BookCode", typeof(ulong));  
        BDT.Columns.Add("Inventory", typeof(int));  
        BDT.Columns.Add("ReleaseDate", typeof(string));  
        BDT.Columns.Add("DateTaken", typeof(string));  
        BDT.Columns.Add("ReturnDate", typeof(string));  
        BDT.Columns.Add("RecipientName", typeof(string));  
        BDT.Columns.Add("BookLanguage", typeof(string));  
        BDT.Columns.Add("Length", typeof(short));  
        BDT.Columns.Add("Form", typeof(string));  
        BDT.Columns.Add("Translator", typeof(string));  
        BDT.Columns.Add("Narrator", typeof(string));  
        BDT.Columns.Add("ISBN", typeof(string));  
        BDT.Columns.Add("Location", typeof(string));  
        BDT.Columns.Add("Price", typeof(string));  
        //We only need two rows.  
        BDT.Rows.Add();  
        BDT.Rows.Add();  
    }  
    public void FillDataTableFromDataGrid(DataGrid DG, DataTable DT, byte NumberOfColumns, byte Count)  
    {  
        if (Count == 0)  
        {  
            for (byte j = 0; j < NumberOfColumns; j++)  
            {  
                //When we set "EnableRowVirtualization" to "True", we may encounter a "null" error when using the "For" loop; in this case, the "ScrollIntoView" function should be used  
                DG.ScrollIntoView((DataRowView)DG.SelectedItem);  
                DT.Rows[0][j] = ((DG.Columns[j].GetCellContent(((DataGridRow)DG.ItemContainerGenerator.ContainerFromItem(DG.SelectedItem)))) as TextBlock).Text.Trim();  
            }  
        }  
        else if (Count == 1)  
        {  
            for (byte j = 0; j < NumberOfColumns; j++)  
            {  
                //When we set "EnableRowVirtualization" to "True", we may encounter a "null" error when using the "For" loop; in this case, the "ScrollIntoView" function should be used  
                DG.ScrollIntoView((DataRowView)DG.SelectedItem);  
                DT.Rows[1][j] = ((DG.Columns[j].GetCellContent(((DataGridRow)DG.ItemContainerGenerator.ContainerFromItem(DG.SelectedItem)))) as TextBlock).Text.Trim();  
            }  
        }  
    }  
    private bool HasTheRowBeenEdited(DataGrid DG, DataTable DT, byte NumberOfColumns)  
    {  
        bool Changes = false;  
        if (DG.CurrentItem != null)  
        {  
            DG.ScrollIntoView((DataRowView)DG.CurrentItem);  
            for (byte i = 0; i < NumberOfColumns; i++)  
            {  
                string PrimaryValue = DT.Rows[0][i].ToString();  
                string SecondaryValue = DT.Rows[1][i].ToString();  
                if (PrimaryValue != SecondaryValue)  
                {  
                    Changes = true;  
                    break;  
                }  
            }  
        }  
        return Changes;  
    }  
    private void BookDataGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)  
    {  
        FillDataTableFromDataGrid(BookDataGrid, BDT, 17, 0);  
    }  
    private void BookDataGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)  
    {  
        (sender as DataGrid).RowEditEnding -= BookDataGrid_RowEditEnding;  
        //To commit changes, use "CommitEdit()"; otherwise, the value will be received before the cell was edited  
        (sender as DataGrid).CommitEdit();  
        (sender as DataGrid).Items.Refresh();  
        FillDataTableFromDataGrid(BookDataGrid, BDT, 17, 1);  
        (sender as DataGrid).RowEditEnding += BookDataGrid_RowEditEnding;  
        if (HasTheRowBeenEdited(BookDataGrid, BDT, 17))  
        {  
            MessageWindow MessageWindowAsk = new MessageWindow();  
            MessageWindowAsk.OKButton.Visibility = Visibility.Hidden;  
            MessageWindowAsk.Image.Source = GetImageFromBytes(System.IO.File.ReadAllBytes(System.Windows.Forms.Application.StartupPath + @"\Images\Warning.bin"));  
            MessageWindowAsk.YesButton.Visibility = Visibility.Visible;  
            MessageWindowAsk.NoButton.Visibility = Visibility.Visible;  
            MessageWindowAsk.MessageTextBlock.Text = "Do you want to save the changes?";  
            MessageWindowAsk.ShowDialog();  
            if (App.ButtonReturn)  
            {  
                OleDbCommand OleDbCommand_Update = new OleDbCommand();  
                OleDbCommand_Update.Connection = OleDbConnect;  
                OleDbCommand_Update.CommandText = "Update [BookTable] Set BookName = @BookName , Publisher = @Publisher , Category = @Category ,ReleaseDate = @ReleaseDate , DateTaken = @DateTaken , ReturnDate = @ReturnDate , RecipientName = @RecipientName , BookLanguage = @BookLanguage , Length = @Length , Form = @Form , Translator = @Translator , Narrator = @Narrator , ISBN = @ISBN , Location = @Location , Price = @Price Where BookCode = @BookCode";  
                //The order of the parameter names and their values must be based on the order of the query, otherwise it will not work correctly.  
                //For example here, "@BookCode" should be last because the condition is last in the query.  
                OleDbCommand_Update.Parameters.AddWithValue("@BookName", BDT.Rows[1][0].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@Publisher", BDT.Rows[1][1].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@Category", BDT.Rows[1][2].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@ReleaseDate", BDT.Rows[1][5].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@DateTaken", BDT.Rows[1][6].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@ReturnDate", BDT.Rows[1][7].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@RecipientName", BDT.Rows[1][8].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@BookLanguage", BDT.Rows[1][9].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@Length", short.Parse(BDT.Rows[1][10].ToString()));  
                OleDbCommand_Update.Parameters.AddWithValue("@Form", BDT.Rows[1][11].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@Translator", BDT.Rows[1][12].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@Narrator", BDT.Rows[1][13].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@ISBN", BDT.Rows[1][14].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@Location", BDT.Rows[1][15].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@Price", BDT.Rows[1][16].ToString());  
                OleDbCommand_Update.Parameters.AddWithValue("@BookCode", ulong.Parse(BDT.Rows[1][3].ToString()));  
                OleDbConnect.Open();  
                OleDbCommand_Update.ExecuteNonQuery();  
                OleDbConnect.Close();  
                MessageWindow MW = new MessageWindow();  
                MW.YesButton.Visibility = Visibility.Hidden;  
                MW.NoButton.Visibility = Visibility.Hidden;  
                MW.Image.Source = GetImageFromBytes(System.IO.File.ReadAllBytes(System.Windows.Forms.Application.StartupPath + @"\Images\Check.bin"));  
                MW.MessageTextBlock.Text = "Information updated successfully";  
                MW.OKButton.Content = "OK";  
                MW.ShowDialog();  
            }  
        }  
    }

Output (when EnableRowVirtualization is set to True, even if there are thousands of rows, RAM consumption is reduced):

Tested in:
Visual Studio 2017 .NET Framework 4.5.2 WPF
To delete rows from the database, we can use the primary key to delete rows that have been deleted from the DataGrid.

Answer

The following uses code samples I had already.

Note, all code has been done with .NET Framework 4.8, will also work with .NET Framework but not earlier than 4.8.

You are missing the point with string concatenation. This is what I meant, use parameters.

public static (int identifier, Exception exception) SampleInsert(string companyName, string contactName)  
{  
  
    using (var cn = new OleDbConnection { ConnectionString = ConnectionString })  
    {  
        using (var cmd = new OleDbCommand() { Connection = cn })  
        {  
            cmd.CommandText =  
                "INSERT INTO Customers (CompanyName,ContactName) " +  
                "Values (@CompanyName,@ContactName)";  
  
            cmd.Parameters.Add("@CompanyName",  
                OleDbType.LongVarChar).Value = companyName;  
  
            cmd.Parameters.Add("@ContactName",  
                OleDbType.LongVarChar).Value = contactName;  
  
            try  
            {  
                cn.Open();  
  
                cmd.ExecuteNonQuery();  
                return ((int)cmd.ExecuteScalar(), null);  
            }  
            catch (Exception ex)  
            {  
                return (-1, ex);  
            }  
  
        }  
    }  
  
}

Then there is always using a OleDbDataAdapter in a class which your frontend uses rather than placing all the data operations in the frontend.

Answer

First off, how do your users even begin to work with 50,000 records? Then edit 4,000 records? This is highly irregular for any application.

Next up, there is never a reason to iterate a DataGrid rows and columns, do this to the data source if needed.

Next up, never use string concatenation for values in an insert, update, select or delete.

Have you considered using a OleDbDataAdapter? This would solve many of your issues or simply move to SQL-Server which has better SQL commands for performing bulk changes to a database.

Answer

The DataAdapter example

using System;  
using System.Data;  
using System.Data.OleDb;  
using System.Windows.Forms;  
  
namespace DataAdapterFormApp.Classes  
{  
    public class DataOperations  
    {  
        public static string ConnectionString => "TODO";  
  
        private static OleDbDataAdapter _sqlDataAdapter = new OleDbDataAdapter();  
        private static readonly DataSet _dataSet = new DataSet();  
        private static readonly OleDbConnection connection = new OleDbConnection(ConnectionString);  
        public static readonly BindingSource BindingSource = new BindingSource();  
  
        ///   
        /// Get data specified in the SELECT statement  
        ///   
        ///   
        public static (bool success, Exception exception) Load()  
        {  
            try  
            {  
                var selectStatement = "SELECT id, FirstName, LastName, HiredDate FROM dbo.employee;";  
                _sqlDataAdapter = new OleDbDataAdapter(selectStatement, connection);  
  
                _sqlDataAdapter.Fill(_dataSet);  
  
                // setup SQL commands e.g. insert, edit, delete  
                _ = new OleDbCommandBuilder(_sqlDataAdapter);  
  
                _sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;  
                BindingSource.DataSource = _dataSet.Tables[0];  
  
                return (true, null);  
            }  
            catch (Exception ex)  
            {  
                return (false, ex);  
            }  
        }  
  
        ///   
        /// Get current row in grid  
        ///   
        public static DataRow Current()   
            => ((DataRowView)BindingSource.Current).Row;  
  
        ///   
        /// Get DataTable so we need not cast in user code  
        ///   
        public static DataTable DataTable()   
            => (DataTable)BindingSource.DataSource;  
  
        ///   
        /// Check if modified records and provide a DataTable to examine  
        /// Caller should deconstruct for clearly working with return information  
        ///   
        public static (bool hasChanges, DataTable table) ModifiedRecords()  
        {  
            var table = DataTable().GetChanges(DataRowState.Modified);  
            return table == null ? (false, null) : (true, modified: table);  
        }  
        ///   
        /// Determine if there are modified records  
        ///   
        public static bool HasModified()   
            => DataTable().GetChanges(DataRowState.Modified) != null;  
  
        ///   
        /// Check if added records and provide a DataTable to examine  
        /// Caller should deconstruct for clearly working with return information  
        ///   
        public static (bool hasChanges, DataTable table) Added()  
        {  
            var table = DataTable().GetChanges(DataRowState.Added);  
            return table == null ? (false, null) : (true, modified: table);  
        }  
  
        ///   
        /// Determine if there are no records  
        ///   
        public static bool HasNewRecords() =>   
            DataTable().GetChanges(DataRowState.Added) != null;  
  
        ///   
        /// Determine if one or more records have been deleted  
        ///   
        public static bool HasDeletedRecords() =>   
            DataTable().GetChanges(DataRowState.Deleted) != null;  
  
        ///   
        /// Save changed, return count of changes and if there was an  
        /// exception provide access to it to the caller  
        ///   
        ///   
        public static (int affected, Exception exception) SaveChanges()  
        {  
            var count = -1;  
            try  
            {  
                count = _sqlDataAdapter.Update(_dataSet);  
            }  
            catch (Exception ex)  
            {  
                return (count, ex);  
            }  
  
            return (count, null);  
        }  
  
    }  
}

My method takes a long time to save a lot of information from DataGrid to MS Access

3 additional answers