如何使用存储过程实现Gridview自定义分页？

有人可以将我重定向到您自己的博客链接或任何其他链接，解释有关如何使用存储过程实现gridview自定义分页的完整教程吗？我在谷歌搜索给我2006年的旧文章。现在我使用的是asp.net 3.5和c＃。

已邀请:

3 个回复

.aspx页面

    <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>

代码隐藏

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();
    }



}
}

在我转到另一个我当时正在使用linq的项目之前，我只完成了cust结果，但这可能有助于您了解获得gridview所需的内容。这是使用linq和AdventureWorks作为示例数据库现在代码解释你需要注意的是

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

以及aspx页面上的列和绑定字段，其中DataField =数据库中列的名称，标题字段是gridview上列顶部标记的内容。现在关注代码看看

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();
}

他们从数据源和分页控件进行绑定。如果您需要更多解释，请发表评论

脖呐

检查下面的代码。

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)

然后我们需要生成总页数没有行，页面大小和当前页面的分页器，下面是填充页面分页器的示例代码这里分页器链接的总数是5，可以通过重置名为totalLinkInPage的局部变量进行自定义，并具有第一个和最后一个当前页面首先或最后相应时自动启用或禁用的按钮。检查生成分页代码并绑定gridview代码（参考）： - 在asp.net中使用gridview中的存储过程进行自定义分页，具有良好的分页

葛瞎说漓

由于自2006年以来SQL的语法没有太大变化，因此没有太多要更新的内容。在存储过程方面，您仍然会执行ROW_Number（）调用。

 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

要回复问题请先登录或注册

如何使用存储过程实现Gridview自定义分页？

3 个回复

发起人

custom_paging

custompaging

问题状态

如何使用存储过程实现Gridview自定义分页？

与内容相关的链接

3 个回复

发起人

custom_paging

custompaging

问题状态