Jan 11, 2011

Custom Paging in GridView in ASP.Net

In this article, you will learn what is Custom Paging and how it is implemented in a Grid-view.

First of all, I would like to share the benefit of using Custom Paging over Normal Paging.

The purpose for using Custom Paging over Normal Paging is performance. The key point in Custom Paging is that it only retrieves "n" no. of records from the database that are to be displayed on the page.

Suppose, there are 100K records in the database and you want to display 10 records on a page. In Normal Paging on each page change, all 100K records are retrieved from the database or is maintained in the viewstate, but in Custom Paging we implement it in such a way that it retrieves only 10 records from database on each page change.

Let's have an example code to show how custom paging is implemented

Suppose we have a Employee Table and we are going to bind employee details to Grid-view.
The code has below listed functionalities for navigation of pages
  • First, Previous, Next & Last Buttons to navigate between pages
  • Link Buttons to navigate between pages
First create a Table and populate some data in it.
CREATE TABLE dbo.tblEmployee(
    EmpID    char(8),
    EmpName varchar(50),
    DOB        datetime,
    DOJ        datetime,
    Gender    char(1)
)
INSERT INTO tblEmployee
SELECT    'EMP' + RIGHT('00000' + cast(number as varchar),4)
        , 'EMP' + RIGHT('00000' + cast(number as varchar),4)       
        , CONVERT(VARCHAR, DATEADD(D, (-10000 + ABS(CHECKSUM(NEWID()))%1000), GETDATE()),101)
        , CONVERT(VARCHAR, DATEADD(D, (-1000 + ABS(CHECKSUM(NEWID()))%1000), GETDATE()),101)
        , CASE WHEN ABS(CHECKSUM(NEWID()))%100 > 50 THEN 'M' ELSE 'F' END       
FROM    master..spt_values
WHERE   type='P'
AND     number between 1 and 35
Now let's create a stored procedure that would retrieve only 10 records on records based on the selected page. We have used Row_Number (window function) to generate a sequential no. to the result and based on the which we would fetch the records.

Stored Procedure returns employee details based on current page
CREATE PROCEDURE [dbo].[uspGetEmployees] (
    @iPageNo INT
    , @iPageRecords INT
)
AS
BEGIN   
    DECLARE @ROWCNT INT, @PAGECNT INT
    SELECT EmpID, EmpName, DOB, DOJ, Gender
    FROM (
        SELECT  ROW_NUMBER() OVER(ORDER BY EmpID)  AS RowNum
                , EmpID, EmpName
                , CONVERT(VARCHAR, DOB, 101) AS DOB
                , CONVERT(VARCHAR, DOJ, 101) AS DOJ
                , CASE Gender WHEN 'M' THEN 'Male' ELSE 'Female' END as Gender
        FROM    tblEmployee
    ) TAB WHERE RowNum BETWEEN @iPageRecords * (@iPageNo - 1 ) + 1 AND @iPageNo * @iPageRecords
 
    SELECT @ROWCNT = COUNT(1) FROM tblEmployee
    SELECT @ROWCNT/@iPageRecords + CASE WHEN @ROWCNT%@iPageRecords = 0 THEN 0 ELSE 1 END
END
Now let's add a Page to the ASP.Net application

CustomPaging.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CustomPaging.aspx.cs" Inherits="CustomPaging" %>
<!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>Custom Paging</title>
    <style type="text/css">
        tr{
            background-color: ButtonFace;
        }
        td{
            font-family:Verdana;
            font-size:12px;
        }
    </style>
</head>

