SUMMARY
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.
The
JoinView 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.
The
JoinView 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.
The
JoinView 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:
- 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.
back to the top
Download the Sample JoinView Class
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:
119591 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.Data
Imports 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 the Microsoft fee-based
consulting line at (800) 936-5200. For more information about Microsoft
Certified Partners, please visit the following Microsoft Web site:
For more information about the support options that are available
and about how to contact Microsoft, visit the following Microsoft Web site:
back to the top
