WWW Yariv Hammer's Code Site

Thursday, October 20, 2005

Calculate a Cell value in a DataGrid based on the rows above it

In the previous post I showed how to use DataGridColumnStyle in order to paint a cells color based on a condition.
Now I will show how to calculate a cell value based on the rows above it. In the previous example I used a Transaction table with two columns: Amount and Balance. The Amount column is the amount of money deposited to the account in the transaction. Balance should be a calculated column, based on the row above it. Each time the balance is equal to the previous balance plus the amount in the current transacion. What happens when the user changes a deposit in the grid? All the balance column values should be recalculated. What happens if the grid is sorted? If a row is deleted? Each time the balance should be recalculated.
But how do we know when is data being editted? The DataGrid itself won't help us. While it has a CurrentCellChanged event, it does not contain any event to notify us when the data is actually changed.
We will need to catch events in the DataSource itself. In our case the DataSource is a DataView. Even if we bind the DataGrid to a DataTable, it is actually bound to the DefaultView property of the DataTable. What events do we have? We have the DataTable.RowChanged and DataTable.RowDeleted events (The first one handles addition and changing of a row). Those are fired when a DataRow is changed in the underlying table. But what do we do when the DataGrid is sorted, or the DataView is filtered? We have the DataView.ListChanged event to handle those cases.

Ok, so after we set the column styles, and set the DataSource we want to sign up for events:

private void Form1_Load(object sender, System.EventArgs e)
   DataTable dt = new DataTable("Transactions");
   dt.Columns.Add(new DataColumn("Amount",typeof(Int32)));
   dt.Columns.Add(new DataColumn("Balance",typeof(Int32)));

   DataView dv = new DataView(dt);
   dataGrid1.DataSource = dv;

(...) // See first article

   //Setup events
   dv.ListChanged += new ListChangedEventHandler(dv_ListChanged);
   dt.RowChanged += new DataRowChangeEventHandler(dt_RowChanged);
   dt.RowDeleted += new DataRowChangeEventHandler(dt_RowChanged);

For simplicity, I declared an empty table and the columns. In real scenarios you will need to use an actual table from the database.

Now you see at the end of the event handler that I listen to three events:

  • dv.ListChanged will be raised whenever someone will add or remove rows from the GUI - or whenever someone will change the RowFilter or Sort property of the DataView (including sorting from inside the grid).
  • dt.RowChanged will be raised whenever someone changes a value in the row, or add a row to the table by code.
  • dt.RowDeleted - Whenever someone deletes the row from the table by code.

Here is the event handlers code:

private bool _lock = false;
private void dv_ListChanged(object sender, ListChangedEventArgs e)
   if (_lock)
   _lock = true;
   int balance = 0;
   foreach (DataRowView drv in (DataView)sender)
      if (drv.Row["Amount"] == DBNull.Value)
         drv.Row["Amount"] = 0;
      drv.Row["Balance"] = balance;
   _lock = false;

private void dt_RowChanged(object sender, DataRowChangeEventArgs e)
   if (_lock)
   _lock = true;
   int balance = 0;
   foreach (DataRow dr in ((DataTable)sender).Rows)
      if (dr["Amount"] == DBNull.Value)
         dr["Amount"] = 0;
      dr["Balance"] = balance;
   _lock = false;

In both handlers we iterate on the sender (DataView or DataTable) and recalculate the Balance.
Notice the _lock member. I use this technic to prevent races between events. It happens a lot that by doing something inside the event handler other events are fired (For example, changes to the DataView will change the DataTable as well and vice versa). So by using the lock I prevent that from happening.
Also notice the use of one event handler for both RowChanged and RowDeleted.


Post a Comment

<< Home

Feel free to use everything here. Add links to my site if you wish.

Do not copy anything to other sites without adding link to here.

All the contents of the site belong to Yariv Hammer.