Scrollable GridView

• Download source code - 27.8 KB

Introduction

This article provides a simple reusable cross browser scrollable GridView in a web user control that can be used with any data source.

Background

One of the most common pieces of information displayed on web pages is information from a database. And, the most common web controls used to display database information in a browser are DataGrids and GridViews. However, these controls don't provide any features for scrolling. So, if there are more rows of information than will fit neatly on one page of the browser, the user must scroll down the page to see all of the data or use pagination. Pagination is not a bad idea unless the visitor wishes to download all of the content.

One method commonly used to add scrolling capability is to wrap the GridView declaration inside HTML DIV tags. When you do this, the header of the GridView scrolls along with all of the information being displayed. To resolve this issue, many developers hide the header values in the GridView and then add a separate table above the GridView to show the column headings. This solution works fine in IE based browsers, but Mozilla based browsers render the tables differently horizontally. As a consequence, the developer must make cumbersome adjustments to the header table so that the table and the GridView align correctly in all browsers. Additionally, if the data changes as a result of new application specifications, the developer must manually change the header table separately. This article shows how a simple web user control can be created to display rows of information with scrollbars.

Using the code

Requirements

• Visual Studio 2005
• A database like SQL Server 2005 Express
• Internet Information Services 6.0

Example

An application developer is asked to display a customer contact list in a web page. The list has both domestic and international customers.

The database schema looks like this:

The fields desired on the web page are:

• Name 
• Postal Code
• Street Address
• Email Address
• City
• Phone Number and Extension (if one is provided)
• State or Country
• First Name
• Last Name

The database never has more than 2000 customers at one time. Use a common table expression and a Union statement to combine the domestic addresses and the international addresses into one recordset. Then, use a Stored Procedure with no input parameters to return the entire customer list.

An application developer sometimes writes their Select statement like this:

'Using a common table expression to combine the domestic and international addresses
With Addresses as (
   select DomesticAddressID as D_ID, 0 as I_ID,  StAddress, 
          City, State, PostalCode  from DomesticAddresses
   Union

   Select 0 as D_ID, InternationaAddressID as I_ID, StAddress, 
               City, Country as [State], PostalCode from InternationalAddresses
)

'retrieving the columns from the three tables - customers, Contact Information and Addresses
Select * From Customers c Inner Join ContactInformation ci on 
                        c.ContactInformationID = ci.ContactInformation
                        Inner Join addresses on 
                        c.DomesticAddress = addresses.D_ID and 
                        c.internationalAddress = addresses.I_ID

Note: The default value for InternationalAddressID is 0 when the customer is domestic, and vise versa when the customer is international.

Then, in the VB code, the appropriate column headings are added to a detached table, and non-essential columns are removed.

A better method is to include all of the appropriate column names in the Stored Procedure. Like this:

With Addresses as (
select DomesticAddressID as D_ID, 0 as I_ID,  StAddress as [Street Address], 
       City, State as [Country or State], PostalCode as [Postal Code]  
       from DomesticAddresses
Union
Select 0 as D_ID, InternationaAddressID as I_ID, StAddress as [Street Address], 
       City, Country as [Country or State], 
       PostalCode as [Postal Code] from InternationalAddresses
)
 
Select Name, a.[Street Address], a.City, a.[Country or State], a.[Postal Code], 
             ci.email as [Email Address], ci.phone as [Phone Number], 
             ISNULL(ci.extension  '') AS Extension, 
             ci.fname as [First Name], ci.lname as [Last Name]
From
Customers c Inner Join ContactInformation 
          ci on c.ContactInformationID = ci.ContactInformationID
Inner Join addresses a on c.DomesticAddressID = a.D_ID and 
                          c.internationalAddressID = a.I_ID

When everything is spelled out in T-SQL, fields can easily be added and / or  removed as requirements change without changing any of the Visual Basic code.

With this Stored Procedure in place, the only requirement in your VB code is to execute the Stored Procedure in a command object and bind the returned recordset to a GridView.

Web User Control

Provided below is a picture of the user control. It has two GridViews on it, a header GridView and a body GridView. The header GridView doesn't have any rows of data. Its sole purpose is to display the column header text. The body GridView displays the data rows; the value for the ShowHeaderRow property is False, and the RowStyle TextWrap property is True. The body GridView is enclosed within a div container. The div tag style is described in the stylesheet section.

The code-behind class provides subroutines to set up the header GridView and set the column widths for the header GridView and the body GridView.

