Grouping data in the ASP.Net GridView
Presenting a grouped data in the standard GridView is really a tough task to get it to a satisfactory level. If you ever worked in Spreadsheets to group the data, you may have seen that the merge functionality gives a very good presentation for the grouped data
Note:
1) As it is already explained in earlier articles several times, I am not going to explain again how to get the data and how to bind the data from database. So please refer to the Asp.net DataControls section or GridView section for that matter.
2) Northwind database is used for samples, this database can be downloaded from MSDN
3) This sample is to explain how to have this merge functionality using row span attribute of the cell. So it may be a tough task to extend this task to get two or more columns to have this merge functionality.
RowSpan and ColSpan in HTML TD
In HTML TD we can use the COL span to spread a single cell to two columns. Likewise we can spread a cell to two rows also. In both the cases the cell where it is going to spread itself should not be exist. Otherwise you will see an additional cell. Keep this in mind before reading further. Without knowing this the source code may be bit harder to understand.
Screen Capture
RowDataBound event for grouping
I have preferred to write the function related to the grouping in the RowDataBound event since the RowDataBound will be fired for each row. So you don’t need to iterate all the rows again if you can finish everything in this event.
Use of Static variables
As I have already decided to write everything into the RowDataBound event, I ended up using few static variables. As the static variables will hold the value during the lifespan of the application, I preferred to use them to share the variables between rows(while iterating through rows in RowDataBound event).
Logic used to Group
Rowspan is used to specify how many rows the first cell has to spread. In addition the cells under the first cell in each row have to be removed. It is that simple. If you see the sample section you will know how I have accomplished this.
Source Code
Markup(ASPX)
<%@ Page Language=
"VB" AutoEventWireup=
"false" CodeFile=
"Default.aspx.vb" Inherits=
"_Default" %>
<!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 id=
"Head1" runat=
"server">
<title>Untitled Page</title>
</head>
<body>
<form id=
"form1" runat=
"server" defaultbutton=
"btnLoad">
<asp:Button runat=
"server" ID=
"btnLoad" Text=
"Load" />
<asp:GridView runat=
"server" AutoGenerateColumns=
"false" ID=
"GridView1">
<Columns>
<asp:BoundField DataField=
"CategoryName" HeaderText=
"CategoryName" />
<asp:BoundField DataField=
"ProductName" HeaderText=
"ProductName" />
<asp:BoundField DataField=
"UnitPrice" HeaderText=
"UnitPrice" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
Code-behind(ASPX.VB)
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Private RowCount As Integer
Private ReadOnly Property ConnectionString() As String
Get
Return "Server=.\SQLEXPRESS;Database=NorthWind;" & _
"Trusted_Connection=True"
End Get
End Property
Private ReadOnly Property Connection() As SqlConnection
Get
Dim ConnectionToFetch As New SqlConnection(ConnectionString)
ConnectionToFetch.Open()
Return ConnectionToFetch
End Get
End Property
Public Function GetData() As DataView
Dim SelectQry = "select CategoryName,ProductName,UnitPrice " & _
" from Products P Inner Join Categories C " & _
" on P.CategoryID=C.CategoryID Order By CategoryName"
Dim SampleSource As New DataSet
Dim SortableView As DataView
Try
Dim SampleDataAdapter = New SqlDataAdapter(SelectQry, _
ConnectionString)
SampleDataAdapter.Fill(SampleSource)
SortableView = SampleSource.Tables(0).DefaultView
RowCount = SampleSource.Tables(0).Rows.Count
Catch ex As Exception
Throw ex
End Try
Return SortableView
End Function
Private Sub LoadGrid()
GridView1.DataSource = GetData()
GridView1.DataBind()
End Sub
Protected Sub btnLoad_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles btnLoad.Click
LoadGrid()
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound
Static RowSpan As Integer
Static PrevItem As String
Static FirstItemInIndex As Integer
If (e.Row.RowIndex = 0) Then
PrevItem = e.Row.Cells(0).Text
FirstItemInIndex = 0
RowSpan = 0
End If
If (e.Row.RowType = DataControlRowType.DataRow) Then
If (PrevItem = e.Row.Cells(0).Text) Then
If (e.Row.RowIndex <> FirstItemInIndex) Then
e.Row.Cells.RemoveAt(0)
End If
RowSpan += 1
Else
Dim FirstCell = GridView1.Rows(FirstItemInIndex).Cells(0)
FirstCell.RowSpan = RowSpan
FirstItemInIndex = e.Row.RowIndex
PrevItem = e.Row.Cells(0).Text
RowSpan = 1
End If
End If
If (e.Row.RowIndex = RowCount - 1) Then
Dim FirstCell = GridView1.Rows(FirstItemInIndex).Cells(0)
FirstCell.RowSpan = RowSpan
End If
End Sub
End Class