Using the DefaultDataView for Filtering and Sorting

Using the DefaultDataView for Filtering and Sorting

In FoxPro, we can use SET ORDER TO TAG IndexTagName to change the sort order of a table or cursor, and SET FILTER TO Expr to limit the records displayed. We can also use LOCATE or SEEK to move the record pointer to a particular record. These commands operate on the current work area, a concept that has no meaning in Visual Basic .NET.

The Visual Basic .NET equivalent is the DataView . The DataView has a Sort property that takes the name of one of the columns in the DataView as its value and changes the display order of the data to the selected column. It also has a RowFilter property to which we can assign an expression like State = 'CA' . Only records matching the filter expression will be displayed.

A DataView can be created from a datatable object using the following assignment:

 Dim dv as dataview = Dataset.Tables(0).DefaultView 

However, you can also reference the DefaultView directly. Figure 9.6 shows a form I've built to demonstrate how these features work. In the related code shown in Listing 9.5, the DefaultView of the first table (table 0) in the dataset is the object containing the data displayed in the grid. In my example, you have to type a filter expression that assumes you know the exact spelling of the field name, and the character expression must use single quotes as delimiters. Obviously, you'd want to build this expression in code using the contents of a combo box to select the column name and a text box for the string to search for. If you run the sample code, which is located in the Chapter9\VB\SearchAndFilter directory, note that the code looks for an exact match for the name entered in the Search For text box, so if you enter only a first name, the program won't find the record.

Figure 9.6. A sort and filter example using the DefaultDataView .

Listing 9.5. Code for the DefaultDataView Example

 Imports System.Data.SqlClient Public Class Form1     Inherits System.Windows.Forms.Form     Dim cn As SqlConnection     Dim da As SqlDataAdapter     Dim ds As New DataSet + Windows Forms Designer Generated Code omitted     Private Sub Form1_Load( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs) _      Handles MyBase.Load         cn = New SqlConnection( _         "Server=localhost;Database=Northwind;" _         + "UID=sa;PWD=;")         da = New SqlDataAdapter( _         "SELECT * FROM CUSTOMERS", cn)         da.Fill(ds)         DataGrid1.DataSource = ds.Tables(0)         ds.Tables(0).DefaultView.Sort = "ContactName"         Me.Text = "The date is " _          + Strings.FormatDateTime(Today.Now, DateFormat.ShortDate)     End Sub     Private Sub btnFind_Click_1( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs) _      Handles btnFind.Click         Dim s As String = TextBox1.Text.Trim         BindingContext(ds.Tables(0)).Position = _          ds.Tables(0).DefaultView.Find(s)     End Sub     Private Sub ComboBox1_SelectedIndexChanged( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs) _      Handles ComboBox1.SelectedIndexChanged         ds.Tables(0).DefaultView.Sort = _         ComboBox1.SelectedItem.trim     End Sub     Private Sub Button1_Click( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs) _      Handles Button1.Click         Dim s As String = TextBox2.Text.Trim         ds.Tables(0).DefaultView.RowFilter = s     End Sub End Class