Home > ASP.Net > Paging with Gridview using SQL

Paging with Gridview using SQL


Hi

Try this example to create paging with Gridview using SQL

In this Example i will use Northwind Database and Products table

Step1:- create a website and add web page

Step2:- open Sql Server 2005 and create the following Stored Procedure

CREATE PROCEDURE [usp_GetProducts]
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT

AS

DECLARE @first_id int, @startRow int

SET @startRowIndex =  (@startRowIndex - 1)  * @maximumRows

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = ProductID FROM Products ORDER BY ProductID

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT ProductID, ProductName FROM Products WHERE
ProductID >= @first_id
ORDER BY ProductID
 
SET ROWCOUNT 0

-- GEt the total rows

SELECT @totalRows = COUNT(ProductID) FROM Products
GO

Step3:- in Default.aspx add Gridview and Two lables and Two button control

<div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
            DataKeyNames="ProductID" ForeColor="#333333">
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"
                    ReadOnly="True" SortExpression="ProductID" />
                <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
            </Columns>
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <table style="width: 292px">
            <tr>
                <td>
                    <asp:Button ID="Btn_Previous" CommandName="Previous"
            runat="server" OnCommand="ChangePage"
            Text="Previous" /></td>
                <td colspan="2" style="text-align: center">
                    Page
                    <asp:Label ID="lblCurrentPage" runat="server" Text="Label"></asp:Label>
                    of
                    <asp:Label ID="lblTotalPages" runat="server"></asp:Label></td>
                <td>
                    <asp:Button ID="Btn_Next" runat="server" CommandName="Next"
            OnCommand="ChangePage" Text="Next" /></td>
            </tr>
        </table>
   
    </div>

and in code behind:-

protected int currentPageNumber = 1;
    private const int PAGE_SIZE = 10;

    private void BindData()
    {
        string connectionString = "Server=localhost;" +"Database=Northwind;Trusted_Connection=true";
        SqlConnection myConnection = new SqlConnection(connectionString);
        SqlCommand myCommand = new SqlCommand("usp_GetProducts",myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@startRowIndex",currentPageNumber);
        myCommand.Parameters.AddWithValue("@maximumRows", PAGE_SIZE);
        myCommand.Parameters.Add("@totalRows", SqlDbType.Int, 4);
        myCommand.Parameters["@totalRows"].Direction =ParameterDirection.Output;
        SqlDataAdapter ad = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        ad.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        // get the total rows
        double totalRows = (int)myCommand.Parameters["@totalRows"].Value;
        lblTotalPages.Text = CalculateTotalPages(totalRows).ToString();
        lblCurrentPage.Text = currentPageNumber.ToString();
        if (currentPageNumber == 1)
        {
            Btn_Previous.Enabled = false;
            if (Int32.Parse(lblTotalPages.Text) > 0)
            {
                Btn_Next.Enabled = true;
            }
            else
                Btn_Next.Enabled = false;

        }

        else
        {
            Btn_Previous.Enabled = true;
            if (currentPageNumber == Int32.Parse(lblTotalPages.Text))
                Btn_Next.Enabled = false;
            else Btn_Next.Enabled = true;
        }
    }

    protected void ChangePage(object sender, CommandEventArgs e)
    {

        switch (e.CommandName)
        {
            case "Previous":
                currentPageNumber = Int32.Parse(lblCurrentPage.Text) - 1;
                break;

            case "Next":
                currentPageNumber = Int32.Parse(lblCurrentPage.Text) + 1;
                break;
        }

        BindData();
    }

    private int CalculateTotalPages(double totalRows)
    {
        int totalPages = (int)Math.Ceiling(totalRows / PAGE_SIZE);
        return totalPages;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }

Hope this helps

Good Luck

Advertisements
Categories: ASP.Net Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: