Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:
C#
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 11) 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:
C#
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;
}


What I have tried:

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
Posted
Updated 25-Jul-22 8:48am
v3
Comments
Afzaal Ahmad Zeeshan 25-Jul-22 20:42pm    
Not a direct answer: but be aware that your code is prone to SQL Injection. For offline and client-only database, it is not very sensitive, but still does not provide a good experience to the users if their apps stop working (just because someone messed with the database schema).
[no name] 25-Jul-22 20:46pm    
You delete the record in the database; if it succeeds, you update the grid; otherwise, you have to revert the grid if the database call fails.
Reza jafery 26-Jul-22 4:13am    
Thanks for your tips.
The problem would most likely be solved if there were LINQ equivalents for the following code:
            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();
            }

Something like this:
            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;
                }
                BookDataView = Query.AsDataView();
            }

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900