JYelton JYelton - 1 year ago 166
SQL Question

How to determine if DataGridView contains uncommitted changes when bound to a SqlDataAdapter

I have a form which contains a DataGridView, a BindingSource, a DataTable, and a SqlDataAdapter. I populate the grid and data bindings as follows:

private BindingSource bindingSource = new BindingSource();
private DataTable table = new DataTable();
private SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM table ORDER BY id ASC;", ClassSql.SqlConn());
private void LoadData()
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = bindingSource;
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
bindingSource.DataSource = table;

The user can then make changes to the data, and commit those changes or discard them by clicking either a save or cancel button, respectively.

private void btnSave_Click(object sender, EventArgs e)
// save everything to the displays table

private void btnCancel_Click(object sender, EventArgs e)
// alert user if unsaved changes, otherwise close form

I would like to add a dialog if cancel is clicked that warns the user of unsaved changes, if unsaved changes exist.


How can I determine if the user has modified data in the DataGridView but not committed it to the database? Is there an easy way to compare the current DataGridView data to the last-retrieved query? (Note that there would not be any other threads or users altering data in SQL at the same time.)

Answer Source

In order to detect changes in the DataGridView, I wound up using two events, the CellValueChanged and CurrentCellDirtyStateChanged (the latter due to checkbox type columns).

When either of those events occurs, I set a boolean (UnsavedChanges) to indicate changes exist. When the form is closed or if the cancel button (now renamed to "Revert") is clicked, the boolean is checked and the dialog shown if set to true. If the save button is clicked, the boolean is set to false and the data saved.

While not as simple as checking one property of the databinding or datagrid, it works as desired.