<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <table style="width: 100%; border-color: Gray;">
                <tr align="center">
                    <td colspan="2">
                        Employees Details
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
                            ForeColor="#333333" GridLines="Horizontal" Width="100%">
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            <Columns>
                                <asp:BoundField HeaderText="Employee ID" DataField="EmpID" />
                                <asp:BoundField HeaderText="Employee Name" DataField="EmpName" />
                                <asp:BoundField HeaderText="Date of Birth" DataField="DOB" />
                                <asp:BoundField HeaderText="Date of Joining" DataField="DOJ" />
                                <asp:BoundField HeaderText="Gender" DataField="Gender" />
                            </Columns>
                            <EditRowStyle BackColor="#999999" />
                            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                            <SortedAscendingCellStyle BackColor="#E9E7E2" />
                            <SortedAscendingHeaderStyle BackColor="#506C8C" />
                            <SortedDescendingCellStyle BackColor="#FFFDF8" />
                            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                        </asp:GridView>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Button ID="btnFirst" runat="server" Text="<<" OnClick="btnFirst_Click"/>
                        <asp:Button ID="btnPrev" runat="server" Text="<" OnClick="btnPrev_Click" />
                        Page
                        <asp:TextBox ID="txtPageNo" runat="server" Text="1" Width="45px"></asp:TextBox>
                        of
                        <asp:Label ID="lblPages" runat="server" Text="1"></asp:Label>
                        <asp:Button ID="btnNext" runat="server" Text=">" OnClick="btnNext_Click" />
                        <asp:Button ID="btnLast" runat="server" Text=">>" OnClick="btnLast_Click"/>
                        <asp:RequiredFieldValidator ID="rfvPageNo" runat="server" ControlToValidate="txtPageNo"
                            ErrorMessage="*" ValidationGroup="grpGo">
                        </asp:RequiredFieldValidator>
                        <asp:RangeValidator ID="rvPageNo" runat="server" ControlToValidate="txtPageNo" Type="Integer"
                            MinimumValue="1" MaximumValue="1" ValidationGroup="grpGo"></asp:RangeValidator>                       
                    </td>
                    <td align="right">
                        <asp:Label ID="lblStatus" runat="server"> Displaying 1 of 1</asp:Label>
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:Panel ID="pnl" runat="server"></asp:Panel>
                    </td>
                </tr>
            </table>
        </ContentTemplate>
    </asp:UpdatePanel>
    </form>
</body>
</html>
CustomPaging.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class CustomPaging : System.Web.UI.Page
{
    enum PageNav { First, Previous, Next, Last, None}
    private int iPageRecords;

    #region Events
    protected void Page_Load(object sender, EventArgs e)
    {
        iPageRecords = 10;
        if (!IsPostBack)
        {
            BindGrid(1, iPageRecords);
        }
        generateLinkBtns();
    }

    protected void lnkButton_Click(object sender, EventArgs e)
    {
        ViewState["currentPage"] = ((LinkButton)sender).Text;
        BindGrid(Convert.ToInt32(ViewState["currentPage"]), iPageRecords);
        txtPageNo.Text = ViewState["currentPage"].ToString();
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.None);
    } 

    protected void btnFirst_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.First);
    }

    protected void btnPrev_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Previous);
    }

    protected void btnNext_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Next);
    }

    protected void btnLast_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Last);
    }
    #endregion

    #region Methods
    string GetConnectionString()
    {
        //Mofify connection string       
        return "Data Source=SERVERNAME;Initial Catalog=DBNAME;User Id=UserName;Password=Pwd; Connect Timeout=0";       
    }

    private void generateLinkBtns()
    {
        LinkButton lnkButton = null;
        Label lbl = null;
        for (int i = 1; i <= Convert.ToInt32(ViewState["PageCount"]); i++)
        {
            lnkButton = new LinkButton();
            lnkButton.Click += new EventHandler(lnkButton_Click);
            lnkButton.ID = "lnk" + i.ToString();
            lnkButton.Text = i.ToString();
            pnl.Controls.Add(lnkButton);
            lbl = new Label();
            lbl.Text = " ";
            pnl.Controls.Add(lbl);
        }
    }

    private void BindGrid(int iPageNo, int iPageRecords)
    {
        DataSet ds = new DataSet();
        ds = getEmployees(iPageNo, iPageRecords);
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
        if (!IsPostBack)
        {           
            int iPageCount;
            iPageCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);           
            rvPageNo.MaximumValue = iPageCount.ToString();
            ViewState["PageCount"] = iPageCount;           
            btnFirst.Enabled = false;
            btnPrev.Enabled = false;
            ViewState["currentPage"] = "1";
        }          

        txtPageNo.Text = iPageNo.ToString();
        lblPages.Text = ViewState["PageCount"].ToString();
        lblStatus.Text = "Displaying Page : " + iPageNo.ToString() + " of " + ViewState["PageCount"].ToString();
    }

    public DataSet getEmployees(int iPageNo, int iRecords)
    {
        SqlParameter[] param;
        param = new SqlParameter[2];
        param[0] = new SqlParameter("@iPageNo", iPageNo);
        param[1] = new SqlParameter("@iPageRecords", iRecords);
        SqlConnection conn = new SqlConnection(GetConnectionString());
        conn.Open();
        SqlCommand cmd = new SqlCommand("uspGetEmployees", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(param);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        conn.Close();
        conn.Dispose();
        return ds;
    }

    private void PageChange(int currentPage, PageNav pg)
    {
        int pageCount;
        pageCount = Convert.ToInt32(ViewState["PageCount"]);
        btnFirst.Enabled = true;
        btnPrev.Enabled = true;
        btnNext.Enabled = true;
        btnLast.Enabled = true;       
        switch (pg)
        {
            case PageNav.First:
                currentPage = 1;
                btnFirst.Enabled = false;
                btnPrev.Enabled = false;
                break;
            case PageNav.Previous:
                if (currentPage == 2)
                {
                    btnFirst.Enabled = false;
                    btnPrev.Enabled = false;
                }
                currentPage--;
                break;
            case PageNav.Next:
                if (currentPage == pageCount - 1)
                {
                    btnNext.Enabled = false;
                    btnLast.Enabled = false;
                }
                currentPage++;
                break;
            case PageNav.Last:
                btnNext.Enabled = false;
                btnLast.Enabled = false;
                currentPage = Convert.ToInt32(ViewState["PageCount"]);
                break;
            case PageNav.None:
                if (currentPage == 1)
                {
                    btnFirst.Enabled = false;
                    btnPrev.Enabled = false;
                }
                else if (currentPage == pageCount)
                {
                    btnNext.Enabled = false;
                    btnLast.Enabled = false;
                }
                break;
        }
        BindGrid(currentPage, iPageRecords);
        ViewState["currentPage"] = currentPage;
    }
    #endregion
}
OUTPUT
Custom Paging Output

