How to implement Gridview Custom paging using Stored Procedure?

Can someone redirect me to your own blog link or any other link explaining complete tutorial about开发者_如何学JAVA how to implement gridview custom paging using stored procedure?

My search in google giving me old articles from year 2006. Right now I am using asp.net 3.5 and c#.

---

.aspx page

    <asp:DropDownList ID="ddlSelector" AutoPostBack="true" runat="server" 
        onselectedindexchanged="ddlSelector_SelectedIndexChanged" >
        <asp:ListItem>Employee</asp:ListItem>
        <asp:ListItem>Customer</asp:ListItem>
    </asp:DropDownList>

    <asp:Button ID="btnSearch" runat="server" onclick="btnSearch_Click" Text="Search" />

    <br />
    <table>
        <tr>
            <td class="txtclmn">
                First Name:</td>
            <td>
                <asp:TextBox ID="txtFname" runat="server"></asp:TextBox>
            </td>
            <td class="txtclmn">
                Last Name:</td>
            <td>
                <asp:TextBox ID="txtLname" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="txtclmn">
                Middle Name:</td>
            <td>
                <asp:TextBox ID="txtMname" runat="server"></asp:TextBox>
            </td>
            <td class="txtclmn">
                Phone Number:</td>
            <td>
                <asp:TextBox ID="txtPhone" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="txtclmn">
                Email Address:</td>
            <td>
                <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
            </td>
            <td class="txtclmn">
            </td>
            <td>
            </td>

        </tr>

    </table>
    <div runat="server" id="empCriteria">
    <table >
        <tr>
            <td class="txtclmn">
                <asp:Label ID="lblHdate" runat="server" Text="Hire Date:"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtHMonth" runat="server" MaxLength="2" CssClass="input2char"></asp:TextBox>
                <asp:Label ID="lblHdash1" runat="server" Text="-"></asp:Label>
                <asp:TextBox ID="txtHDay" runat="server" MaxLength="2" CssClass="input2char"></asp:TextBox>
                <asp:Label ID="lblHdash2" runat="server" Text="-"></asp:Label>
                <asp:TextBox ID="txtHYear" runat="server" MaxLength="4" CssClass="input4char"></asp:TextBox>
            </td>
            <td class="txtclmn">
                <asp:Label ID="lblBdate" runat="server" Text="Birth Date:"></asp:Label>
                </td>
            <td>
                <asp:TextBox ID="txtBMonth" runat="server"  MaxLength="2" CssClass="input2char"></asp:TextBox>
                <asp:Label ID="lblBdash1" runat="server" Text="-"></asp:Label>
                <asp:TextBox ID="txtBDay" runat="server"  MaxLength="2" CssClass="input2char"></asp:TextBox>
                <asp:Label ID="lblBdash2" runat="server" Text="-"></asp:Label>
                <asp:TextBox ID="txtBYear" runat="server" MaxLength="4" CssClass="input4char"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="txtclmn">
                <asp:Label ID="lblLoginId" runat="server" Text="Login ID:"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtLogin" runat="server"></asp:TextBox>
            </td>
            <td class="txtclmn">
                <asp:Label ID="lblJobTitle" runat="server" Text="Job Title:"></asp:Label></td>
            <td>
                <asp:TextBox ID="txtJobTitle" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="txtclmn">
                <asp:Label ID="lblGender" runat="server" Text="Gender:"></asp:Label>
            </td>
            <td>
                <asp:DropDownList ID="ddlGender" runat="server" style="width: 73px">
                    <asp:ListItem Value="-1">-----------</asp:ListItem>
                    <asp:ListItem Value="M">Male</asp:ListItem>
                    <asp:ListItem Value="F">Female</asp:ListItem>
                </asp:DropDownList>
            </td>
            <td class="txtclmn">
                <asp:Label ID="lblMarStat" runat="server" Text="Marital Status:"></asp:Label>
            </td>
            <td>
                <asp:DropDownList ID="ddlMarStat" runat="server">
                    <asp:ListItem Value="-1">-----------</asp:ListItem>
                    <asp:ListItem Value="S">Single</asp:ListItem>
                    <asp:ListItem Value="M">Married</asp:ListItem>
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td class="txtclmn">
                <asp:Label ID="lblActive" runat="server" Text="Active:"></asp:Label>
            </td>
            <td>
                <asp:DropDownList ID="ddlActive" runat="server">
                    <asp:ListItem Value="-1">-----</asp:ListItem>
                    <asp:ListItem Value="1">Yes</asp:ListItem>
                    <asp:ListItem Value="0">No</asp:ListItem>
                </asp:DropDownList>
            </td>
            <td class="txtclmn">
               <asp:Label ID="lblSalary" runat="server" Text="Salaried:"></asp:Label></td>
            <td>
                <asp:DropDownList ID="ddlSalary" runat="server">
                    <asp:ListItem Value="-1">-----</asp:ListItem>
                    <asp:ListItem Value="1">Yes</asp:ListItem>
                    <asp:ListItem Value="0">No</asp:ListItem>
                </asp:DropDownList>
            </td>
        </tr>
    </table>
    </div>
    <br />
    <br />
    <asp:GridView ID="gdvCust" runat="server" AllowPaging="True"
        AutoGenerateColumns="False" 
        onpageindexchanging="gdvCust_PageIndexChanging">
        <Columns>
            <asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" 
                SortExpression="ContactID" />
            <asp:BoundField DataField="Title" HeaderText="Title" ReadOnly="True" 
                SortExpression="Title" />
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True" 
                SortExpression="FirstName" />
            <asp:BoundField DataField="MiddleName" HeaderText="MiddleName" ReadOnly="True" 
                SortExpression="MiddleName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True" 
                SortExpression="LastName" />
            <asp:BoundField DataField="Suffix" HeaderText="Suffix" ReadOnly="True" 
                SortExpression="Suffix" />
            <asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress" 
                ReadOnly="True" SortExpression="EmailAddress" />
            <asp:BoundField DataField="Phone" HeaderText="Phone" ReadOnly="True" 
                SortExpression="Phone" />
            <asp:ButtonField ButtonType="Button" CommandName="Edit" Text="Edit" />
        </Columns>
    </asp:GridView>


    <br />
    <asp:GridView ID="gdvEmp" runat="server" AllowPaging="True"
        AutoGenerateColumns="False" 
        onpageindexchanging="gdvEmp_PageIndexChanging">
        <Columns>
            <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="True" 
                SortExpression="EmployeeID" />
            <asp:BoundField DataField="JobTitle" HeaderText="Job Title" ReadOnly="True" 
                SortExpression="Title" />
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True" 
                SortExpression="FirstName" />
            <asp:BoundField DataField="MiddleName" HeaderText="MiddleName" ReadOnly="True" 
                SortExpression="MiddleName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True" 
                SortExpression="LastName" />
            <asp:BoundField DataField="Suffix" HeaderText="Suffix" ReadOnly="True" 
                SortExpression="Suffix" />
            <asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress" 
                ReadOnly="True" SortExpression="EmailAddress" />
            <asp:BoundField DataField="Phone" HeaderText="Phone" ReadOnly="True" 
                SortExpression="Phone" />
            <asp:BoundField DataField="LoginID" HeaderText="LoginID" ReadOnly="True" 
                SortExpression="LoginID" />
            <asp:BoundField DataField="Title" HeaderText="Title" ReadOnly="True" 
                SortExpression="Title" />
            <asp:BoundField DataField="BirthDate" HeaderText="BirthDate" ReadOnly="True" 
                SortExpression="BirthDate" />
            <asp:BoundField DataField="MaritalStatus" HeaderText="MaritalStatus" ReadOnly="True" 
                SortExpression="MaritalStatus" />
            <asp:BoundField DataField="Gender" HeaderText="Gender" ReadOnly="True" 
                SortExpression="Gender" />
            <asp:BoundField DataField="HireDate" HeaderText="HireDate" 
                ReadOnly="True" SortExpression="HireDate" />
            <asp:CheckBoxField DataField="SalariedFlag" HeaderText="SalariedFlag" 
                ReadOnly="True" SortExpression="SalariedFlag" />
            <asp:CheckBoxField DataField="CurrentFlag" HeaderText="CurrentFlag" 
                ReadOnly="True" SortExpression="CurrentFlag" />
        </Columns>
    </asp:GridView>


