To display the values, we were using all available records from a table specified as a the DataMember of the DataGridView object:

Instead of displaying all values, in some cases you may want to display only some values that respond to a criterion you set. Also, we were displaying the records in the order they appeared in the table. In some cases, you may want to re-arrange the order of records, following an arrangement of your choice. To perform these and other operations, you can use an object called a data view.

A data view is a technique of creating a particular view of the records of a table. In order to use a data view, you must specify a table that the view gets its values from, and then use that data view to specify how the records should be displayed.

To support data views, the .NET Framework provides a class called DataView. Because a data view is a control, there are two ways you can create it: visually or programmatically.

To visually create a data view, in previous versions of Microsoft Visual Studio (2002, 2003, and 2005), a DataView object was available in the Toolbox. It was removed in the 2008 version. Therefore, if you want to use it, you must import it or add it to the Toolbox. To do this, you can right-click the Data section (actually you can right-click any section but the object would be added to the section you right-clicked; since the data view is a database object, it is a better idea to put it in the Data section) of the Toolbox and click Choose Items... In the .NET Framework Component property page, scroll down and put a check mark on DataView:

Click OK. This would add a DataView object to the Toolbox. From there, you can click the DataView button and click the form.

To programmatically create a data view, declare a variable of type DataView and initialize it using one of its three constructors. The default constructor allows you to create a data view without specifying any detail. Here is an example:

Public Class Exercise
    Private dvwStudents As DataView

    Private Sub ExerciseLoad(ByVal sender As Object, 
                              ByVal e As System.EventArgs) 
                              Handles Me.Load
        dvwStudents = New DataView
    End Sub
End Class

In order to play its role, a data view must know the table on which it would apply its operations. Therefore, when creating a data view, the first piece of information you should provide is the table. Of course, you must have previously created a table, such as one from a data set.

If you are visually creating a data view, to specify its table, in the Properties window, click the arrow of the Table field, expand the data set node and select the desired table:

To programmatically specify the table of a data view, you have various (three) options. If you had declared a DataView variable using the default constructor, to specify the table, you can assign the table by its index to the DataView.Table property. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                              ByVal e As System.EventArgs) 
                              Handles Me.Load
    dvwStudents = New DataView

    dvwStudents.Table = dsStudents.Tables(0)
End Sub

You can also use to object name of the variable. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                              ByVal e As System.EventArgs) 
                              Handles Me.Load
    dvwStudents = New DataView

    dvwStudents.Table = dsStudents.Tables("Student")
End Sub

You can also use the variable table of the table. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                              ByVal e As System.EventArgs) 
                              Handles Me.Load
    dvwStudents = New DataView

    dvwStudents.Table = tblStudent
End Sub

When declaring the DataView variable, to specify the table, the DataView class provides the following constructor:

Public Sub New(table As DataTable)

This constructor expects a DataTable object as argument. Here are examples:

Public Class Exercise
    Private dvwStudents As DataView

    Private Sub ExerciseLoad(ByVal sender As Object, 
                              ByVal e As System.EventArgs) 
                              Handles Me.Load
        ' Using the table's index in the data set
        dvwStudents = New DataView(dsStudents.Tables(0))
        ' Using the table object name
        dvwStudents = New DataView(dsStudents.Tables("Student"))
        ' Using the table variable name
        dvwStudents = New DataView(tblStudent)
    End Sub
End Class

We already saw that, to create a data view, you could declare a variable of type DataView. You do not have to formally create a data view. To provide you with an alternative, the DataTable class is equipped with a property named DefaultView. This property produces a data view:

Public ReadOnly Property DefaultView As DataView

This property allows you to access a default directly from a table object.

By default, when you display the records of a table in a control such as a data grid view, the records appear in the order they were created. Sorting records consists of arranging them in an alphabetical, numerical, or chronological order. To support this operation, you can use the DataView class. This class is equipped with a property of type string and named Sort. The DataView.Sort property expects the name of a column. The data view would arrange the records using that column as the basis. Here is an example:

Private Sub rdoStudentNumberCheckedChanged(ByVal sender As System.Object, 
                                            ByVal e As System.EventArgs) 
                                  Handles rdoStudentNumberAsc.CheckedChanged
    dvwStudents.Sort = "StudentNumber"