Jan 4, 2011

Pivot with Dynamic Columns in SQL Server

In this post, you will learn about dynamic pivot in SQL Server and where it is required with an example.

Pivot is basically transpose used to convert unique row values of a column as columns of the resultset.

If we already know all unique values of the column and are static then we can use static pivot where we can pass the unique values in the query, but if we are not sure of all the unique values we will require dynamic pivot where we will have to dynamically create a list of values to pass for pivot.

Let's take an example of dynamic pivot

Lets create a sales table and populate some data

CREATE TABLE tblSales (
      CustCode  VARCHAR(50)
    , InvAmt    INT
    , InvDate    DATETIME
)

INSERT INTO tblSales
SELECT 'C0001', 1500, '10/01/2010' UNION ALL
SELECT 'C0002', 2000, '10/01/2010' UNION ALL
SELECT 'C0002', 1100, '10/25/2010' UNION ALL
SELECT 'C0001', 1200, '10/20/2010' UNION ALL
SELECT 'C0001', 1600, '11/05/2010' UNION ALL
SELECT 'C0002', 1300, '11/17/2010'

SELECT * from tblSales
OUTPUT
Sales Table Data

Now, suppose we want to use pivot to display Invoice Amount for each Customer code on the basis of Period (Month/Year) and Period will be dynamically construct as column on the basis of Invoice Period i.e (Invoice from date and to date)

DECLARE   @dtFrom DATETIME
        , @dtTo DATETIME
        , @sPeriodList VARCHAR(100)
        , @sQuery NVARCHAR(500)

SET @dtFrom = '2010-10-01'
SET @dtTo = '2010-11-30'

SELECT @sPeriodList = STUFF((
    SELECT  distinct ',[' + CONVERT(VARCHAR(7), InvDate, 111) + ']' AS [data()]
    FROM    tblSales
    WHERE  InvDate BETWEEN @dtFrom AND @dtTo    
    FOR XML PATH('')
),1,1,'')

SET @sQuery='SELECT CustCode,' + @sPeriodList + ' FROM (
  SELECT CustCode, InvAmt, CONVERT(VARCHAR(7), InvDate, 111) AS Period 
  FROM   tblSales) src
  PIVOT  (SUM(InvAmt) FOR Period IN (' + @sPeriodList + ')
) pvt'
EXEC sp_executesql @sQuery
OUTPUT
Dynamic Pivot