Auto Saving DataGridView Rows to a SQL Server Database

• Download demo project - 20.6 Kb

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.

The solution

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);
    // resize the column once, but allow the
    // users to change it.
    this.regionDataGridView.AutoResizeColumns(
      DataGridViewAutoSizeColumnsMode.AllCells);
  }
 
  //tracks for PositionChanged event last row
  private DataRow LastDataRow = null;

  /// <SUMMARY>
  /// Checks if there is a row with changes and
  /// writes it to the database
  /// </SUMMARY>
  private void UpdateRowToDatabase() {
    if (LastDataRow!=null) {
      if (LastDataRow.RowState==
          DataRowState.Modified) {
        regionTableAdapter.Update(LastDataRow);
      }
    }
  }
  
  private void regionBindingSource_PositionChanged(
    object sender, EventArgs e) 
  {
    // if the user moves to a new row, check if the 
    // last row was changed
    BindingSource thisBindingSource = 
      (BindingSource)sender;
    DataRow ThisDataRow=
      ((DataRowView)thisBindingSource.Current).Row;
    if (ThisDataRow==LastDataRow) {
      // we need to avoid to write a datarow to the 
      // database when it is still processed. Otherwise
      // we get a problem with the event handling of 
      //the DataTable.
      throw new ApplicationException("It seems the" +
        " PositionChanged event was fired twice for" + 
        " the same row");
    }

    UpdateRowToDatabase();
    // track the current row for next 
    // PositionChanged event
    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.