End Sub

Private Sub rdoFirstNameCheckedChanged(ByVal sender As Object, 
                                        ByVal e As System.EventArgs) 
                                Handles rdoFirstNameAsc.CheckedChanged
    dvwStudents.Sort = "FirstName"
End Sub

Private Sub rdoLastNameCheckedChanged(ByVal sender As Object, 
                                       ByVal e As System.EventArgs) 
                                Handles rdoLastNameAsc.CheckedChanged
    dvwStudents.Sort = "LastName"
End Sub

Private Sub rdoDateOfBirthCheckedChanged(ByVal sender As Object, 
                                          ByVal e As System.EventArgs) 
                                Handles rdoDateOfBirthAsc.CheckedChanged
    dvwStudents.Sort = "DateOfBirth"
End Sub

Private Sub rdoGenderCheckedChanged(ByVal sender As Object, 
                                     ByVal e As System.EventArgs) 
                                Handles rdoGenderAsc.CheckedChanged
    dvwStudents.Sort = "Gender"
End Sub

When it comes to sorting, you can arrange a list in alphabetical or reverse alphabetical order, in numerical or reverse numerical order, in incremental or decremental order, in chronological or reverse chronological order. When sorting in alphabetical, numerical, incremental, in chronological order, you can specify the name of the column as done above or follow that name with the ASC operator. Here is an example:

Private Sub rdoStudentNumberCheckedChanged(ByVal sender As System.Object, 
                                            ByVal e As System.EventArgs) 
                                  Handles rdoStudentNumberAsc.CheckedChanged
    dvwStudents.Sort = "StudentNumber ASC"
End Sub

To sort a list in reverse alphabetical, reverse numerical, decremental, or reverse chronological order, you must specify the name of the column followed by the DESC operator. Here is an example:

Private Sub rdoStudentNumberCheckedChanged(ByVal sender As System.Object, 
                                            ByVal e As System.EventArgs) 
                                  Handles rdoStudentNumberAsc.CheckedChanged
    dvwStudents.Sort = "StudentNumber DESC"
End Sub

These operators are not case-sensitive. This means that ASC, Asc, and asc have the same effect. Here are examples:

Private Sub rdoStudentNumberAscCheckedChanged(ByVal sender As System.Object, 
                                               ByVal e As System.EventArgs) 
                                               Handles rdoStudentNumberAsc.CheckedChanged
    dvwStudents.Sort = "StudentNumber asc"
End Sub

Private Sub rdoFirstNameAscCheckedChanged(ByVal sender As Object, 
                                           ByVal e As System.EventArgs) 
                                           Handles rdoFirstNameAsc.CheckedChanged
    dvwStudents.Sort = "FirstName asc"
End Sub

Private Sub rdoLastNameAscCheckedChanged(ByVal sender As Object, 
                                          ByVal e As System.EventArgs) 
                                          Handles rdoLastNameAsc.CheckedChanged
    dvwStudents.Sort = "LastName asc"
End Sub

Private Sub rdoDateOfBirthAscCheckedChanged(ByVal sender As Object, 
                                             ByVal e As System.EventArgs) 
                                        Handles rdoDateOfBirthAsc.CheckedChanged
    dvwStudents.Sort = "DateOfBirth asc"
End Sub

Private Sub rdoGenderAscCheckedChanged(ByVal sender As Object, 
                                        ByVal e As System.EventArgs) 
                                        Handles rdoGenderAsc.CheckedChanged
    dvwStudents.Sort = "Gender asc"
End Sub

Private Sub rdoStudentNumberDescCheckedChanged(ByVal sender As Object, 
                                                ByVal e As System.EventArgs) 
                                        Handles rdoStudentNumberDesc.CheckedChanged
    dvwStudents.Sort = "StudentNumber desc"
End Sub

Private Sub rdoFirstNameDescCheckedChanged(ByVal sender As Object, 
                                            ByVal e As System.EventArgs) 
                                        Handles rdoFirstNameDesc.CheckedChanged
    dvwStudents.Sort = "FirstName desc"
End Sub

Private Sub rdoLastNameDescCheckedChanged(ByVal sender As Object, 
                                           ByVal e As System.EventArgs) 
                                    Handles rdoLastNameDesc.CheckedChanged
    dvwStudents.Sort = "LastName desc"
