HOW TO: Extend the Windows Form DataGridTextBoxColumn to Display Data From Other Tables by Using Visual Basic .NET (319082)

SUMMARY

Use this step-by-step guide to extend the Windows Form DataGridTextBoxColumn control so that you can display data from other tables in the DataGrid, effectively joining two or more related DataTables for the purposes of display.

NOTE: This technique is not required if you use individual controls, because you can link them hierarchically.

The sample application later in this article demonstrates how to display fields from the Orders, Employees, and Customers tables in the Northwind database in a Windows Form DataGrid at the same time. The fields from the Orders table are editable; the fields from the related reference tables are not. You can select a new EmployeeID or CustomerID to change the reference table fields automatically to reflect the new value.

The JoinTextBoxColumn class must be mapped to a field in the many-side table (in this case, Orders), even though it displays a field from the reference table. The actual field does not matter. However, you cannot bind more than one column style to a field in the underlying DataView. If you run out of fields, you can add some dummy fields to the DataTable and bind to those, as demonstrated in the Form.Load code in the sample application.

back to the top

Sample Application

1. Start Visual Studio .NET and create a new Visual Basic Windows Form application.
2. Add a reference to System.Data.SQLClient at the top of the Form1.vb file:

   Imports System.Data.SqlClient
   					

3. Add the following DataSet variable declaration at the Form1 class level:

   Private ds As DataSet
   					

4. Add a DataGrid control to the form.
5. Double-click the form background. Type or paste the following code to add a Form1_Load event handler to generate sample data and to initialize the DataGrid to use the JoinTextBoxColumn class.

       '
       ' Read the data and set relationships between the tables.
       '
       Dim cn As New SqlConnection("Server=localhost;uid=myuser;pwd=mypassword;Database=Northwind")
       Dim daCust As New SqlDataAdapter("SELECT * FROM Customers", cn)
       Dim daEmp As New SqlDataAdapter("SELECT * FROM Employees", cn)
       Dim daOrd As New SqlDataAdapter("SELECT * FROM Orders WHERE CustomerID LIKE 'B%'", cn)
       ds = New DataSet()
       daCust.Fill(ds, "Cust")
       daEmp.Fill(ds, "Emp")
       daOrd.Fill(ds, "Ord")
       ds.Relations.Add("CustOrd", ds.Tables!Cust.Columns!CustomerID, ds.Tables!Ord.Columns!CustomerID)
       ds.Relations.Add("EmpOrd", ds.Tables!Emp.Columns!EmployeeID, ds.Tables!Ord.Columns!EmployeeID)
   
       '
       ' Add dummy fields for mapping the JoinTextBoxColumn styles.
       ' Do this only if you are mapping more fields than
       ' there are in the underlying DataTable. You can map the 
       ' JoinTextBoxColumn class to any column because it references
       ' fields in another table.
       '
       With ds.Tables!Ord
         .Columns.Add("FirstName", GetType(String))
         .Columns.Add("LastName", GetType(String))
         .Columns.Add("CompanyName", GetType(String))
         .Columns.Add("ContactName", GetType(String))
       End With
   
       '
       ' Map fields from all three tables. Use a standard
       ' DataGridTextBoxColumn style for base table fields because they can
       ' be updated. Use the JoinTextBoxColumn style for reference table
       ' fields. These are read-only.
       '
       Dim ts As New DataGridTableStyle()
       Dim cs As DataGridColumnStyle
   
       cs = New DataGridTextBoxColumn()
       cs.Width = 75
       cs.MappingName = "OrderID"
       cs.HeaderText = "Order ID"
       ts.GridColumnStyles.Add(cs)
   
       cs = New DataGridTextBoxColumn()
       cs.Width = 75
       cs.MappingName = "EmployeeID"
       cs.HeaderText = "Emp ID"
       ts.GridColumnStyles.Add(cs)
   
       cs = New JoinTextBoxColumn("EmpOrd", ds.Tables!Emp.Columns!FirstName)
       cs.Width = 120
       cs.MappingName = "FirstName"
       cs.HeaderText = "First Name"
       ts.GridColumnStyles.Add(cs)
   
       cs = New JoinTextBoxColumn("EmpOrd", ds.Tables!Emp.Columns!LastName)
       cs.Width = 120
       cs.MappingName = "LastName"
       cs.HeaderText = "Last Name"
       ts.GridColumnStyles.Add(cs)
   
       cs = New DataGridTextBoxColumn()
       cs.Width = 75
       cs.MappingName = "CustomerID"
       cs.HeaderText = "Cust ID"
       ts.GridColumnStyles.Add(cs)
   
       cs = New JoinTextBoxColumn("CustOrd", ds.Tables!Cust.Columns!CompanyName)
       cs.Width = 120
       cs.MappingName = "CompanyName"
       cs.HeaderText = "Company"
       ts.GridColumnStyles.Add(cs)
   
       cs = New JoinTextBoxColumn("CustOrd", ds.Tables!Cust.Columns!ContactName)
       cs.Width = 120
       cs.MappingName = "ContactName"
       cs.HeaderText = "Contact"
       ts.GridColumnStyles.Add(cs)
   
       cs = New DataGridTextBoxColumn()
       cs.Width = 75
       cs.MappingName = "OrderDate"
       cs.HeaderText = "Ord. Date"
       ts.GridColumnStyles.Add(cs)
   
       ts.MappingName = "Ord"
       DataGrid1.TableStyles.Add(ts)
   
       DataGrid1.DataSource = ds
       DataGrid1.DataMember = "Ord"
   
   					

