Friday, 26 September 2014

Custom paging for GridView using DataList in ASP.NET using C#/VB.NET to increase SQL perfomance

Hi friends,in this article I will explain about How to do Custom paging for GridView using DataList in ASP.NET using C#/VB.NET to increase SQL perfomance
When there are bulk records to show in the GridView, developers often use paging to distribute the complete records with multiple pages to show the records in the GridView. This means that, when you change the page index, the GridView is bound everytime  from the database to show the next records.
If Stored Procedure retrieving 1000+ records, for every page number we change the SP retrieving 1000+ records every time. This is time taking. To increase the performance we use custom paging.

Stored Procedure
--USP_GET_UserDetails 1,10
ALTER PROCEDURE [USP_GET_UserDetails] @currentPage int,
@pageSize int
AS
BEGIN
  DECLARE @Query varchar(max)
  DECLARE @FirstRow int,
          @LastRow int
  SELECT
    @FirstRow = (@CurrentPage - 1) * @PageSize + 1,
    @LastRow = (@CurrentPage - 1) * @PageSize + @PageSize;
  SELECT
    *
  FROM (SELECT
    ROW_NUMBER() OVER (ORDER BY User_ID) AS RowNumber,
    *
  FROM (SELECT
    *
  FROM User_Details) AS TBL1) AS TBL2
  WHERE RowNumber >= @FirstRow
  AND RowNumber <= @LastRow


  SELECT DISTINCT
    COUNT(*)
  FROM User_Details
END

ASP.NET:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Custom paging for GridView using DataList in ASP.NET using C#/VB.NET</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="gvData" runat="server" BackColor="White" BorderColor="#CC9966"
            PageSize="4" AutoGenerateColumns="false" BorderStyle="Solid" BorderWidth="1px"
            CellPadding="4" Font-Names="Georgia" Font-Size="Small" Width="475px">
            <Columns>
                <asp:BoundField DataField="USER_ID" HeaderText="USER ID" />
                <asp:BoundField DataField="UserName" HeaderText="User Name" />
            </Columns>
            <FooterStyle BackColor="Tan" />
            <AlternatingRowStyle BackColor="#E6E6E1" />
            <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
        </asp:GridView>
        <table style="border:1px solid black;width:475px;border-top:none;">
            <tr>
                <td>
                    <asp:LinkButton ID="lnkPrevPages" runat="server" Text="..." CommandName="PrevPage"
                        Visible="false" OnClick="lnkPrevPages_Click" ForeColor="White"></asp:LinkButton>
                </td>
                <td>
                    <asp:DataList ID="dlPaging" runat="server" RepeatDirection="Horizontal" OnItemCommand="dlPaging_ItemCommand"
                        CellPadding="1" CellSpacing="1" OnItemDataBound="dlPaging_ItemDataBound">
                        <ItemStyle HorizontalAlign="Left" ForeColor="#FFFFFF" Font-Bold="True" 
                            Height="20px" Font-Size="10" />
                        <ItemTemplate>
                            <asp:LinkButton ID="lnkpageNum" runat="server" CommandName="Paging" CommandArgument='<%#Eval("PageNumbers") %>'
                                Text='<%#Eval("PageNumbers") %>'></asp:LinkButton>
                        </ItemTemplate>
                    </asp:DataList>
                </td>
                <td>
                    <asp:LinkButton ID="lnkNextPages" runat="server" Text="..." CommandName="PrevPage"
                        Visible="false" OnClick="lnkNextPages_Click" ForeColor="White"></asp:LinkButton>
                </td>
            </tr>
        </table>
        <asp:Label Visible="false" ID="hdnPageCount" runat="server" />
        <asp:Label Visible="false" ID="HdnCurrentPageNumber" runat="server" />
        <asp:Label Visible="false" ID="Hdnduplicatecurrent" runat="server" />
        <asp:Label Visible="false" ID="maxvalue" runat="server" />
        <asp:Label Visible="false" ID="minvalue" runat="server" />
    </div>
    </form>