End Sub

Private Sub rdoDateOfBirthDescCheckedChanged(ByVal sender As Object, 
                                              ByVal e As System.EventArgs) 
                                  Handles rdoDateOfBirthDesc.CheckedChanged
    dvwStudents.Sort = "DateOfBirth desc"
End Sub

Private Sub rdoGenderDescCheckedChanged(ByVal sender As Object, 
                                         ByVal e As System.EventArgs) 
                                   Handles rdoGenderDesc.CheckedChanged
    dvwStudents.Sort = "Gender desc"
End Sub

Here is an example of running the program:

So far, we specified that the sorting apply to only one column. You can ask the compiler to sort more than one record. To do this, you would put the list of columns in a string, and then assign that string to the DataView.Sort property. Here is an example:

Private Sub btnSortClick(ByVal sender As System.Object, 
                          ByVal e As System.EventArgs) 
                          Handles btnSort.Click
    dvwStudents.Sort = "LastName, FirstName"
End Sub

When asked to arrange the records, the data view would sort the records based on the first column first, then the records would be arranged based on the second column. If you provide only the list of records, they would be arranged in ascending order. To enforce this, you can add the ASC operator on the right side of the list of columns. Here is an example:

Private Sub btnSortClick(ByVal sender As System.Object, 
                          ByVal e As System.EventArgs) 
                          Handles btnSort.Click
    dvwStudents.Sort = "LastName, FirstName, DateOfBirth ASC"
End Sub

To arrange the records in reverse order, add the DESC operator to the list of columns. Here is an example:

Private Sub btnSortClick(ByVal sender As System.Object, 
                          ByVal e As System.EventArgs) 
                          Handles btnSort.Click
    dvwStudents.Sort = "LastName DESC, FirstName"
End Sub

So far, when displaying the records, we were showing all those that existed in the table of our choice. Data filtering consists of showing only some records, based on a condition, called a criterion, or more than one condition, in which case the plural is criteria.

To support filtering, the DataView class is equipped with a property named RowFilter:

Public Overridable Property RowFilter As String

The DataView.RowFilter property is string-based. This means that you must assign a string to it. The string must follow strict rules that the database parser will analyze. The simplest way to create this string is to assign a double-quoted value to the name of a column. An example would be:

LastName = "Simms"

The value to assign to the DataView.RowFilter must be in the form of a string. Since the value assigned to the column name must be stringed itself, you must include it in single-quotes. Therefore, the above string would be assigned as:

Private Sub btnFilterClick(ByVal sender As Object, 
                            ByVal e As System.EventArgs) 
                            Handles btnFilter.Click
    dvwStudents.RowFilter = "LastName = 'Simms'"
End Sub

This would produce:

If the value is a string or a number, you can include it in single-quotes. If the value is a time or a date, you must include it between two pound signs "#" without the single-quotes. Here is an example:

Private Sub btnFilterClick(ByVal sender As Object, 
                            ByVal e As System.EventArgs) 
                            Handles btnFilter.Click
    dvwStudents.RowFilter = "DateOfBirth = #10/10/1994#"
End Sub

This would produce:

Instead of using a simple value, that is, instead of assigning the value of a column to the name of a column, you may want to create an expression that would be used as the criterion. To create an expression, you use some logical operators. Most of the operators are the same you are already familiar with from your knowledge of the Visual Basic language, except as follows:

Here is an example:

Private Sub btnFilterClick(ByVal sender As Object, 
                            ByVal e As System.EventArgs) 
                            Handles btnFilter.Click
    dvwStudents.RowFilter = "DateOfBirth < #10/10/1994#"
End Sub

You can also use the other logical operators, such as the negation operator, the conjunction operator, and the disjunction operator:

 Here is an example:

Private Sub btnFilterClick(ByVal sender As Object, 
                            ByVal e As System.EventArgs) 
                            Handles btnFilter.Click
    dvwStudents.RowFilter = "(LastName = 'Simms') OR (LastName = 'Hamden')"
End Sub

This would produce:

Databases uses other logical operators such LIKE. Here is an example:

Private Sub btnFilterClick(ByVal sender As Object, 
                            ByVal e As System.EventArgs) 
                            Handles btnFilter.Click
    dvwStudents.RowFilter = "LastName LIKE '*on*'"
End Sub

This would produce:

You can also use the IN logical operator. Here is an example:

Private Sub btnFilterClick(ByVal sender As Object, 
                            ByVal e As System.EventArgs) 
                            Handles btnFilter.Click
    dvwStudents.RowFilter = "LastName IN ('Simms', 'Hamden')"
End Sub

This would produce:

Besides the operators, you can also use some of the available functions.

Besides sorting and filtering records, another one of the most valuable actions you can perform using a data view consists of looking for a record. Finding a record consists of isolating one particular record based on a condition that can distinguish the intended record from the other records in the table. You have many options.

Before asking the data view to find a record, you must sort the records using the column by which you will apply the value. Here is an example:

Private Sub btnFindStudentNumberClick(ByVal sender As System.Object, 
                                       ByVal e As System.EventArgs) 
                                       Handles btnFindStudentNumber.Click
    dvwStudents.Sort = "StudentNumber"
End Sub

In previous lessons, we saw how to use the unique properties or the primary key to make sure each record was uniquely identifiable among the other records of the same list. To find a record, you can provide this unique identification to the interpreter. To support this technique, the DataView class is equipped with a method named Find that is overloaded with two versions. One of the versions of the method uses the following syntax:

Public Function Find(key As Object) As Integer

This method takes as argument the value, usually of a primary key column, that can be used to uniquely identify a record. If a record exists with that value, the method returns its index. If there is no record with that value, the method returns -1. Here is an example:

Private Sub btnFindStudentNumberClick(ByVal sender As System.Object, 
                                           ByVal e As System.EventArgs) 
                                           Handles btnFindStudentNumber.Click
        Dim StudentNumber As Long

        If txtStudentNumber.Text.Length = 0 Then
            MsgBox("You must enter the student number you want to look for.")
            Exit Sub
        End If

        Try
            StudentNumber = CLng(txtStudentNumber.Text)
            dvwStudents.Sort = "StudentNumber"
            Dim Index As Integer = dvwStudents.Find(StudentNumber)

            If Index >= 0 Then
                MsgBox("The index of the student is: " & Index.ToString() & ".")
            Else
                MsgBox("Student not found")
            End If
        Catch Exc As FormatException
            MsgBox("Invalid Student Number!")
        End Try
End Sub

Here is an example of running the program:

Instead of using a value from a primary key, if you know a combination of values of different columns of the same record, you can submit that combination to the data view to find a record that contains that combination. As done previously, before performing this operation, you must sort the record. This time, you must sort using the combination, and order, of the columns you will submit to the data view. Here is an example:

Private Sub btnFindFullNameClick(ByVal sender As System.Object, 
                                  ByVal e As System.EventArgs) 
                                  Handles btnFindFullName.Click
    dvwStudents.Sort = "FirstName, LastName"
End Sub

To support the idea of finding a record using a combination of columns, the DataView class is equipped with another version of its Find() method. Its syntax is:

Public Function Find(key As Object()) As Integer

This version of the DataView.Find() method expects as argument an array that holds the combination of values. You create that combination in an array variable and pass that array to the method. The elements of the array must be stored in the same combination the columns were sorted. Here is an example:

Private Sub btnFindFullNameClick(ByVal sender As System.Object, 
                                      ByVal e As System.EventArgs) 
                                      Handles btnFindFullName.Click
        Dim FullName(1) As String

        If txtFirstName.Text.Length = 0 Then
            MsgBox("You must enter the student number you want to look for.")
            Exit Sub
        End If

        Try
            FullName(0) = txtFirstName.Text
            FullName(1) = txtLastName.Text

            dvwStudents.Sort = "FirstName, LastName"
            Dim Index As Integer = dvwStudents.Find(FullName)

            If Index >= 0 Then
                MsgBox("The index of the student is: " & Index.ToString() & ".")
            Else
                MsgBox("Student not found")
            End If
        Catch Exc As FormatException
            MsgBox("Invalid Student Number!")
        End Try
End Sub

This is an example of running the program:

In our introduction, we saw that we could use a data view as an accessory to perform sorting or filtering operations on a table. You can in reverse create a temporary table using the results of a data view. You can create a table, add records to it, delete some records, or get the number of its records. The data view itself supports these minimal operations.

You can use the values of a data view to create a table. To support this operation, the DataView class is equipped with a method named ToTable that is overloaded with various versions. One of the versions uses the following syntax:

Public Function ToTable As DataTable

This method creates a table. Here is an example of calling it:

Private Sub btnCreateTableClick(ByVal sender As Object, 
                                 ByVal e As System.EventArgs) 
                                 Handles btnCreateTable.Click
    Dim tblSimms As DataTable = New DataTable

    dvwStudents.RowFilter = "LastName = 'Simms'"
    tblSimms = dvwStudents.ToTable()

    dgvStudents.DataSource = tblSimms
End Sub

This would produce:

If you create the table as above, it would receive a default name. If you want to specify the name, you can use the following version of the DataView.ToTable() method:

Public Function ToTable(tableName As String) As DataTable

Here is an example:

Private Sub btnCreateTableClick(ByVal sender As Object, 
                                 ByVal e As System.EventArgs) 
                                 Handles btnCreateTable.Click
    Dim tblSimms As DataTable = New DataTable

    dvwStudents.RowFilter = "LastName = 'Simms'"
    tblSimms = dvwStudents.ToTable("StudentsNamedSimms")

    dgvStudents.DataSource = tblSimms
End Sub

As a normal list, there are many operations you can perform on a table created from a data view. In fact, you can use any of the properties and methods we reviewed for tables. Here is an example that adds new records to a table created from a data view:

Private Sub btnCreateTableClick(ByVal sender As Object, 
                                 ByVal e As System.EventArgs) 
                                 Handles btnCreateTable.Click
        Dim tblTempTable As DataTable = New DataTable

        dvwStudents.RowFilter = "LastName = 'Thomas'"
        tblTempTable = dvwStudents.ToTable()

        Dim Record As DataRow = Nothing

        Record = tblTempTable.NewRow()
        Record(0) = "959402"
        Record(1) = "Helene"
        Record(2) = "Mukoko"
        Record(3) = "04/08/1996"
        Record(4) = "Female"
        tblTempTable.Rows.Add(Record)

        Record = tblTempTable.NewRow()
        Record(0) = "297462"
        Record(1) = "Camille"
        Record(2) = "Solis"
        Record(3) = "07/12/2000"
        Record(4) = "Unknown"
        tblTempTable.Rows.Add(Record)

        Record = tblTempTable.NewRow()
        Record(0) = "294729"
        Record(1) = "Florence"
        Record(2) = "Jansen"
        Record(3) = "11/10/1994"
        Record(4) = "Female"
        tblTempTable.Rows.Add(Record)

        Record = tblTempTable.NewRow()
        Record(0) = "826358"
        Record(1) = "Carl"
        Record(2) = "Tonie"
        Record(3) = "06/10/1998"
        Record(4) = "Male"
        tblTempTable.Rows.Add(Record)

        dgvStudents.DataSource = tblTempTable
End Sub

This would produce:

Besides the normal operations you would perform on a table, you can use the data view's own method to perform additional operations. For example, you can allow a user to add new records to a data view. The ability to add new records to a data view is controlled by the Boolean AllowAdd property of the DataView class:

Public Property AllowNew As Boolean

The default value of this property is True, which indicates that new records can be added to the data view. To prevent new records on a data view, set this property to False.

As studied for the records of a table, you can add a new record to a data view. You can also edit an existing record. The ability to edit or deny editing a record of a data view is controlled by the AllowEdit property of the DataView class. This is a Boolean property:

Public Property AllowEdit As Boolean

The default value of this property is True, which indicates that the records of a data view can be changed. To prevent that the records be edited, you can set this property to False.

To edit a record, you can locate one or more of its values using the columns.

To delete a record from a data view, you can call the Delete() method of the DataView class. Its syntax is:

Public Sub Delete(index As Integer)

The ability to delete a record from a data view is controlled by the AllowDelete property of the DataView. This is a Boolean property:

Public Property AllowDelete As Boolean

Its default value is True, which allows the records to be deleted. If you set it to False (programmatically or in the Properties window), no record can be deleted from the data view.