6. Change the connection string that is being passed to the SqlConnection constructor so that it contains the correct server name, username, and password for your instance of SQL Server.
7. Add the following class definition to the Form1.cs file. You can place it inside the Form1 class definition or outside the form definition. This code contains the JoinTextBoxColumn class that extends the DataGridTextBoxColumn class.

   Class JoinTextBoxColumn
     Inherits DataGridTextBoxColumn
   
     '
     ' Store the information to get to a field in the referenced table.
     '
     Private m_RelationName As String
     Private m_ParentField As DataColumn
   
     Public Sub New(ByVal RelationName As String, ByVal ParentField As DataColumn)
       m_RelationName = RelationName
       m_ParentField = ParentField
       MyBase.ReadOnly = True    ' this column's base style is read only
     End Sub
   
     Protected Overrides Function GetColumnValueAtRow(ByVal cm As CurrencyManager, ByVal RowNum As Integer) As Object
       '
       ' Get the current DataRow from the CurrencyManager.
       ' Use the GetParentRow and the DataRelation name to get the parent row.
       ' Return the field value from the parent row.
       '
       Try
         Dim dr As DataRow = CType(cm.List, DataView).Item(RowNum).Row
         Dim drParent As DataRow = dr.GetParentRow(m_RelationName)
         Return drParent(m_ParentField).ToString()
       Catch
         Return "" ' handles NullReferenceException case when adding record
       End Try
     End Function
   
     Protected Overrides Function Commit(ByVal cm As CurrencyManager, ByVal RowNum As Integer) As Boolean
       '
       ' Dummy implementation because it is read-only.
       '
       Return False
     End Function
   
     Public Shadows ReadOnly Property [ReadOnly]() As Boolean
     '
     ' Shadow the base property so it cannot be set.
     ' Return TRUE so the DataGrid cannot allow edits.
     '
       Get
         Return True
       End Get
     End Property
   End Class
   					

8. Run the application. The DataGrid has several columns. Besides the OrderID, CustomerID, EmployeeID, and OrderDate columns in the Orders table, the DataGrid contains the FirstName and LastName fields from the Employees table and the CompanyName and ContactName fields from the Customers table.
9. Edit the data (add, delete, modify rows). The fields from the Employees and Customers tables are read-only. If you edit the EmployeeID or CustomerID fields from the Orders table, the referenced fields will reflect the change.

back to the top

Troubleshooting

Some common problems that you may run into when you use this code are:

• If you give the JoinTextBoxColumn object a non-existant DataRelation name, you may receive an exception error when the DataGrid is rendered. The exception error does not identify which line of code caused the error. You can redesign the constructor of the class to accept a DataRelation object so that this type of error can be caught during the initialization phase and to isolate the problem to a specific line of code.
• The column is not displayed unless you set the Width property. The default setting is 0 (hidden).
• The data is not displayed unless you set the MappingName for both the DataGridTableStyle and DataGridColumnStyle objects and assign them to their respective collections. The Mapping name for the JoinTextBoxColumn object can be any field name; the actual field that is displayed is set by the constructor.
• The column heading will be blank if you don't set the HeaderText property.

back to the top