</form>

codebehind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace TestProj
{
public partial class Search : System.Web.UI.Page
{

    static IQueryable<Contact> _conResults;
    //static IQueryable<EmpContact> _empResults;
    protected void Page_Load(object sender, EventArgs e)
    {


    }


    protected void ddlSelector_SelectedIndexChanged(object sender, EventArgs e)
    {
        gdvCust.DataSource = null;
        gdvCust.DataBind();
        gdvEmp.DataSource = null;
        gdvEmp.DataBind();
        bool flag;
        if (ddlSelector.SelectedValue == "Employee")
            flag = true;
        else
        {               
            flag = false;
        }
        foreach( Control c in empCriteria.Controls)
        {
            c.Visible = flag;
        }
    }
    private IQueryable<Contact> CreateCustQuery()
    {
       TestDataClassDataContext dc = new TestDataClassDataContext();
        var predicate = PredicateBuilder.True<Contact>();
        var cust = from individual in dc.Individuals
                  join contact in dc.Contacts on individual.ContactID equals contact.ContactID
                  select contact;
        if (!string.IsNullOrEmpty(txtLname.Text))
            predicate = predicate.And( e => e.LastName.Contains(txtLname.Text));
        if (!string.IsNullOrEmpty(txtFname.Text))
            predicate = predicate.And(e => e.FirstName.Contains(txtFname.Text));
        if (!string.IsNullOrEmpty(txtMname.Text))
            predicate = predicate.And(e => e.MiddleName.Contains(txtMname.Text));
        if (Utility.IsValidPhone(txtPhone.Text))
            predicate = predicate.And(e => e.Phone.Contains(txtPhone.Text));
        if (Utility.IsValidEmailAddress(txtEmail.Text))
            predicate = predicate.And(e => e.EmailAddress.Contains(txtEmail.Text));

        var results = cust.Where(predicate);

        return results;
    }

    //private void CreateEmpQuery()
    //{
    //    TestDataClassDataContext dc = new TestDataClassDataContext();

    //    var emp = from c in dc.Contacts
    //              from e in dc.Employees
    //              where c.ContactID == e.ContactID
    //              select new 
    //              {
    //                  FirstName = c.FirstName,LastName = c.LastName, MiddleName =c.MiddleName, Phone = c.Phone,
    //                  EmailAddress = c.EmailAddress
    //              };

    //    var predicate = PredicateBuilder.True<>();
    //    if (!string.IsNullOrEmpty(txtLname.Text))
    //        predicate = predicate.And(e => e.LastName.Contains(txtLname.Text));
    //    if (!string.IsNullOrEmpty(txtFname.Text))
    //        predicate = predicate.And(e => e.FirstName.Contains(txtFname.Text));
    //    if (!string.IsNullOrEmpty(txtMname.Text))
    //        predicate = predicate.And(e => e.MiddleName.Contains(txtMname.Text));
    //    if (Utility.IsValidPhone(txtPhone.Text))
    //        predicate = predicate.And(e => e.Phone.Contains(txtPhone.Text));
    //    if (Utility.IsValidEmailAddress(txtEmail.Text))
    //        predicate = predicate.And(e => e.EmailAddress.Contains(txtEmail.Text));

    //    var results = emp.Where(predicate);


    //}

    private void GetCustResults()
    {
        _conResults = CreateCustQuery();
        gdvCust.DataSource = _conResults;
        gdvCust.DataBind();
    }

    //private void GetEmpResults()
    //{
    //    _empResults = CreateEmpQuery();
    //    gdvEmp.DataSource = _empResults;
    //    gdvEmp.DataBind();
    //}

    protected void gdvCust_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gdvCust.PageIndex = e.NewPageIndex;
        gdvCust.DataSource = _conResults;
        gdvCust.DataBind();
    }
    //protected void gdvEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
    //{
    //    gdvCust.PageIndex = e.NewPageIndex;
    //    gdvEmp.DataSource = _empResults;
    //    gdvEmp.DataBind();
    //}

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        if (ddlSelector.SelectedValue == "Customer")
            GetCustResults();
        //else
            //GetEmpResults();
    }



}
}