</body>
</html>
C#:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class GridViewPagingWithDataList : System.Web.UI.Page                                              
{
    int tryparse_out = 0;
    int TotalRecords;
    int PageSize;
    int CurrentPage;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }

    private void BindData()
    {
        Int32.TryParse(Convert.ToString(ViewState["VS_CurrentPage"]), out tryparse_out);
        CurrentPage = tryparse_out == 0 ? 1 : tryparse_out;
        PageSize = gvData.PageSize;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        SqlCommand cmd = new SqlCommand("[USP_GET_UserDetails]", con);
        cmd.Parameters.Add("@currentPage", SqlDbType.Int).Value = CurrentPage;
        cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = PageSize;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        da.Fill(ds);
        gvData.DataSource = ds;
        gvData.DataBind();
        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
        {
            int _pagesize = gvData.PageSize;
            DataTable dtPaging = new DataTable();
            dtPaging.Columns.Add("PageNumbers", typeof(Int32));

            if (ds.Tables[1] != null && Convert.ToInt32(ds.Tables[1].Rows[0][0]) > 0)
                TotalRecords = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

            if (!string.IsNullOrEmpty(Convert.ToString(TotalRecords)))
            {
                int rowscount = Convert.ToInt32(TotalRecords);
                int pagestart;

                if (rowscount > _pagesize)
                {
                    dlPaging.Visible = true;
                    int pages = rowscount / _pagesize;
                    if (rowscount % _pagesize != 0)
                        pages++;

                    if (string.IsNullOrEmpty(Hdnduplicatecurrent.Text))
                    {
                        ViewState["VS_CurrentPage"] = "1";
                        Hdnduplicatecurrent.Text = "1";
                        pagestart = 1;
                    }
                    else
                        pagestart = Convert.ToInt32(Hdnduplicatecurrent.Text);

                    for (int i = pagestart; i <= pages; i++)
                    {
                        DataRow dr = dtPaging.NewRow();
                        dr["PageNumbers"] = i;

                        if (dtPaging.Rows.Count == 10)
                        {
                            lnkNextPages.Visible = true;
                            break;
                        }
                        dtPaging.Rows.Add(dr);
                        maxvalue.Text = Convert.ToString(i);
                    }

                    minvalue.Text = Convert.ToString(dtPaging.Rows[0]["PageNumbers"]);
                    if (Convert.ToInt32(maxvalue.Text) == pages)
                    {
                        lnkPrevPages.Visible = true;
                        lnkNextPages.Visible = false;
                    }

                    if (Convert.ToInt32(minvalue.Text) == 1)
                        lnkPrevPages.Visible = false;
                    else
                        lnkPrevPages.Visible = true;
                }
                else
                {
                    dlPaging.Visible = false;
                    lnkNextPages.Visible = false;
                    lnkPrevPages.Visible = false;
                }
                hdnPageCount.Text = Convert.ToString(_pagesize);

                if (string.IsNullOrEmpty(HdnCurrentPageNumber.Text))
                    HdnCurrentPageNumber.Text = "1";

                dlPaging.DataSource = dtPaging;
                dlPaging.DataBind();
            }
        }

    }
    protected void dlPaging_ItemCommand(object source, DataListCommandEventArgs e)
    {
        if (e.CommandName == "Paging")
        {
            int pagnum = Convert.ToInt32(e.CommandArgument);
            HdnCurrentPageNumber.Text = Convert.ToString(e.CommandArgument);
            ViewState["VS_CurrentPage"] = pagnum.ToString();
            BindData();
        }
    }

    protected void dlPaging_ItemDataBound(object sender, DataListItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            if (!string.IsNullOrEmpty(Convert.ToString(hdnPageCount.Text)))
            {
                LinkButton lnkPaging = (LinkButton)e.Item.FindControl("lnkpageNum");
                lnkPaging.ForeColor = System.Drawing.Color.Black;
                lnkPaging.Attributes.Add("onclientclick", "return true;");
                if (Convert.ToString(ViewState["VS_CurrentPage"]) == lnkPaging.Text)
                {
                    lnkPaging.ForeColor = System.Drawing.Color.Maroon;
                    lnkPaging.Font.Underline = false;
                    lnkPaging.Attributes.Add("onclientclick", "return false;");
                }
            }
        }
    }

    protected void lnkNextPages_Click(object sender, EventArgs e)
    {
        if (!string.IsNullOrEmpty(hdnPageCount.Text))
        {
            int nextpage = Convert.ToInt32(maxvalue.Text) + 1;
            HdnCurrentPageNumber.Text = Convert.ToString(Convert.ToInt32(maxvalue.Text) + 1);
            Hdnduplicatecurrent.Text = Convert.ToString(Convert.ToInt32(maxvalue.Text) + 1);
            ViewState["VS_CurrentPage"] = HdnCurrentPageNumber.Text;
            lnkPrevPages.Visible = true;
            BindData();
        }
    }

    protected void lnkPrevPages_Click(object sender, EventArgs e)
    {
        if (!string.IsNullOrEmpty(hdnPageCount.Text))
        {
            int prevpage = Convert.ToInt32(minvalue.Text) - 1;
            Hdnduplicatecurrent.Text = Convert.ToString(prevpage - 9);
            HdnCurrentPageNumber.Text = Convert.ToString(prevpage);
            ViewState["VS_CurrentPage"] = HdnCurrentPageNumber.Text;
            BindData();
        }
    }
}

