Understanding DataViews


Understanding DataViews

A DataView represents a particular view on a DataTable . You can use a DataView to display a filtered and sorted view of the rows in a DataTable . You also can use a DataView to find rows that have certain column values in a DataTable .

You can create a new DataView from an existing DataTable by passing a DataTable to the DataView 's constructor like this:

 
 dvwDataView = New DataView( dtblDataTable ) 

Alternatively, you can create a new DataView by calling the DefaultView() method of the DataTable class like this:

 
 dvwDataView = dtblDataTable.DefaultView() 

Which method you use to create a DataView is entirely a matter of preference.

ADVANCED NOTE

Technically, you can bind only a DataView , not a DataSet or DataTable , to a control. Thestatement

 
 dgrdDataGrid.DataSource = dstDataSet 

is actually shorthand for

 
 dgrdDataGrid.DataSource = dstDataSet.Tables( 0 ).DefaultView() 

There is no disadvantage to using the shorthand syntax; it works just as well.


Filtering Rows in a DataView

You can filter rows in a DataTable by creating a DataView for it and setting the DataView 's RowFilter property.

The page in Listing 12.17 contains a simple example of filtering rows. The page displays only the products from the Products database table that have a CategoryID of 4 .

Listing 12.17 SimpleRowFilter.aspx
 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load   Dim dstNorthwind As DataSet   Dim conNorthwind As SqlConnection   Dim dadProducts As SqlDataAdapter   Dim dvwProducts As DataView   dstNorthwind = New DataSet()   conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )   dadProducts = New SqlDataAdapter( "Select * From Products", conNorthwind )   dadProducts.Fill( dstNorthwind, "Products" )   dvwProducts = dstNorthwind.Tables( "Products" ).DefaultView   dvwProducts.RowFilter = "CategoryID=4"   dgrdProducts.DataSource = dvwProducts   dgrdProducts.DataBind() End Sub </Script> <html> <head><title>SimpleRowFilter.aspx</title></head> <body> <asp:DataGrid   ID="dgrdProducts"   Runat="Server" /> </body> </html> 

The C# version of this code can be found on the CD-ROM.

In Listing 12.17, a DataView is created from the Products database table. The expression "CategoryID=4" is assigned to the RowFilter property. When the DataView is bound to the DataGrid , only those products with a CategoryID of 4 are displayed.

A more complicated example of using the RowFilter property is included in Listing 12.18. In this example, a RowFilter displays all the products for a particular category. The list of categories is displayed by a Repeater control. The list of products for each category is listed by a DataGrid control contained in the ItemTemplate of the Repeater control (see Figure 12.9).

Listing 12.18 NestedRowFilter.aspx
 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Dim dstNorthwind As DataSet Dim dvwProducts As DataView Sub Page_Load   Dim conNorthwind As SqlConnection   Dim dadNorthwind As SqlDataAdapter   dstNorthwind = New DataSet()   conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )   dadNorthwind = New SqlDataAdapter( "Select * From Categories", conNorthwind )   dadNorthwind.Fill( dstNorthwind, "Categories" )   dadNorthwind.SelectCommand = New SqlCommand( "Select * From Products", conNorthwind )   dadNorthwind.Fill( dstNorthwind, "Products" )   dvwProducts = dstNorthwind.Tables( "Products" ).DefaultView()   rptCategories.DataSource = dstNorthwind   rptCategories.DataMember = "Categories"   rptCategories.DataBind() End Sub Function FilterProducts( intCatID As Integer ) As DataView   dvwProducts.RowFilter = "CategoryID=" & intCatID   Return dvwProducts End Function </Script> <html> <head><title>NestedRowFilter.aspx</title></head> <body> <asp:Repeater   ID="rptCategories"   Runat="Server"> <ItemTemplate> <h2><%# Container.DataItem( "CategoryName" )%></h2> <asp:DataGrid   DataSource='<%# FilterProducts( Container.DataItem( "CategoryID" ) )%>'   Runat="Server" /> </ItemTemplate> </asp:Repeater> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 12.9. Filtering a DataView .

graphics/12fig09.jpg

The Categories table is bound to the Repeater control. The ItemTemplate of the Repeater control displays the value of the CategoryName column. Additionally, the ItemTemplate contains a DataGrid .

The DataGrid displayed by the Repeater control's ItemTemplate displays all the products for a category. A function named FilterProducts() is assigned to the DataGrid 's DataSource property. The CategoryID of the current category is passed to the function.

Within the FilterProducts() function, a DataView that represents the Products table is filtered with the CategoryID passed to the function. The FilterProducts function returns this DataView . The end result is that each DataGrid shows only a certain subset of products.

Sorting Rows in a DataView

