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:
<Window.Resources>
<local:DatabaseDataSet x:Key="Database_DataSet"/>
<CollectionViewSource x:Key="BookTableViewSource" Source="{Binding BookTable, Source={StaticResource Database_DataSet}}"/>
<CollectionViewSource x:Key="MemberTableViewSource" Source="{Binding MemberTable, Source={StaticResource Database_DataSet}}"/>
</Window.Resources>
<Grid DataContext="{StaticResource BookTableViewSource}" Width="486" Height="386">
<!--When "EnableRowVirtualization" is set to "True", the DataGrid only imports the rows that the user is scrolling over from the database (for example, instead of loading 8000 rows, only are loaded 10 number of them at a time, so, less RAM is used)-->
<!--To avoid a "null" error when using a "For" loop between "DataGrid" rows, set "VirtualizingPanel.ScrollUnit" to "Pixel"-->
<DataGrid x:Name="BookDataGrid" HeadersVisibility="Column" EnableRowVirtualization="True" VirtualizingPanel.ScrollUnit="Pixel" CanUserAddRows="False" AutoGenerateColumns="False" ItemsSource="{Binding}" HorizontalAlignment="Left" VerticalAlignment="Top" Width="486" Height="386" Margin="0">
<DataGrid.Columns>
<DataGridTextColumn x:Name="BookName" Binding="{Binding BookName}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Publisher" Binding="{Binding Publisher}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Category" Binding="{Binding Category}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="BookCode" Binding="{Binding BookCode}" IsReadOnly="True" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Inventory" Binding="{Binding Inventory}" IsReadOnly="True" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="ReleaseDate" Binding="{Binding ReleaseDate}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="DateTaken" Binding="{Binding DateTaken}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="ReturnDate" Binding="{Binding ReturnDate}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="RecipientName" Binding="{Binding RecipientName}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Language" Binding="{Binding BookLanguage}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Length" Binding="{Binding Length}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Form" Binding="{Binding Form}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Translator" Binding="{Binding Translator}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Narrator" Binding="{Binding Narrator}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="ISBN" Binding="{Binding ISBN}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Location" Binding="{Binding Location}" Width="SizeToHeader"/>
<DataGridTextColumn x:Name="Price" Binding="{Binding Price}" Width="SizeToHeader"/>
<DataGridTemplateColumn x:Name="BookImage" Width="SizeToHeader">
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<Image x:Name="BookImg" Source="{Binding BookImage}"/>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
</DataGrid.Columns>
</DataGrid>
</Grid>
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
.