How to test a DataTable update in RowChanging event handler
I have a WPF DataGrid bound to a DataTable. I fill the underlying DataSet from an arbitrary table in a database. I've attached to both the DataTable RowChanging and RowChanged events. As the user changes a row, these events fire and allow me to validate the row.
To get the optimal behavior from the DataGrid, it seems apparent to me that the e.Row.RowError message should be set and and exception thrown from the RowChanging event handler. I have some xaml that marks the row as error in the row header so it's visible and I get a nice tooltip with the error message. By optimal, I mean that the escape sequences that one expects with these grids works as expected when the validation is handled as described. Trying to perform the same validation from the RowChanged event makes for some funky behavior that does not roll back the edits properly.
The problem that I have is that I need to update the underlying DataSet so all the DB validation rules are applied and collisions with other user's changes can be detected in the RowChanging handler. If the action fails, I can flag the validation as described. But, the e.Row.RowState comes in as Unchanged and if I pass its containing DataSet to my DB update method, its DataAdapter.Update(myDataTable) method does not see the row as changed and, therefore, does nothing. This behavior contrasts with what would happens when I do the same in the RowChanged handler. At that point, the record (Current/Original/Proposed) values are updated and the record marked Modified.
An DataAdapter update at that point results in database activity. But, I'm at the wrong point in the event sequence in the case of a failure. I can flag the error but the rollback behavior for the grid will not work correctly (typically results in the changed cells not rolling back).
My question is, how does one get the record (or a copy of the record??) in the Modified state so the database will be updated? I normally use a typed DataSet but this time I am going after arbitrary tables and, therefore, am using a DataSet.
Answer
OK, it got a little interesting but I finally worked it out. The key was to handle the add & modify events in the RowChanging handler and the delete event in the RowDeleted handler. I'll present enough code to save the next person several hours of head scratching.
In the code below, _dataSet is a DataSet filled via a DataAdapter. _dataTable is _dataSet.Tables[0].DefaultView. _dataTable is bound to the DataGrid in the XAML as the ItemsSource. This code is in my ViewModel but it could have been in the Model code just as well. I cut it down a bit so it may have to be tweaked to work in code for you.
private void AttachDataTableEvents()
{
_dataTable.RowChanging += new DataRowChangeEventHandler(DataTable_RowChanging);
_dataTable.RowChanged += new DataRowChangeEventHandler(DataTable_RowChanged);
_dataTable.RowDeleting += new DataRowChangeEventHandler(DataTable_RowDeleting);
_dataTable.RowDeleted += new DataRowChangeEventHandler(DataTable_RowDeleted);
}
private void DataTable_RowChanging(object sender, DataRowChangeEventArgs e)
{
Trace.WriteLine(string.Format("DataTable_RowChanging(): Action {0}, RowState {1}", e.Action, e.Row.RowState));
if (e.Action == DataRowAction.Add)
{
e.Row.ClearErrors();
DataTable updateDataTable = CreateUpdateDataTableForRowAdd(_dataSet, 0, e.Row);
int rowsAffected;
string errorMessage;
if (!UpdateTableData(updateDataTable, out rowsAffected, out errorMessage))
{
e.Row.RowError = errorMessage;
throw new ArgumentException(errorMessage);
}
}
else if (e.Action == DataRowAction.Change)
{
e.Row.ClearErrors();
DataTable updateDataTable = CreateUpdateDataTableForRowChange(_dataSet, 0, e.Row);
int rowsAffected;
string errorMessage;
if (!UpdateTableData(updateDataTable, out rowsAffected, out errorMessage))
{
e.Row.RowError = errorMessage;
throw new ArgumentException(errorMessage);
}
}
}
private void DataTable_RowChanged(object sender, DataRowChangeEventArgs e)
{
Trace.WriteLine(string.Format("DataTable_RowChanged(): Action {0}, RowState {1}", e.Action, e.Row.RowState));
if (e.Action == DataRowAction.Add)
{
e.Row.AcceptChanges();
}
else if (e.Action == DataRowAction.Change)
{
e.Row.AcceptChanges();
}
}
private void DataTable_RowDeleting(object sender, DataRowChangeEventArgs e)
{
Trace.WriteLine(string.Format("DataTable_RowDeleting(): Action {0}, RowState {1}", e.Action, e.Row.RowState));
// can't stop the operation here
}
private void DataTable_RowDeleted(object sender, DataRowChangeEventArgs e)
{
Trace.WriteLine(string.Format("DataTable_RowDeleted(): Action {0}, RowState {1}", e.Action, e.Row.RowState));
DataTable updateDataTable = CreateUpdateDataTableForRowDelete(_dataSet, 0, e.Row);
int rowsAffected;
string errorMessage;
if (!UpdateTableData(updateDataTable, out rowsAffected, out errorMessage))
{
e.Row.RejectChanges();
Mediator mediator = _iUnityContainer.Resolve<Mediator>();
mediator.NotifyColleagues<string>(MediatorMessages.NotifyViaModalDialog, errorMessage);
}
else
{
e.Row.AcceptChanges();
}
}
The key was to make a new DataTable with the record to be updated. This DataTable is then passed to the DataAdapter.Update(dataTable) method. For the add/change/delete events, a clone of the DataSet schema was made and then a record was added to the DataTable in the correct state. The three helper functions shown below returned a DataTable with the record in the appropriate state and with the correct column information in the Current/Original/Proposed members.
private static DataTable CreateUpdateDataTableForRowAdd(DataSet originalDataSet, int originalDataTableIndex, DataRow addedDataRow)
{
DataSet updateDataSet = originalDataSet.Clone();
DataTable updateDataTable = updateDataSet.Tables[originalDataTableIndex];
DataRow dataRow = updateDataTable.NewRow();
int columnCount = updateDataTable.Columns.Count;
for (int i = 0; i < columnCount; ++i)
{
dataRow[i] = addedDataRow[i, DataRowVersion.Proposed];
}
updateDataTable.Rows.Add(dataRow);
// dataRow state is *Added*
return updateDataTable;
}
private static DataTable CreateUpdateDataTableForRowChange(DataSet originalDataSet, int originalDataTableIndex, DataRow changedDataRow)
{
DataSet updateDataSet = originalDataSet.Clone();
DataTable updateDataTable = updateDataSet.Tables[originalDataTableIndex];
DataRow dataRow = updateDataTable.NewRow();
int columnCount = updateDataTable.Columns.Count;
for (int i = 0; i < columnCount; ++i)
{
dataRow[i] = changedDataRow[i, DataRowVersion.Original];
}
updateDataTable.Rows.Add(dataRow);
dataRow.AcceptChanges();
dataRow.BeginEdit();
for (int i = 0; i < columnCount; ++i)
{
dataRow[i] = changedDataRow[i, DataRowVersion.Proposed];
}
dataRow.EndEdit();
// dataRow state is *Modified*
return updateDataTable;
}
private static DataTable CreateUpdateDataTableForRowDelete(DataSet originalDataSet, int originalDataTableIndex, DataRow deletedDataRow)
{
DataSet updateDataSet = originalDataSet.Clone();
DataTable updateDataTable = updateDataSet.Tables[originalDataTableIndex];
DataRow dataRow = updateDataTable.NewRow();
int columnCount = updateDataTable.Columns.Count;
for (int i = 0; i < columnCount; ++i)
{
dataRow[i] = deletedDataRow[i, DataRowVersion.Original];
}
updateDataTable.Rows.Add(dataRow);
dataRow.AcceptChanges();
dataRow.Delete();
// dataRow state is *Deleted*
return updateDataTable;
}
If the code above is implemented, the behavior is almost correct. The problem that is seen is when validation fails as you move off the record. The first time it works, i.e., the error marker is shown on the row header. However, if you move into the record as if editing but don't change any values, and then move off again, the error indicator goes away. However, you are still prevented from moving to another cell in the grid before moving back to the row and cancel the edit.
In order get that behavior right, you need to add a validation rule for the grid:
<DataGrid Grid.Column="1" Grid.Row="1" AutoGenerateColumns="True" ItemsSource="{Binding TableDataView}" Name="_gridTableGrid" CanUserDeleteRows="True" CanUserAddRows="True" RowHeaderWidth="25" CanUserResizeRows="False">
<DataGrid.RowValidationRules>
<local:DataGridRowValidationRule ValidationStep="CommittedValue" />
</DataGrid.RowValidationRules>
</DataGrid>
Then, in the code-behind, add the following:
public class DataGridRowValidationRule : ValidationRule
{
public override ValidationResult Validate(object value, System.Globalization.CultureInfo cultureInfo)
{
BindingGroup bindingGroup = (BindingGroup)value;
if (bindingGroup.Items.Count > 0)
{
System.Data.DataRowView dataRowView = bindingGroup.Items[0] as System.Data.DataRowView;
if (dataRowView.Row.HasErrors)
{
string errorMessage = string.IsNullOrWhiteSpace(dataRowView.Row.RowError) ? "There is an unspecified error in the row" : dataRowView.Row.RowError;
return new ValidationResult(false, errorMessage);
}
else
{
return ValidationResult.ValidResult;
}
}
else
{
return ValidationResult.ValidResult;
}
}
}
Now the error indication works robustly.
The last issue that needs to be dealt with revolves around auto-generated index values. If there is a table with an auto-generated index, it's possible to enter a different value in that field, as well as other fields, and commit the record (tab off it or return). If the grid view is refreshed, we'll see that the other fields have changed but the key has retained its initial value. I'll have to figure out how to retrieve/redisplay that record without having to retrieve/refresh all other rows (an arbitrary and possibly large number).
This effort has resulted in retaining the cancel edit behavior expected through the standard escape sequence. I.e., if the record validation fails, the first cancels the current cell edit; the second cancels the row edit.
Share and enjoy!
Edit: I added the validation rule used in the XAML and the code-behind to get robust error indication. Sorry about such a long answer. If I had figured all this out in the first place, I would have chosen a more appropriate forum to present the method.