This article points to a sample JoinView
class that you can download, and then include in your Visual Basic .NET applications. The JoinView
class is similar to the DataView
class that permits you to join two or more DataTable
objects together, and to expose selected fields to the rest of your program, or to the databinding mechanism.
The primary goal of the JoinView
class is to permit you to display fields from multiple tables together in the Windows Form DataGrid
control. If you are not using the DataGrid
control, use the standard hierarchical binding.
class offers the following features:
- You can specify fields from related parent tables. These fields are read-only.
- You can specify a subset of fields. This means that you do not have to build a DataGridTableStyle object to limit the fields that are displayed in the DataGrid control, or change the field order.
- You can specify an alias for fields. By default, the alias is used for the display column. By specifying an alias, you do not have to build a DataGridTableStyle object to have a custom column heading.
- You can sort on any field, including "joined" fields (both ascending and descending.) However, unlike the DataView class, which can sort on multiple fields, the JoinView class can only sort on one field at a time. The AllowSort property permits you to turn off sorting.
- You can specify a RowFilter and Sort on the base DataTable to govern which rows the DataRowView exposes, and to govern their initial sort order. The filter and sort are applied at the time that the JoinView class is constructed, and unlike in the DataView class, they do not actively filter or sort the rows after this point.
- You can search on any field, including "joined" fields. Unlike the DataView class, you can also search on fields other than the sorted field, although this will perform a sequential search. Searching on the sorted field performs a binary search.
- You can add, edit, and delete records. The AllowDelete, AllowEdit, and AllowNew properties permit you to turn off these features.
- You can undo edits and new rows. The JoinViewRow object supports the BeginEdit, CancelEdit, and EndEdit methods. Updates are written back to the base DataTable.
class specifically does not support:
- Sorting on multiple fields.
- Removing a sort after it has been applied. Instead, you must specify a new sort column.
- Dynamic filtering. This means that if you change a field value so that the row no longer fits the search criteria, the row remains in the list. The DataView class, instead, removes the row from the list.
- Editing fields from related tables.
- Hierarchical navigation.
- Notification that another entity has changed the base table. This pertains specifically to deleted rows. The JoinView class does not detect when you delete a row from the base DataTable. It is designed to be the only view on the DataTable.
class has not been tested in a Web Form data binding scenario, although it supports most of the same interfaces as the DataView
class. It also has not been tested with design-time visual data tools.
The sample file contains the following four classes:
back to the top Download the Sample JoinView Class
- FieldInfo: This class stores the FieldName, FieldAlias, Relation, and Type of each field that is exposed by the JoinView class. The FieldName is the name of the field in the base DataTable or related DataTable. The FieldAlias, is the name of the exposed field, and it becomes the default column heading when it is bound to the DataGrid control. When you reference fields, or you search or sort, you must use the FieldAlias. The Relation is the name of the parent DataRelation if the field is located in a related DataTable. This information is used to provide dynamic column generation in the JoinViewRow object.
- JoinView: This class is equivalent to the DataView class and is the main class that is used. The functionality comparison is shown at the beginning of this article. The JoinView class implements a number of interfaces that give it various attributes:
- IList: This interface is implemented so that you can bind JoinViewRow objects in a scrollable list. You can also enumerate and delete rows. Add functionality has been disabled. You must use the AddNew method instead.
- IBindingList: This interface permits you to add rows (through the AddNew method), and sort them by the DataGrid control.
- ITypedList: This interface permits binding consumers to discover the schema for a bindable list, where the properties that are available for binding differ from the public properties of the object to bind to.
- IComparer: This interface provides custom comparison for the internal ArrayList object, and permits you to sort on the selected column.
- JoinViewRow: This is equivalent to the DataRowView object and represents a single row of the view. Fields are accessed by their alias name, which if not explicitly specified, is the same as the field name. The JoinViewRow class implements a number of interfaces that give it various attributes:
- IEditableList: This interface provides row undo functionality through the ESC key.
- ICustomTypedescriptor: This interface provides dynamic type information about the row. It permits the JoinViewRow to provide dynamic property information and custom accessor objects to binding consumers.
- JoinViewRowPropertyDescriptor: This class provides accessor methods for each column of the JoinViewRow. Without this class and the ICustomTypeDescriptor interface that is implemented by the JoinViewRow, binding only occurs to public properties instead of to a dynamically generated field list.
The following file is available for download from the Microsoft Download Center:
Release Date: September 6, 2002
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The JoinView.exe file contains the following files: JoinView.vb (36.2Kb) back to the top Sample Application
This section provides a sample application that creates an instance of the JoinView
class, and binds it to a Windows Form DataGrid
control. This sample uses the Orders, Employees, and Customers tables from the Microsoft SQL Server Northwind sample database. Modify the code to use the database of your choice.
- In Visual Studio .NET, create a new Visual Basic Windows Form application.
- Download the class that is described earlier in this article. Save the file as JoinView.vb.
- On the Project menu, click Add Existing Item to add the file to the project. It appears in the Solution Explorer pane.
- Display the main form (Form1) and add a DataGrid control (DataGrid1) to the form. Anchor the DataGrid to the form boundaries (use the Anchor property in the property pane), or size it appropriately to display eight columns of data.
- At the top of the code window of the form, add the following IMPORTS statements:
Imports System.DataImports System.Data.SqlClient
- Add the following variable declarations:
Private ds As DataSet, jv As JoinView
- Switch back to the form designer and double-click the background of the form to add the Form.Load event handler. Add the following code to the event handler:
'' Fill the DataSet.'Dim cn As New SqlConnection("server=localhost;integrated security=true;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 'A%'", cn)ds = New DataSet()daCust.Fill(ds, "Cust")daEmp.Fill(ds, "Emp")daOrd.Fill(ds, "Ord")'' Establish relations between tables.'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)'' Create a new instance of the <B>JoinView</B> class and bind to the grid.'jv = New JoinView(ds.Tables!Ord, _ "OrderID,CustomerID,EmployeeID,OrderDate,CustOrd.CompanyName Company,CustOrd.ContactName Contact,CustOrd.ContactTitle Position,EmpOrd.FirstName,EmpOrd.LastName", _ "EmployeeID<5", "OrderDate")DataGrid1.DataMember = ""DataGrid1.DataSource = jv
The second parameter of the JoinView
constructor specifies which fields to display, whether they come from related tables (by specifying relationname.fieldname), and specifies an alias, which is a friendly name that is displayed in the DataGrid
control, or it programmatically accesses the column.
Compile and run the application. The form loads with the selected subset of records. You can add, delete, and modify records, and you can also click column headers to sort the DataGrid
. When you modify an EmployeeID or a CustomerID value, the related fields update immediately.NOTE
: Fields from related tables are read-only, and the cell backgrounds are visibly (or noticeably) different in color. back to the top Troubleshooting
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:
Microsoft Certified Partners - https://partner.microsoft.com/global/30000104
Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMSback to the top