Developers need to track the edits (insert, update and delete) while working in any grid control and then provide the capability to utilize the same database connection to perform the updates, and optionally perform the updates as an atomic transaction.  There are use cases when a user changes data in a data grid, and those changes are immediately reflected back to the database.

Another scenario is one where the design requires that the user be able to make their edits and then commit all the changes at once. An example of this would be a master-detail use case, when child rows are associated with a master record, e.g. customers, orders, order detail.

In this example, all other development concerns such as data validation, concurrency, actually reading from or writing to a database are not part of the example code. This, simply, enables the reader to focus on one technique for tracking inserts, updates, and deletes.

Though, there are techniques for dynamically adding data to your data source without having to do this, but I wanted to allow C1DataGrid to perform its work in a natural way so our data source is derived from BindingList<T>.

Tracking Inserts, Updates and Deletes


When the user deletes a row in C1DataGrid, the row is removed from the collection and is no longer visible in the UI. This is where our custom class TrackBindingList<T> comes into play. This class keeps track of the deleted rows by adding a deleted row to an internal collection and then exposing a method (GetDeletedItems) to return those deleted rows when required. You may notice I've overridden the RemoveItem() method; the implementation adds the deleted row to the internal collection of deleted rows. It also exposes a method (GetChangedItems) to return only those non-deleted rows that have been inserted or updated.


public class TrackBindingList<T> : BindingList<T> where T : ITrackDirtyEntity {
IList<T> _deletedItems = new List<T>();
public TrackBindingList() {
}
public TrackBindingList(IList<T> list)
: base(list) {
}

public IEnumerable<T> GetAllItems() {
return this.Union(_deletedItems).ToList();
}
public IEnumerable<T> GetChangedItems() {
return this.Where(i => i.IsDirty).ToList();
}

public IEnumerable<T> GetDeletedItems() {
return _deletedItems;
}

protected override void ClearItems() {
base.ClearItems();
_deletedItems = new List<T>();
}

protected override void RemoveItem(Int32 index) {
var item = this[index];
_deletedItems.Add(item);
base.RemoveItem(index);
}
}

Consuming the TrackBindingList<T>


Another class 'MainWindowViewModel' exposes the 'Customers' collection. The collection is initialized and populated in the constructor (please do not populate your collections in your constructors, this is demo-only code).

You’ll notice that after loading the customers, I loop through the collection and set the IsDirty property to false. Your base class for your entity objects should handle this for you, so that when an object is populated from a database, the object is returned from the service layer in a non-dirty state to provide accurate tracking in the UI layer. The example below is over simplified on purpose to show how the view model will process the data once the user saves their changes.

The most important method below is the SaveExecute method that is invoked when the user clicks the Save button. The CanSaveExecute method determines if the collection has been changed or not. Any change to the collection will cause the Save button to be enabled.

The code in the SaveExecute method would execute in a service layer. The service layer method would receive the TrackBindingList<T> as an argument and would use a data layer to process the changes.

Within the SaveExecute method we can see the workflow:

  • Deleted items are removed from the database.

  • Then the inserted or updated items are committed to the database.

  • The view model would then reload the inserted and changed data into the data grid. This reloading refreshes the timestamps that play the role in concurrency and identity columns are populated after an item is inserted and reloaded from the database.



public class MainWindowViewModel : ObservableObject
{
TrackBindingList<Customer> _customers;
Boolean _customersDirty;
ICommand _saveCommand;
public TrackBindingList<Customer> Customers
{
get { return _customers; }
set
{
_customers = value;
RaisePropertyChanged("Customers");
}
}
public ICommand SaveCommand
{
get { return _saveCommand ?? (_saveCommand = new RelayCommand(SaveExecute, CanSaveExecute)); }
}
public MainWindowViewModel()
{
// load from the service layer
var list = new TrackBindingList<Customer>();
list.Add(new Customer { FirstName = "Jason", LastName = "Bourne", Id = 1 });
list.Add(new Customer { FirstName = "Alexander", LastName = "Conklin", Id = 2 });
list.Add(new Customer { FirstName = "David", LastName = "Webb", Id = 3 });
list.Add(new Customer { FirstName = "Marie", LastName = "StJacques", Id = 4 });
list.Add(new Customer { FirstName = "Moris", LastName = "Panov", Id = 5 });
list.Add(new Customer { FirstName = "Joshua", LastName = "Webb", Id = 6 });
foreach (var customer in list)
{
customer.IsDirty = false;
}
this.Customers = list;
this.Customers.ListChanged += (s, e) => _customersDirty = true;
}
Boolean CanSaveExecute()
{
return _customersDirty;
}
void SaveExecute()
{
foreach (var customer in this.Customers.GetDeletedItems())
{
Console.WriteLine(String.Format("Customer deleted: {0} {1}", customer.FirstName,   customer.LastName));
}
foreach (var customer in this.Customers.GetChangedItems())
{
if (customer.Id == 0)
{
// perform insert in service layer
customer.Id = this.Customers.Max(c => c.Id) + 1;
// simulate inserting into the data base
Console.WriteLine(String.Format("Customer inserted: {0} {1}", customer.FirstName, customer.LastName));
}
else
{
Console.WriteLine( String.Format("Customer updated: {0} {1}", customer.FirstName, customer.LastName));
}
}
foreach (var customer in this.Customers)
{
customer.IsDirty = false;
}
_customersDirty = false;
}
}


Here's a GIF of the operation performed at runtime using any of the attached samples. Please notice the changes being updated in the VisualStudio Output window.



 

 

 

 

 

 

 

 

Download C# Sample