Using the DataView Class

Using the DataView Class

The DataView class is a convenient class for presenting a subset of a data table, for filtering, sorting, searching, editing, and browsing data in Windows Forms and Web Forms applications. The easiest way to obtain a data view is to request the default view from a data table. The basic steps are to connect to a data source, fill a data set or data table, and request the default view. Listing 16.4 demonstrates how to obtain a data view and display the data in a data grid. (Refer to Chapters 11 and 12 for more information on updating data, including updating a database by using a data view.)

Listing 16.4 Binding the Default Data View to a Data Grid

 Private Sub Page_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load   Dim Adapter As IDbDataAdapter = _     New OleDbDataAdapter("SELECT * FROM CUSTOMERS", _     Database.GetConnection())     Dim DataSet As DataSet = New DataSet("CUSTOMERS")     Adapter.Fill(DataSet)     DataGrid1.DataSource = DataSet.Tables(0).DefaultView     DataGrid1.DataBind() End Sub 

Listing 16.4 declares an IDbDataAdapter interface and initializes it to an OleDbDataAdapter object by using a literal SQL statement and the Database.GetConnection shared method. (I am reusing the Database class from Listing 16.2.) Next , a DataSet object is created. DataSet , DataTable , and DataView objects are provider-neutral, so there is no need to use an interface here. After the DataSet object is created I use the Adapter instance to fill the DataSet object. The final two statements request the first DataTable object from the DataSet object's Tables collection and the DefaultView object from the DataTable object, then bind the DataView object. The result (Figure 16.3) is precisely the same in this instance as if we had assigned the DataSource property directly to the table.

Figure 16.3. The default data view of all the Northwind customers.

We achieve a different view by sorting or filtering the view, or by creating a custom data view and adding those columns we are interested in showing the user. For example, suppose we want to show the user only the contact information, sorted by company. We can request all the data and switch to a contacts-only view without rehitting the database. (Refer to the section Caching Objects in Chapter 15 for more information.) Additionally, I will move the general code for requesting an Adapter instance to the Database class. Listing 16.5 contains the revised and additional code.

Listing 16.5 Creating a Customized Data View

 1:  Imports System.Data 2:  Imports System.Data.OleDb 3: 4:  Public Class WebForm1 5:    Inherits System.Web.UI.Page 6:    Protected WithEvents Label1 As System.Web.UI.WebControls.Label 7:    Protected WithEvents DataGrid1 As _ 8:      System.Web.UI.WebControls.DataGrid 9: 10:   [ Web Form Designer generated code ] 11: 12:   Private Sub Page_Load(ByVal sender As System.Object, _ 13:     ByVal e As System.EventArgs) Handles MyBase.Load 14: 15:     Dim View As DataView = Contacts 16:     DataGrid1.DataSource = View 17:     Label1.Text = String.Format("Count: {0}", View.Count) 18:     DataGrid1.DataBind() 19: 20:   End Sub 21: 22:   Private Function GetAllCustomers() As DataView 23: 24:     ' Verbose code 25:     Dim Adapter As IDbDataAdapter = _ 26:       New OleDbDataAdapter("SELECT * FROM CUSTOMERS", _ 27:       Database.GetConnection()) 28: 29:     Dim DataSet As DataSet = New DataSet("CUSTOMERS") 30:       Adapter.Fill(DataSet) 31: 32:     Return DataSet.Tables(0).DefaultView 33: 34:   End Function 35: 36: 37:   Private ReadOnly Property Contacts() As DataView 38:   Get 39:     Dim Table As DataTable 40:     Table = Customers.Tables(0).Copy() 41:     Table.Columns.Remove("CustomerID") 42:     Table.Columns.Remove("Address") 43:     Table.Columns.Remove("City") 44:     Table.Columns.Remove("Region") 45:     Table.Columns.Remove("PostalCode") 46:     Contacts = New DataView(Table) 47: 48:     Contacts = New DataView(Table, "", _ 49:       "Country", DataViewRowState.CurrentRows) 50:   End Get 51:   End Property 52: 53:   Private ReadOnly Property Customers() As DataSet 54:   Get 55:     Customers = New DataSet("CUSTOMERS") 56:     Database.GetAdapter("SELECT * FROM CUSTOMERS"). _ 57:     Fill(Customers) 58:   End Get 59:   End Property 60: 61: End Class 

The code represents a single Web form with an HTML table, a data grid, and a label Web control. The data view is bound to the grid (as demonstrated in Listing 16.4). GetAllCustomers represents the code from Listing 16.4. The ReadOnly property Customers (lines 53 through 59) represents a simplified version of GetAllCustomers . I basically took the code in lines 25 through 27 and added a GetAdapter method to the Database class discussed earlier in the chapter. The interesting new code is in lines 37 through 51.

The ReadOnly property Contacts obtains all customers and then creates the custom data view by dropping columns we are not interested in viewing. The new data table is declared in line 39 and initialized in line 30. Lines 41 through 45 remove the columns we don't want, and line 46 creates the data view from our new, customized data table.

Lines 48 through 50 demonstrate how to filter, sort , and indicate the rows we want. The first argument to the DataView constructor indicates the table from which we should create the view. The next argument is a filter. For example, we could have written "Country='Liberia'" to only get Liberian contacts. The third argument indicates that we should sort by country, and the last argument indicates that we want all current rows in the table. I used an empty string for the filter, so the result returns all rows in the Customers table (Figure 16.4).

Figure 16.4. The custom Contacts data view created from Listing 16.5.

We could have created the same view by writing an SQL statement that returned only the columns we wanted and had an ORDER BY clause. (Try it as an exercise if you want.) However, in our implementation we have all of the customers' information and we can create various views without having to go back to the database ( assuming we cache or serialize the data set). Our choice can provide dramatic improvements in the responsiveness of our application by eliminating extra reads from the database.