Introduction
SQL Enterprise Manager did it since years: Whenever a user changes a row in a table, it is automatically written back to the database table. Providing our users with the same functionality is tricky to implement because of the interaction of DataSet
and BindingSource
, which is hardly documented in the .NET help. This article investigates some intuitive solutions and explains why they will not work. A detailed analysis of the involved events leads to the final solution, which is surprisingly simple as any good solution should be.
Background
Often, a user has to save his work explicitly, like saving a document in Word. This approach works right out of the box with DataRowViews
, using the save button of the BindingNavigator
. But saving explicitly can be cumbersome for the user if changes in a DataRow
should be updated immediately to the database. Implementing auto saving should be easy! Just use an event which detects that the row content has changed, use the Update
method of the TableAdapter
and you are done. Unfortunately, ADO.NET will run into some strange internal errors should you try it.
Let's have a closer look at some intuitive solutions (or skip to The solution if you are in a hurry).
DataGridView event
The DataGridView
would be the most obvious choice to detect that a row has changed in the DataGridView
. But the DataGridView
focuses mostly on a cell, displaying its content, the user interaction and writing back the changed data to DatSet.DataTable.DataRow
. Events like DataGridView_RowValidated
fire for all possible reasons, and not necessarily because the user has changed the data.
There would be the DataGridView_CellEndEdit
event indicating a change. But using TableAdapter.Update()
at this point of time will mess up ADO.NET. Updating the database would happen in the middle of copying from the DataView
to the DataTable
. Both activities change the state of the DataRow
. Interrupting the copy with the update will prevent the copy operation from finishing properly (I guess ADO.NET doesn't support reentrancy).
BindingSource event
The data binding for the DataGridView
is done in the BindingSource
, the right place to detect when the content of a cell has changed:
private void BindingSource_CurrentItemChanged(
object sender, EventArgs e)
{
DataRow ThisDataRow =
((DataRowView)((BindingSource)sender).Current).Row;
if (ThisDataRow.RowState==DataRowState.Modified) {
TableAdapter.Update(ThisDataRow);
}
}
If you try this code, it will work, alas for the first changed record only! You will get a strange error message during the update of the second row, basically the row seems to be empty. When you check with the debugger, the row has meaningful data before the update and only after the runtime error it seems to be empty. The update even writes the second record successfully into the database.
DataTable event
If the BidingSource
doesn't work, how about using an event from the DataSet.DataTable
? After all, any change to the DataRow
should be written to the database, regardless of who does it. The code could look like this:
void Table_RowChanged
(object sender, DataRowChangeEventArgs e)
{
if (e.Row.RowState == DataRowState.Modified)
{
TableAdapter.Update(e.Row);
}
}
This time, you will immediately get a run time error. ADO.NET has not yet finished changing the DataRow
when the Update
tries to change the state of the DataRow
again.
It seems that ADO.NET doesn't want to be interrupted by a row update to the database until is has completely copied the changes from the DatRowView
to the DataTable
. None of the row change related events can be used to save the row to the database. So the solution must be to use an event which fires after the row is copied and the event should not be related to the row changed! Well, then let's just use the PositionChanged
event of the BindingSource
. It fires for the next row the user navigates to. So the challenge is to remember which was the last row, check if it was modified and update the database if needed. Don't forget to do the same thing when the Form
closes, the PositionChanged
event will not fire when the Form
closes:
public partial class MainForm: Form {
public MainForm() {
InitializeComponent();
}
private void MainForm_Load(
object sender, EventArgs e)
{
this.regionTableAdapter.Fill(
this.northwindDataSet.Region);
this.regionDataGridView.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCells);
}
private DataRow LastDataRow = null;
private void UpdateRowToDatabase() {
if (LastDataRow!=null) {
if (LastDataRow.RowState==
DataRowState.Modified) {
regionTableAdapter.Update(LastDataRow);
}
}
}
private void regionBindingSource_PositionChanged(
object sender, EventArgs e)
{
BindingSource thisBindingSource =
(BindingSource)sender;
DataRow ThisDataRow=
((DataRowView)thisBindingSource.Current).Row;
if (ThisDataRow==LastDataRow) {
throw new ApplicationException("It seems the" +
" PositionChanged event was fired twice for" +
" the same row");
}
UpdateRowToDatabase();
LastDataRow = ThisDataRow;
}
private void MainForm_FormClosed(
object sender, FormClosedEventArgs e)
{
UpdateRowToDatabase();
}
}
Event analysis
As a bonus, find a trace of the events involved when the user changes the content of a cell in the DataGridView
:
DataGridView_CellBeginEdit
CellEditMode: False
DataGridView_CellValidating
CellEditMode: True
DataTable_ColumnChanging
RowState: Unchanged; HasVersion 'DCOP'
DataTable_ColumnChanged
RowState: Unchanged; HasVersion 'DCOP'
DataGridView_CellValidated
CellEditMode: True
DataGridView_CellEndEdit
CellEditMode: False
DataGridView_RowValidating
CellEditMode: False
DataTable_RowChanging
RowState: Unchanged; HasVersion 'DCOP'
BindingSource_CurrentItemChanged
RowState: Modified ; HasVersion 'DCO '
BindingSource_ListChanged
RowState: Modified ; HasVersion 'DCO '
DataTable_RowChanged
RowState: Modified ; HasVersion 'DCO '
DataGridView_RowValidated
CellEditMode: False
DataGridView_Validating
CellEditMode: False
DataGridView_Validated
CellEditMode: False
DataRow Versions:
D: Default
C: Current
O: Old
P: Proposed
Using the code
Before you can run the sample application, open the Solution Explorer to change the NorthwindConnectionString
. The DataSource
should point to your SQL server with the Northwind database.
Once the application is running, change the name of a region and move to another row. This will save the region name to the database. Check in the database or close and restart the application to see if the change is really stored. Don't forget to change the region name back to its original value.
Conclusion
The same problem existed in earlier ADO.NET versions. I didn't try it, but the described approach should also work for earlier versions, just use the events of the CurrencyManager
.
History
- 27.1.2006: Original posting.