<%@ Control Language="vb" AutoEventWireup="false"
    CodeBehind="ScrollingGridView.ascx.vb" Inherits="YourNamespace.ScrollingGridView" %>

<asp:GridView ID="GVHeader" runat="server" ForeColor="Black">
</asp:GridView>

<div id="AdjResultsDiv" style="WIDTH: 610px; HEIGHT: 200px">
            <asp:GridView ID="GVBody" runat="server" ShowHeader="False" ForeColor="Black">
            </asp:GridView>
</div>

Public Partial Class ScrollingGridView _
    Inherits System.Web.UI.UserControl
#Region "Private members"
    Private _Recordset As DataSet
    Private _ColWidths As New ArrayList
#End Region
#Region "Properties"
    ''' <summary> Sets the private member _recordset value. </summary>
    ''' <value> A Dataset object.</value>
    ''' <returns> a Dataset object.</returns>
    Public Property Recordset() As DataSet
        Get
            Return _Recordset
        End Get
        Set(ByVal value As DataSet)
            _Recordset = value
        End Set
    End Property

    ''' This property exposes an arraylist that sets
    ''' the width of the columns in the gridview.
    ''' The arraylist length must equal the number
    ''' of fields in the recordset. When no arraylist is
    ''' provided the default widths for each column is applied.
    ''' <summary> Sets the width of columns when
    ''' the default values are inappropriate. </summary>
    ''' <value> Each element in the arraylist represents
    '''          a column width (pixels) in the gridview.</value>
    ''' <returns>An Arraylist</returns>
    ''' <remarks>.</remarks>
    Public Property ColWidths() As ArrayList
        Get
            Return _ColWidths
        End Get
        Set(ByVal value As ArrayList)
            _ColWidths = value
        End Set
    End Property
#End Region
#Region "Page Events"
 
    ''' <summary> When the user control loads into the browser for the first time,
    '''              bind the DataGridview to the recordset property. Then, remove
    '''              all of the rows from the recordset, add an empty row and bind 
    '''              the HeaderGridview to the recordset. </summary>
    Protected Sub Page_Load(ByVal sender As Object, _
              ByVal e As System.EventArgs) Handles Me.Load
        ' When the page loads for the first time bind the gridviews to the recordset.
        If Not Me.IsPostback
                Me.GVData.DataSource = Me._Recordset
                Me.GVData.DataBind()
                ' remove all of the rows from the recordset
                ' before binding to the header gridview.
                Me.RemoveRows(_Recordset)
                ' Add an empty row to the recordset and bind to the HeaderGridview..
                Me.AddHeaderRow(_Recordset)
        End if
    End Sub

    ''' <summary> Sets the column widths for the header. </summary>
    ''' <remarks>The gridview cell index is equal to the _ColWidths index. </remarks>
    Public Sub HRowDataBound(ByVal sender As Object, _
           ByVal e As GridViewRowEventArgs) Handles GVHeader.RowDataBound
        If e.Row.RowType = DataControlRowType.Header Then  'search for a header row
            Dim i As Integer  ' arraylist counter
            ' Set the width of the cells in the header row equal to the colWidth values.
            For i = 0 To _ColWidths.Count - 1
                e.Row.Cells(i).Width = System.Web.UI.WebControls.Unit.Pixel(_ColWidths(i))
            Next
        End If
    End Sub
 
 
    ''' <summary> Sets the column widths for the data. </summary>
    ''' <remarks>The column index is equal to the _ColWidths index. </remarks>
    Public Sub DRowDataBound(ByVal sender As Object, _
           ByVal e As GridViewRowEventArgs) Handles GVBody.RowDataBound
        If e.Row.RowType <> DataControlRowType.Header Then ' search for data rows
            Dim i As Integer     ' arraylist counter
            ' Set the width of the cells in the header row equal to the colWidth values.
            For i = 0 To _ColWidths.Count - 1
                e.Row.Cells(i).Width = System.Web.UI.WebControls.Unit.Pixel(_ColWidths(i))
            Next
        End If
