Click here to Skip to main content
Click here to Skip to main content

Auto Saving DataGridView Rows to a SQL Server Database

, 26 Jan 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
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
SW Dev Manager from Switzerland living in Singapore
I would be very interested to do SW development in Singapore for an international .NET project.

Comments and Discussions

QuestionMaster-Slave, how to do it Pinmemberdherrmann12-Mar-13 9:16 
QuestionInteresting PinmemberMember 365653120-Sep-12 11:53 
QuestionThanks, it is very usefull sample and guidance Pinmembersanthosh19698-Aug-12 5:15 
Questionneed to help Pinmemberaminsoro5-Mar-12 2:29 
QuestionSimple method PinmemberJasmine250113-Nov-11 18:17 
AnswerRe: Simple method PinmemberBrianGoodheim9-Dec-12 9:21 
AnswerRe: Simple method PinmemberMPascu9-Nov-14 11:23 
BugWhat about deleted, added and detached? PinmemberMrDeej19-Aug-11 4:30 
GeneralMy vote of 5 PinmemberHiraHaque5-Jul-11 5:50 
GeneralThanks PingroupYZK30-Mar-11 1:06 
NewsAlternative to Form.FormClosed event is Component.Disposed event Pinmemberskewty23-Mar-10 20:18 
GeneralRe: Alternative to Form.FormClosed event is Component.Disposed event Pinmemberdajvid8-Nov-12 0:09 
QuestionAnother DB please? Pinmemberi_microsoft27-Feb-10 12:19 
AnswerRe: Another DB please? PinmemberRaviRanjankr5-Apr-11 8:16 
QuestionVersion for Visual studio 2008 Pinmemberfrancis bohorquez15-Dec-09 11:36 
QuestionTrouble adding a row to empty datagridview PinmemberEZ175221-Jul-09 13:07 
Questionis this better solution? Pinmembermekklot18-May-09 23:30 
QuestionNot getting the PositionChanged event PinmemberSam Lambert7-Mar-08 15:32 
AnswerRe: Not getting the PositionChanged event Pinmemberpah_lee27-Aug-08 21:35 
Generalvb solution PinmemberAl Kearns14-Jan-08 10:55 
QuestionRe: vb solution Pinmemberlumartineru4-Oct-08 20:07 
QuestionRowLeave ? Pinmemberandycted22-Oct-07 23:49 
AnswerRe: RowLeave ? Pinmemberandycted17-Nov-07 3:28 
GeneralAdding new rows to the DataGridView UI PinmemberSubrahmanyam K10-Oct-07 22:50 
GeneralRe: Adding new rows to the DataGridView UI PinmemberMomony7-Dec-07 12:57 
I had that similar problem of specifying default values for columns in the datatable that were not in the datagridview.
I did handled the DataGridView's Default values needed, and retrieved the underlying datatable row from the bindingsource's Current property.
The code looked like this:
private void HandleGridViewDefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
{ if (e.Row.IsNewRow)
{ DataRowView currentRowView = BindingSource.Current as DataRowView;
MyTableRow currentRow = currentRowView.Row as MyTableRow
object currentID = currentRow["MyIDColumn"];
if (Convert.IsDBNull(currentID))
{ currentRow["MyIDColumn"] = Guid.NewGuid();
Hope that helps,
QuestionGreat article ... but what about prompting before saving? Pinmemberseehsarp10-Jun-07 4:10 
GeneralThanks - Great article, just what I needed! PinmemberCSutherland6-Jun-07 8:09 
GeneralCant get it to update my db. PinmemberJon Henry14-May-07 15:41 
GeneralRe: Cant get it to update my db. PinmemberElphabah30-Aug-07 16:24 
QuestionValidate the row? Pinmemberanders@sms10-Apr-07 22:53 
GeneralAnother approach Pinmemberbamboozle6-Jan-07 3:36 
QuestionRe: Another approach PinmemberYahshuatwo22-Mar-07 4:32 
GeneralRe: Another approach Pinmemberradioman.lt11-Sep-08 8:55 
QuestionRe: Another approach [modified] Pinmemberlumartineru5-Oct-08 12:35 
GeneralRe: Another approach Pinmemberg_g_1-Sep-09 20:08 
Generalbut if I have a query Pinmembermhowin6-Nov-06 6:20 
GeneralRe: but if I have a query PinmemberJohnieBraaf22-Mar-07 12:33 
QuestionDatabase not getting Updated PinmemberJon Dwan20-Sep-06 2:35 
AnswerRe: Database not getting Updated PinmemberC++reativeSoft22-Nov-06 10:45 
GeneralI did in an other way Pinmemberinamgul20-Aug-06 22:34 
GeneralNew record add Pinmemberhominoid28-Jul-06 4:35 
GeneralThanks! And ... here's a class to encapsulate it. PinmemberJeff Roush25-Jul-06 10:39 
GeneralRe: Thanks! And ... here's a class to encapsulate it. Pinmemberstuallenii28-Aug-06 10:11 
GeneralIn addition to form's OnClose() event... PinmemberDocX21-Jun-06 10:03 
GeneralRe: In addition to form's OnClose() event... PinmemberJim Waverly14-Jul-06 9:25 
GeneralDataTable events can be used, I suppose Pinmemberlponiew11-May-06 11:41 
GeneralRe: DataTable events can be used, I suppose Pinmemberfbachan22-Jun-06 2:04 
AnswerRe: DataTable events can be used, I suppose Pinmemberddrinka28-Feb-07 21:34 
GeneralDatagridView rotate Pinmemberhorvat9-May-06 20:57 
GeneralProblem while updating PinmemberManish Jaiswal10-Apr-06 14:49 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 26 Jan 2006
Article Copyright 2006 by Peter Huber SG
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid