< Day Day Up > |
Using the DefaultDataView for Filtering and SortingIn 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 ExampleImports 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 |
< Day Day Up > |