End Sub
#End Region
#Region "Subroutines"
     ''' <summary> Removes all rows from the input dataset. </summary>
    ''' <param name="ds">Dataset with rows in it.</param>
    Private Sub RemoveRows(ByRef ds As DataSet)
        ds.Tables(0).Rows.Clear()
    End Sub

    ''' <summary> By default a gridview is hidden when there are no rows of data.
    '''       So, an empty row is added to the header gridview.. </summary>
    ''' <param name="ds">An empty dataset</param>
    Private Sub AddHeaderRow(ByRef ds As DataSet)
        Dim rcount As Integer = ds.Tables(0).Rows.Count ' store number of rows
        If (rcount = 0) Then
            ' add a new row.
            ds.Tables(0).Rows.Add(ds.Tables(0).NewRow())
            ' sets the datasource
            Me.GVHeader.DataSource = ds.Tables(0)
            ' binds the datasource
            Me.GVHeader.DataBind()
            'store the number of columns.
            Dim colCount As Integer = Me.GVHeader.Rows(0).Cells.Count
            ' remove any text from the cells
            Me.GVHeader.Rows(0).Cells.Clear()
            Me.GVHeader.Rows(0).Cells.Add(New TableCell()) ' add a new cell 
            'make the cell span across entire table
            Me.GVHeader.Rows(0).Cells(0).ColumnSpan = colCount
            Me.GVHeader.Rows(0).Cells(0).Text = String.Empty ' set the text value
        End If
    End Sub
#End Region

Here's the stylesheet syntax:

/* Scrolling gridview */
div#AdjResultsDiv { 
    width: 1080px; 
    height: 500px; 
    overflow: scroll; 
    position: relative; 
}

div#AdjResultsDiv th 
{
    background-color:Navy;
    cursor: default;
    top: expression(document.getElementById("AdjResultsDiv").scrollTop-2); 
    left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft); 
    position: relative; 
    z-index: 20;
    color:white;
}

div#AdjResultsDiv tbody
{
    margin:auto;
}

Here's how it's implemented in an ASPX file:

To use this control, create a new web form (ASPX file) in Visual Studio. Add the web user control to the page. Then, add the style elements to a new or existing stylesheet.

Web Form

The presentation file contains the web user control, and the code-behind file contains the subroutines to retrieve a dataset and populate the web user control public properties: RecordSet and ColWidths.

Web Form ASPX File

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="TestScrollGrid.aspx.vb" 
         Inherits="YourNamespace.TestScrollGrid" %>
<%@ Register Src="UserControls/ScrollingGridView.ascx" 
         TagName="ScrollingGridView" TagPrefix="uc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="TestScrollGrid" runat="server">
    <div>
        <uc1:ScrollingGridView ID="ScrollingGridViewSample" runat="server" />
    </div>
    </form>
</body>
</html>

Code-Behind File

Partial Public Class TestScrollGrid _
    Inherits System.Web.UI.Page
    ''' <summary> When the page loads into the browser for the first ti
    '''              retrieve the data into a dataset, set the user control  recordset
    '''              property equal to the retrieved dataset, and assign values to the 
    '''             user control colwidths arraylist.
    '''</summary>
    Protected Sub Page_Load(ByVal sender As Object, _
                  ByVal e As System.EventArgs) Handles Me.Load
        ' get some data from the database.
        If Not Me.IsPostBack Then
            Dim ds As DataSet
            Dim colwidths As New ArrayList
            Try
                ds = getdata()
                Me.ScrollingGridViewSample.Recordset = ds
                ' add the width of each datarow column.
                colwidths.Add(100)
                colwidths.Add(500)
                Me.ScrollingGridViewSample.ColWidths = colwidths
            Catch ex as Exception
                'evaluate the error message and display a user friendly message.
            Finally
                ' clean up.
                ds = Nothing
                colwidths = Nothing
            End Try
        End If
    End Sub

    ''' <summary> Retrieves data from a database. </summary>
    ''' <returns> Dataset</returns>
    Private Function GetData() As DataSet
        ' Best practices frowns on writing database commands in a codebehind file.
        ' The code below is for demonstration purposes.
 
        Dim connString as string = _
            "data source=SQLServerName; initial catalog=DatabaseName;" & _
            " persist security info=False; user id=UserID; password= password;"
        'set up the sql connection.
        Dim con As SqlClient.SqlConnection = New SqlConnection(ConnString)

        con.Open()
        Dim cmd As New SqlCommand("dbo.StoredProcName", con)
        cmd.CommandType = CommandType.StoredProcedure

        Dim ds as new dataset 
        Dim da As New SqlClient.SqlDataAdapter

      Try
          da.SelectCommand = cmd
          da.Fill(ds)
          GetData =ds
      Finally
          cmd = Nothing
          da = Nothing
          con.Close()
          con = Nothing
      End Try 
   End Function
End Class

That's all!