Click here to Skip to main content
14,970,162 members
Articles / Desktop Programming / Windows Forms
Posted 26 Jan 2006


163 bookmarked

Auto Saving DataGridView Rows to a SQL Server Database

Rate me:
Please Sign up or sign in to vote.
4.83/5 (43 votes)
26 Jan 2006CPOL4 min read
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.


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.


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 = 
  if (ThisDataRow.RowState==DataRowState.Modified) {

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)

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() {

  private void MainForm_Load(
    object sender, EventArgs e) 
    // resize the column once, but allow the
    // users to change it.
  //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) {
  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 = 
    DataRow ThisDataRow=
    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");

    // track the current row for next 
    // PositionChanged event
    LastDataRow = ThisDataRow;

  private void MainForm_FormClosed(
    object sender, FormClosedEventArgs e) 

Event analysis

As a bonus, find a trace of the events involved when the user changes the content of a cell in the DataGridView:

    CellEditMode: False
    CellEditMode: True
    RowState: Unchanged; HasVersion 'DCOP'
    RowState: Unchanged; HasVersion 'DCOP'
    CellEditMode: True
    CellEditMode: False
    CellEditMode: False 
    RowState: Unchanged; HasVersion 'DCOP'
    RowState: Modified ; HasVersion 'DCO ' 
    RowState: Modified ; HasVersion 'DCO '
    RowState: Modified ; HasVersion 'DCO '
    CellEditMode: False
    CellEditMode: False   
    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.


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.


  • 27.1.2006: Original posting.


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


About the Author

Peter Huber SG
Software Developer (Senior)
Singapore Singapore
Retired SW Developer from Switzerland living in Singapore

Interested in WPF projects.

Comments and Discussions

Questioni want to select from a database and edit it in datagridview and save it back to the database Pin
Member 1186093028-Jul-15 3:21
MemberMember 1186093028-Jul-15 3:21 
QuestionMaster-Slave, how to do it Pin
dherrmann12-Mar-13 8:16
Memberdherrmann12-Mar-13 8:16 
QuestionInteresting Pin
Member 365653120-Sep-12 10:53
MemberMember 365653120-Sep-12 10:53 
QuestionThanks, it is very usefull sample and guidance Pin
santhosh19698-Aug-12 4:15
Membersanthosh19698-Aug-12 4:15 
Questionneed to help Pin
aminsoro5-Mar-12 1:29
Memberaminsoro5-Mar-12 1:29 
QuestionSimple method Pin
Jasmine250113-Nov-11 17:17
MemberJasmine250113-Nov-11 17:17 
AnswerRe: Simple method Pin
BrianGoodheim9-Dec-12 8:21
MemberBrianGoodheim9-Dec-12 8:21 
AnswerRe: Simple method Pin
MPascu9-Nov-14 10:23
MemberMPascu9-Nov-14 10:23 
BugWhat about deleted, added and detached? Pin
MrDeej19-Aug-11 3:30
MemberMrDeej19-Aug-11 3:30 
GeneralMy vote of 5 Pin
HiraHaque5-Jul-11 4:50
MemberHiraHaque5-Jul-11 4:50 
GeneralThanks Pin
YZK30-Mar-11 0:06
MemberYZK30-Mar-11 0:06 
NewsAlternative to Form.FormClosed event is Component.Disposed event Pin
skewty23-Mar-10 19:18
Memberskewty23-Mar-10 19:18 
GeneralRe: Alternative to Form.FormClosed event is Component.Disposed event Pin
dajvid7-Nov-12 23:09
Memberdajvid7-Nov-12 23:09 
QuestionAnother DB please? Pin
i_microsoft27-Feb-10 11:19
Memberi_microsoft27-Feb-10 11:19 
AnswerRe: Another DB please? Pin
RaviRanjanKr5-Apr-11 7:16
professionalRaviRanjanKr5-Apr-11 7:16 
QuestionVersion for Visual studio 2008 Pin
francis bohorquez15-Dec-09 10:36
Memberfrancis bohorquez15-Dec-09 10:36 
QuestionTrouble adding a row to empty datagridview Pin
EZ175221-Jul-09 12:07
MemberEZ175221-Jul-09 12:07 
Questionis this better solution? Pin
mekklot18-May-09 22:30
Membermekklot18-May-09 22:30 
QuestionNot getting the PositionChanged event Pin
Sam Lambert7-Mar-08 14:32
MemberSam Lambert7-Mar-08 14:32 
AnswerRe: Not getting the PositionChanged event Pin
Tóth Pál27-Aug-08 20:35
MemberTóth Pál27-Aug-08 20:35 
Generalvb solution Pin
Al Kearns14-Jan-08 9:55
MemberAl Kearns14-Jan-08 9:55 
QuestionRe: vb solution Pin
lumartineru4-Oct-08 19:07
Memberlumartineru4-Oct-08 19:07 
QuestionRowLeave ? Pin
andycted22-Oct-07 22:49
Memberandycted22-Oct-07 22:49 
AnswerRe: RowLeave ? Pin
andycted17-Nov-07 2:28
Memberandycted17-Nov-07 2:28 
GeneralAdding new rows to the DataGridView UI Pin
Subrahmanyam K10-Oct-07 21:50
MemberSubrahmanyam K10-Oct-07 21:50 

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.