only finished the cust results before I moved on to another project I was using linq at the time but this might help you understand what is needed to get a gridview going. this is using linq with AdventureWorks as a sample database

Explanation of code now what you need to pay attention to is the

    <asp:GridView ID="gdvCust" runat="server" AllowPaging="True"
        AutoGenerateColumns="False" 
        onpageindexchanging="gdvCust_PageIndexChanging">

and the columns and bound fields on the aspx page where DataField = Name of column in database and the Header field is what is labeled at the top of the column on the gridview.

now on the code behind look at

private void GetCustResults()
{
    _conResults = CreateCustQuery();
    gdvCust.DataSource = _conResults;
    gdvCust.DataBind();
}
 //and    
protected void gdvCust_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gdvCust.PageIndex = e.NewPageIndex;
    gdvCust.DataSource = _conResults;
    gdvCust.DataBind();
}

they do the binding from a data source and the paging control. comment if you need some more explaination

---

Check the code below.

CREATE PROCEDURE getDeals
  @StartIndex int,
  @PageSize int,
  @TotalCount int OutPut
as

select @TotalCount=count(1) from mstrDeals;
WITH CTE AS
(
  select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY creationdate) RowNumber,dealid,dealTitle from mstrDeals
)
select * from CTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)

Then we need to generate pager with total no of rows, page size and current page, below is the sample code that populating pager here total number of pager links is 5 that can be customize further by resetting local variable named totalLinkInPage and have first and last button also which automatic enable or disable when current page is first or last accordingly.

Check out generate pagination code and bind gridview code (reference):-

Custom paging in asp.net using stored procedure in gridview with nice pagination

---

Since SQL's syntax hasn't changes much since 2006, there isn't much to update. On the stored procedure side you would still do a ROW_Number() call.

 With table AS 
 ( SELECT ID, NAME, 
   ROW_NUMBER() OVER (order by Name) as RowNumber 
   FROM Customers ) 

select * 
from table 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end