I’ve been dabbling with Winforms Data Biding and the DataGrid control for the past week, and here are some lessons I’ve Found (The code is in VB.NET):
Lesson 1: Displaying Many-To-Many Relationships Using DataBinding
Displaying A Master-Details relation in a forms is really not a big deal. You Set one control up which is bound to the master table, and one DataGrid which is bound to the relation between the Master Table and the Child Table like so:
adpSoldiers.Fill(ds, "Soldiers")
ds.Relations.Add("SoldierNotes", ds.Tables("Soldiers").Columns("ID"), ds.Tables("Notes").Columns("SoldierID"))
In this code, we have one table “Soldiers” and for each soldier we can have 1 or more notes. A simple one-to-many relationship. All we have to do in roder to display a master details form here is have two data grids, one bound to ds.Tables("Soldiers") and one bound to ds with a DataMember property of "Soldiers.SoldierNotes" .
The problem arises when we have a many-to-many relationship. Let’s say we have another table “Courses”. Now, each soldier can be enrolled into one or more courses, and each course can be taken by one or more soldiers. For that purpose, we have a third table “Registrations” which contains only “SoldierID” and “CourseID” as columns, with a PK comprised of both of these columns.
Suppose we want to show, for the selected soldier in grid 1, all the courses, including course name, which he is enrolled to.
Simple Data Binding to the relation between Soldiers and Registrations will not do.
adpSoldiers.Fill(ds, "Soldiers")
adpSoldiers.Fill(ds, "Courses")
adpSoldiers.Fill(ds, "Registrations")
ds.Relations.Add("SolCourses", ds.Tables("Soldiers").Columns("ID"), ds.Tables("Registrations ").Columns("SoldierID"))
We’ll get unreadable data in the “details” grid only showing “SoldierID” and “CourseID” columns. So what’s the solution?
Unfortunately, as much as I looked, there is not really elegant solution, but here’s the best way I’ve found to overcome this:
What we can do, is add calculated columns into our “registrations” datatable, which , for each DataRow in the table, display the value of the parent Courses row’s “Name” value, using a predefined relation:
First, we add this relation into our relations collection, which will allow us to retrieve the parent Courses Row for each Registrations Row according to the “CourseID” value:
ds.Relations.Add("CourseReg", ds.Tables("Courses").Columns("ID"), ds.Tables("Registrations ").Columns("CourseID"))
Next, We’ll add the calculated columns to our “Registrations” DataTable:
ds.Tables("Registrations").Columns.Add("Course", GetType(String), "Parent(CourseReg).Name")
What I pass in to the Columns constructor is the name of the new columns, the type of displayed data in that columns, and the expression that the columns will hold. To retrieve the parent row using a specified relation, I’ll write “Parent(RelationName).ColumName”. I could also get a child aggregate function using this data columns, by specifying “Count(Child(RelationsName).ID)”. If the DataTable only has one relation defined, I don’t even have to specify the name of the relation to use, like so: “Sum(Child.Price)”. To learn more about calculated columns, here’s a nice article about the subject.
OK. So now that we have our calculated column in place, we can use the same relation in the details grid, only this time we’ll see the name of the course as well as the other columns. The calculated columns in the DataGrid will be read-only, as they should be. A new problem is encountered, though. The user still cannot add new items to the DataGrid, without knowing in advance the ID of the needed course, and the soldier. (Once those values are entered inside the new row in the grid, the “name” column will display the name of the new course registration, but not before then). For that you’ll either have to have a combo box that will be placed over the “CourseID” Active cell, or other, more user-friendly external means of inserting a row to the DataTable.
Lesson 2: Reacting to and canceling illegal Row Editing in a DataGrid
Lets say that we have a Grid, bound to the Courses Table, but we don’t want the user to edit a course that has already started. We have a ”StartDate column in the Courses Table, so all we need to do now is know when a user has changed a value, and cancel that edit if needed.
The way to do this, is reacting to the DataTable’s RowChanging event. This event will be thrown once one of the Selected DataRow’s columns will be edited.
Here’s the code:
AddHandler ds.Tables("Courses").RowChanging, New DataRowChangeEventHandler(AddressOf OnRowChange)
Private Sub OnRowChange(ByVal sender As Object, ByVal e As DataRowChangeEventArgs)
If IsInProgress(e.Row) Then
Throw New Exception("Course Is Already In Progress")
End If
End Sub
Private Function IsInProgress(ByVal row As DataRow) As Boolean
'code to determine if the current Course is in progress
End Function
Here’s the funny thing: To cancel the editing, I need to throw an exception. This exception will be caught by the DataGrid, and shown to the user. What ever text you write in the exception message will be displayed to the user in a message box followed by a question whether they would like to cancel or edit the new value.
By the way, this event can also be used to discover new rows. You can also register to the ‘RowDeleting” event of the DataTable to receive notification of a row’s deletion, but you cannot cancel that action from within the event. This is a bit more complicated, and is explained in the next lesson.
You can use the DataRowChangeEventArgs.Action property to determine what action was taken for this even to occur; Deleted, Changed, Added and so on…
Lesson 3: Reacting to and canceling illegal Row Deletion
OK. This part is a little trickier than before, since you can’t just throw an exception which will be handled by the DataGrid. Nope. For some reason you have to go through some hoops to create this kind of functionality yourself.
The first trick to understanding how to accomplish this is understanding how the DataGrid displays its data. You’ll notice that the DataGrid does not have any properties such as “AllowDelete” or “AllowAddNew”. Infact, you can’t control how your users will interact with the grid using any of the grid’s properties(except “Enabled”). The trick is, that the DataGrid is actually basing its interactivity level on an underlying DataView object to which it is bound. If you’ll look at a DataView object, you’ll see that it does contain this kind of properties. So, setting “AllowDelete” on the DataGrid’s underlying DataView object to false will disable delete functionality from the Grid.
Now, the technique becomes pretty simple: We disable the “AllowDelete” property of the DataView, and catch the KeyDown event of the DataGrid. If the KeyCode is that of the Keys.Delete key, we enable deletion on the DataView, delete the current DataRow, and disable deletion again until next time.
Here’s the code:
Private Sub grd_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles grd.KeyDown
If e.KeyCode = Keys.Delete Then
'check whether the current DataRow Course
'is in Progress and cancel deletion if it is
If IsInProgress(CType(GetGridCurrency().Current, DataRowView).Row) Then
MsgBox("Already in progress")
Else
'Delete the current row
EnableDelete(True)
CType(GetGridCurrency().Current, DataRowView).Delete()
EnableDelete(False)
End If
End If
End Sub
Private Function GetGridCurrency() As CurrencyManager
'return the currency manager associated with the DataGrid
Dim cm As CurrencyManager = BindingContext(grd.DataSource, grd.DataMember)
Return cm
End Function
Private Sub EnableDelete(ByVal value As Boolean)
'enable or disable AllowDelete on the
'DataGrid() 's underlying DataView
Dim cm As CurrencyManager = GetGridCurrency()
Dim view As DataView = CType(cm.List, DataView)
view.AllowDelete = value
End Sub
Notice that What I’m actually Deleting is a DataRowView object, not a DataRow. This is because just like a DataTable had DataRows, a DataView as DataRowViews, corresponding to each DataTable row. Each DataRowView holds a property reference to the DataRow associated with it.Performing actions on a DataRowView is just like performing them on a DataRow object.
That’s it. There’s no way around this that I could find… I’ve seen one other way to accomplish this functionality – by deriving a custom grid from the DataGrid, and pre-processing WM_KEYDOWN messages before they arrive *shudder*.
Lesson 4: How to make a Non-DataGrid control act as the Master Table
(or: How to get the desired position in a BindingContext, when all you have to go on is a DataRow object)
This problem would occur whenever you want a control other than a DataGrid to serve as a 'master' in a master-details data bound form. Imagine that the control that you have is a treeview. You filled up the treeNodes and set the node.Tag property with a DataRow object . You want the user to be able to click on a tree node and let a DataGrid on the form show the child rows of the selected DataRow.
Now, If we have a ListBox or ComboBox control, this could be easy. You just set the BindingManager's Position property to the current index of the control. But what about a treeview? Where is the $#%! index there?
The solution is pretty darn simple: using a DataView object.
A DataView object has the ability to find a row based on a key and a value, meaning that if the DataRow is sorted according to the primary key of the table, you can use it too look up a row based on a given primary key. It contains a Find method which, how comfortable, return the index to the row that was found. so, all you have to do is create a new DataView object, make it sort on your PK, and use it to find and index. Then you set the Binding manager's postion according to the returned index. Yu will need to bind your controls to that particular DataView, so you'll get the correct position(no biggie). One word of caution - a DataTable object has by default a DefaultDataView property which you can use.
It is recommended to use a new DataView object, which gives you more flexibilty, and hey, you can create 2 or even 10 different view of your data. You can sort them, you can filter them, hell, you can just show the rows that were last changed,added,deleted - you name it. It's all there using the DataView.
Here's the code to handle the user selecting a tree node:
//Create a custom view of the data
private DataView view = new DataView(m_ds.Tables["Stuff"]);
//make it sort based on the PK
view.Sort="ID";
//Handling a listbox event
private void listBox1_SelectedIndexChanged(object sender, System.EventArgs e)
{
//get the binding manager to set the position
BindingManagerBase bind = BindingContext[m_ds,"Stuff"];
//Using the 'Find' of the DataView
//returns the needed row index!
//Just make sure you bind the ValueMember
//property of the listbox
bind.Position= view.Find(listBox1.SelectedValue);
}
//Handling a TreeView event
private void treeView1_AfterSelect(object sender, System.Windows.Forms.TreeViewEventArgs e)
{
//get the binding manager to set the position
BindingManagerBase bind = BindingContext[m_ds,"Stuff"];
DataRow row = (DataRow)treeView1.SelectedItem.Tag;
int wantedID = int.Parse(row["ID"].ToString());
bind.Position= view.Find(wantedID);
}