You can sort the rows in a DataView by assigning the names of one or more columns to the Sort property of the DataView class. For example, to sort the rows in a DataView in order of price, you would use the following statement:

 
 myDataView.Sort = "Price" 

To sort rows using multiple columns, you need to separate each column name with a comma. For example, to sort rows by author last and first name , you would use the following:

 
 myDataView.Sort = "au_lname, au_fname" 

By default, rows are sorted in ascending order (least to most). You can use the DESC keyword to sort the rows in the opposite order (most to least). For example, to display the most expensive products first, you would use the following statement:

 
 myDataView.Sort = "Price Desc" 

The page in Listing 12.19 illustrates how to use the Sort property with a DataGrid control.

Listing 12.19 SortDataView.aspx
 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Dim dvwProducts As DataView Sub Page_Load   Dim conNorthwind As SqlConnection   Dim dadProducts As SqlDataAdapter   Dim dstProducts As DataSet   ' Create Products DataSet   dstProducts = New DataSet()   conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )   dadProducts = New SqlDataAdapter( "Select * From Products", conNorthwind )   dadProducts.Fill( dstProducts, "Products" )   ' Create default DataView   dvwProducts = dstProducts.Tables( "Products" ).DefaultView()   ' Bind to datagrid   dgrdProducts.DataSource = dstProducts   dgrdProducts.DataBind() End Sub Sub dgrdProducts_SortCommand( s As Object, e As DataGridSortCommandEventArgs )   ' Sort DataView   dvwProducts.Sort = e.SortExpression   ' Rebind to DataGrid   dgrdProducts.DataSource = dvwProducts   dgrdProducts.DataBind() End Sub </Script> <html> <head><title>SortDataView.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid   ID="dgrdProducts"   AllowSorting="True"   OnSortCommand="dgrdProducts_SortCommand"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

When a DataGrid control has its AllowSorting property enabled, all the column headers appear as links. When you click a link, the DataGridSortCommand event is raised and the dgrdProducts_SortCommand subroutine is executed. This subroutine uses the Sort property of a DataView to sort its rows.

Finding Rows in a DataView

You can use a DataView 's Find method to find the index of a row that has a certain value. You can pass a single value to the Find method or an array of values.

To pass a single value to the Find method, simply use the following:

 
 intItemIndex = MyDataView.Find( "Smith" ) 

If Smith is found, the index of the row that contains this name is assigned to intItemIndex .

To pass multiple values to the Find method, use the following:

 
 Dim arrValues( 2 ) As Object arrValues( 0 ) = "Smith" arrValues( 1 ) = "Robert" intItemIndex = dvwDataView.Find( arrValues ) 

If Robert Smith is found, the Find method returns the index of the row. Otherwise, it returns the value -1 (meaning no matching row was found).

If multiple rows match the values being searched, the index of only one of the matched rows is returned.

The Find method requires that the rows in a DataTable are sorted in the same order as the values passed to it. If you want to find an author with a certain last name, for example, the table must be sorted in order of author last names.

The page in Listing 12.20 searches for an author named Ann Dull. If this author's name is found, her phone number is displayed.

Listing 12.20 FindDataView.aspx
 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <% Dim dstAuthors As DataSet Dim conPubs As SqlConnection Dim dadAuthors As SqlDataAdapter Dim dvwAuthors As DataView Dim arrValues(2) As Object Dim intRowIndex As Integer ' Grab Authors Table dstAuthors = New DataSet() conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) dadAuthors = New SqlDataAdapter( "Select * From Authors", conPubs ) dadAuthors.Fill( dstAuthors, "Authors" ) ' Create DataView dvwAuthors = dstAuthors.Tables( "Authors" ).DefaultView() dvwAuthors.Sort = "au_lname, au_fname" ' Find Ann Dull arrValues( 0 ) = "Dull" arrValues( 1 ) = "Ann" intRowIndex = dvwAuthors.Find( arrValues ) ' Display Phone Number If intRowIndex <> -1 Then   Response.Write( dvwAuthors( intRowIndex ).Row( "Phone" ) ) Else   Response.Write( "Can't find Ann Dull" ) End If %> 

The C# version of this code can be found on the CD-ROM.

In Listing 12.20, the Authors table is retrieved into a DataSet . Next, a DataView is created from the DataSet and sorted in order of author last and first name.

The Find method searches for an author named Ann Dull. If this name is found (the intRowIndex does not equal -1 ), the author's phone number is displayed. Otherwise, you see a message stating that she can't be found.



ASP.NET Unleashed
ASP.NET 4 Unleashed
ISBN: 0672331128
EAN: 2147483647
Year: 2003
Pages: 263

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net