I could easily be missing something simple, however, from my tests, I do not really see “how” you can do this easily without using the grid’s CellPainting event in addition to “creating” some container to hold the “edited” cells.
From your comment…
”Other solutions to similar (but not quite identical) questions, are to change the color in the rowpaint / valuechanged etc... events, but
that's not relevant because they apply a color based on the cell's
VALUE (pending/waiting/paid, etc...) and not on the cell's "changed"
or "modified" state.”
You would be somewhat correct in this statement in a sense that you state that all the examples you looked at determined the cells back color ”based on the cells VALUE” and this may well be true… however, this does not mean YOU have to color the cells based the cells value. You can base the cells back color on anything you like. In this case you want to color the cell based on IF the cell has been “edited.”
I hope I understand the goal… is what you want to do is “color” each cell that has been “edited” in the grid since the last time the user pressed the “save” button. In addition, (the hard part) you want to maintain these “colored-edited” cells if the grid is sorted and/or filtered and when rows are added or removed. Because of the sorting, filtering and add/remove of the row’s requirement, this will require some extra work and is a little more challenging than it may appear at first glance.
My solution below does use the grid’s CellPainting event as a last resort. I tried numerous ways using the grids CellEditingControlShowing, CellBegin/EndEdit among others. In each test, the main problem I ran into using the grid’s non-painting events, is when the grid is sorted, filtered or if rows were deleted. If you take the sorting, filtering and deleting out of the picture, then the problem is fairly trivial.
So, if sorting, filtering and deleting do not apply, then you can use the grids CellValueChanged event, CurrentCellDirtyStateChanged event or others and achieve this without having to wire up one of the grid’s “painting” events.
A small but important WARNING!
As I previously hinted, I will usually try to avoid using the grid’s painting events for numerous reasons. One reason is that they fire often. The grids CellPainting event could literally fire hundreds/thousands of times when loading a grid or when the grid is sorted etc. Obviously, the size of the grid is a large factor when determining how many times the event fires.
The point is that when using the grids “painting” events, it is wise to take extra precautions to avoid having your code impact the grids performance.
Example, if we put some code that possibly takes a long time to execute into the grids CellPainting event, then you could easily experience a “sluggish” UI. The grid will appear to be frozen or the cursor moves in jumps and not smoothly or you press the enter key in a cell and it takes a second or two for the grid to respond… etc. … You want to be careful when doing things in any “PAINT” type event. And the answer below is NO EXCEPTION to this rule and could easily fall prey to causing a “sluggish” grid UI.
Given the warning, the problem in this solution goes like this… We will create a DataTable that holds all the cell changes done in the grid. When stated, this table is empty as no changes have been made. In the grids CellPainting event, the code needs to search through this table to find a row/column index that matches the current cells row/column index. Obviously as more changes are made to the grid, this table will grow in size and obviously take a little longer to search.
In my small tests with small amounts of data, this had no impact even with many edited cells sorted or filtered, however, it is NOT difficult to see an eventual performance hit if the data is large to start with and the ChangesGridTable gets to a large size and the search is slow. Fortunately, this is easily remedied by simply saving the “edited” cells and clearing the “changes” table. Just a heads up.
Warnings be dammed let’s implement it anyway…
Let us step back and separate the “data” and the UI. As we already know, we can easily get the “changed rows” from the DataTable. In this case, we may not know which individual “cells” have changed, however, I am confident that updating the DB table with one value or all values for that row is fairly irrelevant. In other words, from the DB perspective we do not care which “cells” were “edited” we only care about the row since we will update the whole row.
If you want to update individual cells, then the solution below will help, however, a far as saving the “data” to the DB perspective… we are done. When the user clicks the Save button to save the “edited” cells, the code simply saves the data and clears the table of “edited” cell rows. Therefore, the main focus from here on is in the UI.
Do we really need a collection of the edited cells?…
If we want to keep track of the cells that have been changed/edited since the last time the save button was clicked, then, I do not see how you can do this without some “collection” that keeps track of the “edited” cells.
First attempts used the cells Tag property. Unfortunately, the cells Tag property can get cleared by the grid. Specifically, if the user clicks on a column header to sort the grid, the Tag will be cleared. If EACH cell had a property that we could use to identify this “edited” state, then, problem solved. Unfortunately, the cell property does not exist. So, we can check the cells Tag property off the list of options.
Your suggestion to add extra invisible columns to keep track of the changes, could work, however, since this is addition is per “row” you would need to do something extra to keep track of the individual cells. In addition, you may be creating a lot of extra cell locations in the table that never contain any usable data. I.E., the cells that were NOT edited. This may waste a lot of space. Also, I try to avoid ANYTHING that “alters” the original source data from the DB. Obviously any changes to the original table may require additional code when saving the table back to the DB since it has been altered. So, I would say, this option is debatable but doable. In this case, I would check it off the list simply from the “altering” of the original table but I am confident it could work.
Code using a DataTable as a collection to hold the changed cells in the grid.
To help, you should be able and copy/past the code below with some small name changes and test what is described below. Create a new VB winform project, drop two (2) DataGridviews onto a form and rename them as shown, and a single button. The grid on right that holds the ChangesGridTable would obviously not be displayed and is only used for testing to visually see the rows being added/deleted/updated. The final product may look something like below. Note the grid on the left has been sorted on Col1 ...
When a cell’s value is changed, we will add a row to this table with the pertinent information. The properties/columns in the table would be the obvious row and column index of the cell in relation to the grid’s data source which is also a DataTable. As per your requirements, we will also add the additional ChangeDate DateTime property, an OldValue and NewValue string property. This ChangesGridTable may look something like…
Dim dt = New DataTable()
dt.Columns.Add("DTRowIndex", GetType(Int32))
dt.Columns.Add("DTColIndex", GetType(Int32))
dt.Columns.Add("ChangeDate", GetType(DateTime))
dt.Columns.Add("OldValue", GetType(String))
dt.Columns.Add("NewValue", GetType(String))
We may use a row from this table in the grids CellPainting event. For each cell being painted, we will grab that cells “data” row’s index number. Then we will search through the rows in the ChangesGridTable and check to see if that cells row and column index matches at least one of the rows/columns (DTRowIndex, DTColIndex) indexes in the “ChangesGridTable.” If we find a match, then we know to color that cell.
Below is the grid’s CellPainting event with some commented debug statements for testing and additional error checking…
‘Dim ecount As Int32 = 1
Private Sub DataGridView1_CellPainting(sender As Object, e As DataGridViewCellPaintingEventArgs) Handles UserGrid.CellPainting
'Debug.WriteLine("CellPainting-> Enter Count: " + count.ToString())
If (ChangesGridTable.Rows.Count > 0) Then
If e.RowIndex >= 0 And e.ColumnIndex >= 0 Then
If Not UserGrid.Rows(e.RowIndex).IsNewRow Then
Dim drv As DataRowView = UserGrid.Rows(e.RowIndex).DataBoundItem ' <- Get the DATA row that this GRID row points to
Dim RowIndex As Int32 = UserGridTable.Rows.IndexOf(drv.Row) ' <- Get that DATA rows index in it DataTable
' Check to see if this row and column index matches any of the row/col indexes in the ChangesTable
Dim result = ChangesGridTable.Select("DTRowIndex = " & RowIndex & " AND DTColIndex = " & e.ColumnIndex)
If result.Length > 0 Then
e.CellStyle.BackColor = Color.LightGreen
'Debug.WriteLine("CellPainting-> Enter Count: " + ecount.ToString())
'ecount = ecount + 1
End If
End If
End If
End If
'Debug.WriteLine("CellPainting-> Leave")
End Sub
Walking through the code… nothing exotic going on and pretty straight forward and for good reason, this code will be called many, many times. Obviously if the ChangesGridTable is empty, we can exit. Then the cursory bounds checking of the row and column indexes along with the grids “new” row check if one exists.
Since we now have a cell… we need to get that cells row index in the “data source.” For that we need the whole row….
Dim drv As DataRowView = UserGrid.Rows(e.RowIndex).DataBoundItem
From that row we can get its “data row” index from the UserGridTable with…
Dim RowIndex As Int32 = UserGridTable.Rows.IndexOf(drv.Row)
Now that we have the “data” row index of that cell, we can search through the ChangesGridTable to see if there is a match. If we get a match with the same row and column index , then color that cell.
Dim result = ChangesGridTable.Select("DTRowIndex = " & RowIndex & " AND DTColIndex = " & e.ColumnIndex)
Pointing to the previous warning… this last step to “search” for the cell indexes in the ChangesGridTable collection “ideally” would be as fast as possible. Using the DataTables Select method may not be the best choice as far as speed is concerned. I am guessing that if you needed to speed this up, then a different data structure would be used. A hash table may be a better choice. I will leave this up to you. I am confident there are many different “better” approaches as far as speed is concerned. Point being, THIS “searching” is the only thing that would cause the UI to start lagging.
We can paint the cells, but how do we add rows to the ChangesGridTable?
So now we have the grids CellPainting event working to color the cells that are in the ChangesGridTable. Therefore, next we need to figure out which grid event(s) to use to add rows to the ChangesGridTable.
First… I am confident/aware there are numerous different ways to do this. And one approach is probably just as good as the other if it works. I am certainly open to any suggestions/comments.
I selected the grid’s CurrentCellDirtyStateChanged event for this. One reason is to handle the situation where the user is “editing” a cell, and the grids cell is in “edit” mode, and the user has already made some changes in the cell, and finally, the user presses the “Esc” key. In that situation, using the grids events, the code will still color that cell as “edited” because the user has already typed some characters into the cell.
Unfortunately, even if you look for the “Esc” key by subscribing to the cell’s keypress event, it will not work. Reason being… that while the cell is in “edit” mode and the user presses the “Esc” key… this is the grid’s default call to “cancel” the edit. Therefore, when the user presses the “Esc” key… the grids “edit” mode ENDS right then and there. It will reset the cell back to its original value and exit its “edit” mode. The cell’s keypress event will not get fired in that case. In many cases this is irrelevant… in this case, it is relevant.
Another reason to use the CurrentCellDirtyStateChanged event is because we want to “keep” the old value and the new value as data in the ‘ChangedGridTable.`
If you trace the grids CurrentCellDirtyStateChanged event you may notice that it fires TWICE. Once when the user starts “editing” a cell, and a second time when the user leaves the cell. We are going to take advantage of this to help with both the “Esc” key issue and retaining the old value.
To help, we will create a global string variable called OriginalCellValue. Initially its value will be set to null/Nothing. The idea, is that when the dirty cell event fires, the first check in the code is on this OriginalCellValue variable… its value will be either null or some string value. This will allow us determine if the cell’s value has been changed or not changed. This is explained further below the code.
Dim OriginalCellValue As String = Nothing
Private Sub DataGridView1_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles UserGrid.CurrentCellDirtyStateChanged
If UserGrid.CurrentCell.RowIndex >= 0 Then
If OriginalCellValue IsNot Nothing Then
Dim curCell As DataGridViewCell = UserGrid.CurrentCell
If curCell.Value IsNot Nothing Then
If OriginalCellValue <> curCell.Value.ToString() Then
Dim drv As DataRowView = UserGrid.Rows(UserGrid.CurrentCell.RowIndex).DataBoundItem
Dim dtRowIndex As Int32 = UserGridTable.Rows.IndexOf(drv.Row)
If (dtRowIndex = -1) Then ' <- if -1, then the user changed a cell in the grids "new" row
dtRowIndex = UserGridTable.Rows.Count ' <- even though the row may not exit in the table now
End If ' we know it will get added after the event exits
ChangesGridTable.Rows.Add(dtRowIndex,
curCell.ColumnIndex,
DateTime.Now,
OriginalCellValue,
curCell.Value.ToString())
End If
OriginalCellValue = Nothing
End If
Else
OriginalCellValue = UserGrid.CurrentCell.Value.ToString()
End If
End If
End Sub
Walking through the code, we have the cursory index bounds checking, etc.… then…
If the OriginalCellValue is null/nothing then it has not yet been set and this tells us that this is the FIRST time the event is fired. So, we simply set the cells value to the OriginalCellValue string. The event exits and the user continues to edit the cell. Now, we have the starting/original cell value in the OrignalCellValue variable.
Sometime later, the user finishes “editing” the cell and tries to leave the cell, then the CurrentCellDirtyStateChanged event fires for the second time. Again, the first check is on the OriginalCellValue string. It obviously will not be null this time around, so a check is made to see if the current cells value “matches” the OriginalCellValue string.
If the cells string value “matches” the OriginalCellValue string, then, that means that the user did not make any changes to the cell or possibly the user pressed the “Esc” key after they did make some changes.
If the cells string value does NOT match the OriginalCellValue, then, that means that the user did indeed change something in the cell. Therefore, we want to save this changed cell by adding a row to the ChangesGridTable.
We have most of what we need. The cells column index, the date, the cells current “edited/new” value and the cells original value in OriginalCellValue. And finally, we grab the “data” row index from the grids data source using that rows DataBoundItem property.
Maintaining the ChangesGridTable when rows are deleted…
This should take care of most the user interaction when sorting/filtering and even when “Adding” new rows. However, removing rows may throw the edited cells coloring off.
If the user clicks on a row header to select the entire row, then presses the delete key, then… the row will be removed from the grid. In addition, the underlying data row will also get removed from the table. This will mean the ChangesGridTable may fall into an inconsistent state without some extra work.
Obviously, we would need to remove all the rows that have the same deleted rows index. This is fairly trivial even when there may be “multiple” rows with the same DTRowIndex value. Unfortunately, there is another issue…
If any cell in the remaining ChangesGridTable has a DTRowIndex value that is “greater” than the row index of the deleted row, then those indexes will need to be “moved up” one (1) row since a row above that row was deleted. The row indexes above the deleted row obviously will not change.
Therefore, to keep it simple, when a row is deleted, we need three (3) steps to keep the ChangesGridTable in a consistent state…
Collect a list of row indexes from the ChangesGridTable that
contain ALL the rows that have the same DTRowIndex value as the
given value. Remember, there may be more than one (1).
Loop through that list and remove those rows from the ChangesGridTable.
Loop through the remaining ChangesGridTable rows and decrement
by 1, each row’s DTRowIndex value IF its value is greater than the
deleted rows index.
Since we many need to call this method in both the UI and possibly in code, creating a method to delete a row given a row index from the ChangesGridTable may come in handy and look something like…
Private Sub RemoveRowFromChangesTable(targetDTIndex As Int32)
Dim rowsToDel As List(Of DataRow) = New List(Of DataRow)()
' Get an int list of all the row indexs of the edited row
' that MATCH the given targetDTIndex
For Each row As DataRow In ChangesGridTable.Rows
If row("DTRowIndex") = targetDTIndex Then
rowsToDel.Add(row)
End If
Next
' loop through the int list and remove those rows
For Each row As DataRow In rowsToDel
ChangesGridTable.Rows.Remove(row)
Next
' for each row "below" the removed row,
' we need to decrement those rows DTRowIndex value by 1 as it has moved up
For Each row As DataRow In ChangesGridTable.Rows
If row("DTRowIndex") > targetDTIndex Then
row("DTRowIndex") = row("DTRowIndex") - 1
End If
Next
End Sub
This should hopefully keep the ChangesGridTable in a consistent state when a row is removed. I will leave an “insert” up to you. However, it would be similar to a delete other than incrementing instead of decrementing.
If the row is deleted in code, then you should have full access to which row should be deleted so it would be a simple call to the above method. On the UI side, we need to figure out which “RowDelete” event to subscribe to so we can call the method above.
In this example, I used the grid’s UserDeletingRow event. This event fires each time a row is deleted. If there are multiple rows selected, then, this event will fire once for each deleted row. Therefore, all we need is that GRID rows corresponding “data” row index in the data source table. Then we can call the above method with the table row index. Something like…
Private Sub UserGrid_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs) Handles UserGrid.UserDeletingRow
Dim drv As DataRowView = UserGrid.Rows(e.Row.Index).DataBoundItem
Dim targetDTIndex = UserGridTable.Rows.IndexOf(drv.Row)
RemoveRowFromChangesTable(targetDTIndex)
End Sub
Getting close… we have the grids CellPainting event coloring the cells based on the rows in the ChangesGridTable. And we have the grids CurrentCellDirtyStateChanged event adding rows to the ChangesGridTable. In addition we have additional code to keep the ChangesGridTable in a consistent state if rows are removed from the table by the user or in code.
One thing left, is what to do when the “Save” button is pressed. Fortunately, this is two lines of code. After the data has been saved to the DB, we need to clear the grids previously colored “edited” cells. To clear the edited cells, all that is needed is to remove all the rows from the ChangesGridTable, then call the grid’s Invalidate method. The code above will do the rest. Something Like…
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnSave.Click
' Update the rows in the DB… check for newly added rows
ChangesGridTable.Rows.Clear()
UserGrid.Invalidate()
End Sub
Last item is filtering…
It is unknown how exactly you are filtering the data. In my tests, I used a simple DataView object, filtered that DataView, then set that DataView as a DataSource to the grid. Fortunately, in this context, the DataView will still contain ALL the cells even if they are not displayed. This will allow the coloring to work as expected without having to write extra code to maintain the ChangesGridTable consistent state as we did with the deleting of the row. Example…
To filter…
Dim dv As DataView = New DataView(UserGridTable)
dv.RowFilter = "Col1 > 12"
UserGrid.DataSource = dv
To un-filter…
UserGrid.DataSource = UserGridTable
Finally, to complete this example, the code below creates some data to test what is described above.
Dim UserGridTable As DataTable
Dim ChangesGridTable As DataTable
Dim OriginalCellValue As String = Nothing
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
UserGridTable = GetUserTable()
UserGrid.DataSource = UserGridTable
UserGrid.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
' initialize the changes table
ChangesGridTable = GetChangesTable()
ChangesGrid.DataSource = ChangesGridTable
End Sub
Private Function GetUserTable() As DataTable
Dim dt = New DataTable()
dt.Columns.Add("Col0", GetType(String))
dt.Columns.Add("Col1", GetType(Int32))
dt.Columns.Add("Col2", GetType(DateTime))
FillTable(dt)
Return dt
End Function
Private Function GetChangesTable() As DataTable
Dim dt = New DataTable()
dt.Columns.Add("DTRowIndex", GetType(Int32))
dt.Columns.Add("DTColIndex", GetType(Int32))
dt.Columns.Add("ChangeDate", GetType(DateTime))
dt.Columns.Add("OldValue", GetType(String))
dt.Columns.Add("NewValue", GetType(String))
Return dt
End Function
Private Sub FillTable(dt As DataTable)
Dim s As String
Dim i As Int32
Dim d As DateTime
Dim rand = New Random()
For index = 1 To 10
s = GetRandomString(rand, 10)
i = rand.Next(1, 100)
d = DateTime.Now.AddDays(rand.Next(-1000, 1000))
dt.Rows.Add(s, i, d)
Next
End Sub
Private Function GetRandomString(rand As Random, length As Int32) As String
Dim sb = New StringBuilder()
Dim letter As Char
Dim offset As Int32
For i = 1 To length
offset = Convert.ToInt32(Math.Floor(25 * rand.NextDouble()))
letter = Convert.ToChar(offset + 65)
sb.Append(letter)
Next
Return sb.ToString()
End Function
Sorry for the long post.
I hope this makes sense and helps. Good Luck
DataTable.If this is the case, are you aware of the DataTable.GetChanges Method ? This method returns aDataTablewith the same schema and will contain only the rows that have changed since the tablesAcceptChangesmethod was last called. This includes changes made in code. – JohnGAcceptChangesmethod. When the user clicks the save button, the code could check to see what rows were changed by calling the tablesGetChangesmethod. Granted, it does not specify “which” cells have changed or if there were “multiple” changes done, however, this does not appear to be an issue in this case. I can give an example; however, it is not clear if this is what you are asking. – JohnG