Click here to Skip to main content
15,867,939 members
Articles / Desktop Programming / Windows Forms
Article

Auto Saving DataGridView Rows to a SQL Server Database

Rate me:
Please Sign up or sign in to vote.
4.84/5 (44 votes)
26 Jan 2006CPOL4 min read 562.3K   11.2K   164   62
Saving a changed row in the DataGridView automatically to the database seems to be a basic task, but is challenging to implement. Read here why the most intuitive approaches fail and how to get it working.

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:

C#
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:

C#
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:

C#
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.

License

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


Written By
Software Developer (Senior)
Singapore Singapore
Retired SW Developer from Switzerland living in Singapore

Interested in WPF projects.

Comments and Discussions

 
GeneralRe: but if I have a query Pin
JohnieBraaf22-Mar-07 11:33
JohnieBraaf22-Mar-07 11:33 
QuestionDatabase not getting Updated Pin
Jon Dwan20-Sep-06 1:35
Jon Dwan20-Sep-06 1:35 
AnswerRe: Database not getting Updated Pin
C++reativeSoft22-Nov-06 9:45
C++reativeSoft22-Nov-06 9:45 
GeneralI did in an other way Pin
inamgul20-Aug-06 21:34
inamgul20-Aug-06 21:34 
GeneralNew record add Pin
hominoid28-Jul-06 3:35
hominoid28-Jul-06 3:35 
GeneralThanks! And ... here's a class to encapsulate it. Pin
Jeff Roush25-Jul-06 9:39
Jeff Roush25-Jul-06 9:39 
GeneralRe: Thanks! And ... here's a class to encapsulate it. Pin
stuallenii28-Aug-06 9:11
stuallenii28-Aug-06 9:11 
GeneralIn addition to form's OnClose() event... Pin
DocX21-Jun-06 9:03
DocX21-Jun-06 9:03 
Good point about checking for a final update before the form containing the grid closes, but users may also need to ensure that the most recent grid change gets recorded when keeping the form open, but clicking to other controls on the form. In this case, the grid's "Leave" event can be used, since it fires when the grid loses focus (i.e. when it is no longer the active control on the form).
GeneralRe: In addition to form's OnClose() event... Pin
Jim Waverly14-Jul-06 8:25
Jim Waverly14-Jul-06 8:25 
GeneralDataTable events can be used, I suppose Pin
lponiew11-May-06 10:41
lponiew11-May-06 10:41 
GeneralRe: DataTable events can be used, I suppose Pin
fbachan22-Jun-06 1:04
fbachan22-Jun-06 1:04 
AnswerRe: DataTable events can be used, I suppose Pin
ddrinka28-Feb-07 20:34
ddrinka28-Feb-07 20:34 
GeneralDatagridView rotate Pin
horvat9-May-06 19:57
horvat9-May-06 19:57 
GeneralProblem while updating Pin
Manish Jaiswal10-Apr-06 13:49
Manish Jaiswal10-Apr-06 13:49 
GeneralRe: Problem while updating Pin
Cyrus Lesser22-Jul-06 0:11
Cyrus Lesser22-Jul-06 0:11 
GeneralRe: Problem while updating Pin
Cyrus Lesser25-Jul-06 1:57
Cyrus Lesser25-Jul-06 1:57 
GeneralBindingSource.BindingComplete Pin
goodman17-Feb-06 7:52
goodman17-Feb-06 7:52 
GeneralRe: BindingSource.BindingComplete Pin
Peter Huber SG8-Feb-06 2:58
mvaPeter Huber SG8-Feb-06 2:58 
QuestionWhat if error in UpdateRowToDatabase? Pin
jareksmcp5-Feb-06 23:11
jareksmcp5-Feb-06 23:11 
General... Pin
shalafiraistlin4-Feb-06 0:52
shalafiraistlin4-Feb-06 0:52 
Generalcast exception Pin
shalafiraistlin4-Feb-06 0:47
shalafiraistlin4-Feb-06 0:47 
QuestionRe: cast exception Pin
sjpjs17-Aug-06 0:36
sjpjs17-Aug-06 0:36 
QuestionRe: cast exception Pin
Jon Dwan14-Sep-06 1:19
Jon Dwan14-Sep-06 1:19 
AnswerRe: cast exception Pin
Peter Huber SG14-Sep-06 5:30
mvaPeter Huber SG14-Sep-06 5:30 
GeneralRe: cast exception Pin
Jon Dwan18-Sep-06 1:04
Jon Dwan18-Sep-06 1:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.