VB.NET:
Imports System.Data
Imports System.Data.SqlClient  
Imports System.Configuration

Partial Public Class GridViewPagingWithDataList
    Inherits System.Web.UI.Page
    Private tryparse_out As Integer = 0
    Private TotalRecords As Integer
    Private PageSize As Integer
    Private CurrentPage As Integer
    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not IsPostBack Then
            BindData()
        End If
    End Sub

    Private Sub BindData()
        Int32.TryParse(Convert.ToString(ViewState("VS_CurrentPage")), tryparse_out)
        CurrentPage = If(tryparse_out = 0, 1, tryparse_out)
        PageSize = gvData.PageSize
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
        Dim cmd As New SqlCommand("[USP_GET_UserDetails]", con)
        cmd.Parameters.Add("@currentPage", SqlDbType.Int).Value = CurrentPage
        cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = PageSize
        cmd.CommandType = CommandType.StoredProcedure
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        da.Fill(ds)
        gvData.DataSource = ds
        gvData.DataBind()
        If ds IsNot Nothing AndAlso ds.Tables.Count > 0 AndAlso ds.Tables(0) IsNot Nothing AndAlso ds.Tables(0).Rows.Count > 0 Then
            Dim _pagesize As Integer = gvData.PageSize
            Dim dtPaging As New DataTable()
            dtPaging.Columns.Add("PageNumbers", GetType(Int32))

            If ds.Tables(1) IsNot Nothing AndAlso Convert.ToInt32(ds.Tables(1).Rows(0)(0)) > 0 Then
                TotalRecords = Convert.ToInt32(ds.Tables(1).Rows(0)(0))
            End If

            If Not String.IsNullOrEmpty(Convert.ToString(TotalRecords)) Then
                Dim rowscount As Integer = Convert.ToInt32(TotalRecords)
                Dim pagestart As Integer

                If rowscount > _pagesize Then
                    dlPaging.Visible = True
                    Dim pages As Integer = rowscount \ _pagesize
                    If rowscount Mod _pagesize <> 0 Then
                        pages += 1
                    End If

                    If String.IsNullOrEmpty(Hdnduplicatecurrent.Text) Then
                        ViewState("VS_CurrentPage") = "1"
                        Hdnduplicatecurrent.Text = "1"
                        pagestart = 1
                    Else
                        pagestart = Convert.ToInt32(Hdnduplicatecurrent.Text)
                    End If

                    For i As Integer = pagestart To pages
                        Dim dr As DataRow = dtPaging.NewRow()
                        dr("PageNumbers") = i

                        If dtPaging.Rows.Count = 10 Then
                            lnkNextPages.Visible = True
                            Exit For
                        End If
                        dtPaging.Rows.Add(dr)
                        maxvalue.Text = Convert.ToString(i)
                    Next

                    minvalue.Text = Convert.ToString(dtPaging.Rows(0)("PageNumbers"))
                    If Convert.ToInt32(maxvalue.Text) = pages Then
                        lnkPrevPages.Visible = True
                        lnkNextPages.Visible = False
                    End If

                    If Convert.ToInt32(minvalue.Text) = 1 Then
                        lnkPrevPages.Visible = False
                    Else
                        lnkPrevPages.Visible = True
                    End If
                Else
                    dlPaging.Visible = False
                    lnkNextPages.Visible = False
                    lnkPrevPages.Visible = False
                End If
                hdnPageCount.Text = Convert.ToString(_pagesize)

                If String.IsNullOrEmpty(HdnCurrentPageNumber.Text) Then
                    HdnCurrentPageNumber.Text = "1"
                End If

                dlPaging.DataSource = dtPaging
                dlPaging.DataBind()
            End If
        End If

    End Sub
    Protected Sub dlPaging_ItemCommand(source As Object, e As DataListCommandEventArgs)
        If e.CommandName = "Paging" Then
            Dim pagnum As Integer = Convert.ToInt32(e.CommandArgument)
            HdnCurrentPageNumber.Text = Convert.ToString(e.CommandArgument)
            ViewState("VS_CurrentPage") = pagnum.ToString()
            BindData()
        End If
    End Sub

    Protected Sub dlPaging_ItemDataBound(sender As Object, e As DataListItemEventArgs)
        If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
            If Not String.IsNullOrEmpty(Convert.ToString(hdnPageCount.Text)) Then
                Dim lnkPaging As LinkButton = DirectCast(e.Item.FindControl("lnkpageNum"), LinkButton)
                lnkPaging.ForeColor = System.Drawing.Color.Black
                lnkPaging.Attributes.Add("onclientclick", "return true;")
                If Convert.ToString(ViewState("VS_CurrentPage")) = lnkPaging.Text Then
                    lnkPaging.ForeColor = System.Drawing.Color.Maroon
                    lnkPaging.Font.Underline = False
                    lnkPaging.Attributes.Add("onclientclick", "return false;")
                End If
            End If
        End If
    End Sub

    Protected Sub lnkNextPages_Click(sender As Object, e As EventArgs)
        If Not String.IsNullOrEmpty(hdnPageCount.Text) Then
            Dim nextpage As Integer = Convert.ToInt32(maxvalue.Text) + 1
            HdnCurrentPageNumber.Text = Convert.ToString(Convert.ToInt32(maxvalue.Text) + 1)
            Hdnduplicatecurrent.Text = Convert.ToString(Convert.ToInt32(maxvalue.Text) + 1)
            ViewState("VS_CurrentPage") = HdnCurrentPageNumber.Text
            lnkPrevPages.Visible = True
            BindData()
        End If
    End Sub

    Protected Sub lnkPrevPages_Click(sender As Object, e As EventArgs)
        If Not String.IsNullOrEmpty(hdnPageCount.Text) Then
            Dim prevpage As Integer = Convert.ToInt32(minvalue.Text) - 1
            Hdnduplicatecurrent.Text = Convert.ToString(prevpage - 9)
            HdnCurrentPageNumber.Text = Convert.ToString(prevpage)
            ViewState("VS_CurrentPage") = HdnCurrentPageNumber.Text
            BindData()
        End If
    End Sub

End Class

The output of the above code as shown in the below figure.



You can download the code by clicking on the below Download image. 

Posted by
Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.