Filter and Sort the DataGrid Using a DataView...

Date: March 2, 2003

The DataView class is a filtering and sorting mechanism. To work, a DataView requires a DataTable. The DataView becomes a specific view of the DataTable and, among other things, can be used to filter and sort the DataTable. The best way to explain a DataView is to see it in action, so we will get right to the code. We will select specific columns of all of the rows of the Northwind Customers table. We will use a DropDownList of letters of the alphabet as a filtering mechanism. In other words although we select all rows in the table, we will be able to filter so that we will see only the CompanyNames beginning with "A" or "E" or whatever. Additionally, we will use the DataView to sort each view by ContactName. First the .aspx file. It is very straight forward. It includes our DropDownList (ddlAlpha) and our DataGrid (dtgCust). The DropDownList will be populated in the code-behind file.

<%@ Page Language="vb" AutoEventWireup="false" Src="DGDataView.aspx.vb" Inherits="DGDataView"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DGDataView</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DropDownList id="ddlAlpha" style="Z-INDEX: 101; LEFT: 52px; POSITION: absolute; TOP: 37px"
          runat="server" AutoPostBack="True"/>
<asp:DataGrid id="dtgCust" style="Z-INDEX: 102; LEFT: 185px; POSITION: absolute; TOP: 37px"
          runat="server"
          BorderColor="#CCCCCC"
          BorderStyle="None"
          BorderWidth="1px"
          BackColor="White"
          CellPadding="3">
  <ItemStyle ForeColor="#000066" />
  <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#006699" />
</asp:DataGrid>
</form>
</body>
</html>

Next for the code-behind file. It too is very basic if you are are populating the DataGrid with a DataSet. Most of the code (until that highlighted in blue) is what you would find in any file using a DataAdapter and a DataSet as the datasource for a DataGrid. Near the top of the code we populate the DropDownList with the letters "A" through "H". I didn't bother to do the entire alphabet. This should be enough to get the point across.

The first line highlighted in blue is where we create a DataTable from the DataSet. This step is necessary as the DataView cannot deal directly with a DataSet, only a DataTable. In the next line we pass the DataTable to the DataView's constructor. We then set the RowFilter. The RowFilter is a very SQL-like statement that might be found in a WHERE clause. Since the SelectedItem.Text property of ddlAlpha defaults to "A" the first time through, our RowFilter will initially read "CompanyName LIKE 'A%'" meaning that all values of the CompanyName column that do not begin with "A" will be filtered out. That is probably saying it backwards but hopefully you get the point. If we were just discussing filtering we could stop here. But the DataView is also capable of sorting so the next line in blue says to sort the (filtered) results on the ContactName column. We then set the DataGrid datasource property to the DataView and bind the grid. When you run the program initially you will see that only rows with CompanyNames beginning with "A" will be displayed and they will be displayed in ContactName order. You may select other letters of the alphabet from the DropDownList to change the CompanyNames displayed, but whatever they are, they will always be displayed in ContactName order.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DGDataView : Inherits System.Web.UI.Page

  Protected WithEvents ddlAlpha As System.Web.UI.WebControls.DropDownList
  Protected WithEvents dtgCust As System.Web.UI.WebControls.DataGrid

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
      With ddlAlpha
        .Items.Add("A")
        .Items.Add("B")
        .Items.Add("C")
        .Items.Add("D")
        .Items.Add("E")
        .Items.Add("F")
        .Items.Add("G")
        .Items.Add("H")
      End With
    End If

    Dim objConn As SqlConnection
    Dim strSql As String

    strSql = "SELECT CompanyName, ContactName, ContactTitle, Phone " _
           & "FROM Customers"
    objConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

    Dim sdaCust As New SqlDataAdapter(strSql, objConn)
    Dim dstCust As New DataSet()

    sdaCust.Fill(dstCust, "Customers")
    Dim dtbCust As DataTable = dstCust.Tables(0)
    Dim dtvCust As New DataView(dtbCust)

    dtvCust.RowFilter = "CompanyName LIKE '" & ddlAlpha.SelectedItem.Text & "%'"
    dtvCust.Sort = "ContactName"
    dtgCust.DataSource = dtvCust
    dtgCust.DataBind()
  End Sub

End Class

You may download the code here.