如何分页的GridView和SQL自定义查询使用ROW_NUMBER [英] How to paginate a gridview and SQL custom query with ROW_NUMBER
问题描述
我有执行自定义查询，其地方保存在数据库上的页面。
我需要能够启用GridView的分页。
I have a page that executes a custom query that its saved somewhere on the database. I need to be able to enable pagination on the gridview.
例如目的查询保存在数据库上它是这样的：
For example purposes the query saved on the database its like this:
select * from requestbases
这将返回10,000行。
This returns 10,000 rows.
使用下面我让它返回10行的方式。
With the method below I make it return 10 rows.
public DataTable GetGenericResults(string strsql, int pageIndex)
{
StringBuilder sb = new StringBuilder();
sb.Append("WITH MyPagedData as ( ");
int indexFrom = strsql.IndexOf("from");
sb.Append(strsql.Substring(0, indexFrom));
sb.Append(", ");
sb.Append("ROW_NUMBER() OVER(ORDER BY RequestBaseId DESC) as RowNum ");
sb.Append(strsql.Substring(indexFrom));
sb.Append(") ");
sb.Append("SELECT * from MyPagedData where RowNum between @StartIndex and @StartIndex + 10");
using(var connection = (SqlConnection)_context.Database.Connection)
{
var adapter = new SqlDataAdapter(sb.ToString(), connection);
adapter.SelectCommand.Parameters.Add("@StartIndex", SqlDbType.Int).Value = pageIndex;
var results = new DataSet();
adapter.Fill(results, "Results");
return results.Tables["Results"];
}
}
这是code到电网结合
And this is the code to bind the grid
var datatable = RequestBaseBL.GetGenericResults(query.QuerySql, 0);
if (datatable.Rows.Count > 0)
{
LblCount.Text = datatable.Rows.Count + " records";
PanelResults.Visible = true;
GrvCustomResults.Visible = true;
GrvCustomResults.DataSource = datatable;
GrvCustomResults.DataBind();
}
问题是查询本身返回10行，因此在GridView绝不会显示一个寻呼机。
The problem is that the query itself returns 10 rows, so the gridview will never show a pager.
<asp:GridView ID="GrvCustomResults" runat="server" Visible="false" AutoGenerateColumns="true">
<PagerSettings
Visible="true"
Position="TopAndBottom"
PreviousPageText="Previous"
NextPageText="Next"
Mode="NumericFirstLast" />
<HeaderStyle CssClass="gridheader" />
推荐答案
这code在aspx页面
this code in aspx page
<asp:Panel runat="server" id="pnlPager" CssClass="pager">
</asp:Panel>
下面将在的.cs页使用的方法
这被用于跟踪的记录页次和页大小
Here the method that will use in the .cs page This is used to track the record of the pagenum and pagesize
protected int PageNum
{
get { return Convert.ToInt16(ViewState["PageNum"]); }
set { ViewState["PageNum"] = value; }
}
protected int PageSize
{
get { return Convert.ToInt16(ViewState["PageSize"]); }
set { ViewState["PageSize"] = value; }
}
protected int TotalRecord
{
get { return Convert.ToInt16(ViewState["TotalRecords"]); }
set { ViewState["TotalRecords"] = value; }
}
这是用于调用存储过程，将发送页次的方法，页面大小
This is the method is used for the call the store procedure that will send the pagenum ,page size
public DataSet GetCollegeSerachData(int PageNum,int PageSize,out int TotalRecords)
{
DS = new DataSet();
ObjDataWrapper = new DataWrapper(ClsCommon.CnnString, CommandType.StoredProcedure);
TotalRecords=0;
ErrorCount = 0;
Searchpattern = "";
try
{
ObjDataWrapper.AddParameter("@PageNum", PageNum);
ObjDataWrapper.AddParameter("@PageSize", PageSize);
SqlParameter ObjTotalRecords=(SqlParameter)(ObjDataWrapper.AddParameter("@TotalRowsNum","",SqlDbType.Int,ParameterDirection.Output));
DS=ObjDataWrapper.ExecuteDataSet("ADMJGetCollegeSearch");
if(ObjTotalRecords.Value!= DBNull.Value || ObjTotalRecords!=null)
{
TotalRecords=Convert.ToInt32(ObjTotalRecords.Value);
}
}
catch (Exception Ex)
{
string err = Ex.Message;
if (Ex.InnerException != null)
{
err = err + " :: Inner Exception :- " + Ex.InnerException.Message;
}
string addInfo = "Error While Executing GetCollegeSerachData in ClsColleges:: -> ";
ClsExceptionPublisher objPub = new ClsExceptionPublisher();
objPub.Publish(err, addInfo);
}
return DS;
}
这将返回将用于在存储过程绑定数据集
that will return the dataset that will used to bind the store procedure
下面的存储过程将用于分页
here the store procedure that will used for pagination
GO
/****** Object: StoredProcedure [dbo].[ADMJGetCollegeSearch] Script Date: 06/06/2012 15:43:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ADMJGetCollegeListByState]
@PageNum int,
@PageSize int,
@TotalRowsNum int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
WITH College_CollegeId As
(
SELECT 'RowNumber' = ROW_NUMBER() OVER(ORDER BY collegeid asc),College.*
FROM College
)
-- Query result
SELECT *
FROM College_CollegeId
WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
ORDER BY collegename asc
-- Returns total records number
SELECT @TotalRowsNum = count(*)
FROM College
END
最后，你会绑定在GridView
grdCourse.DataSource = DS.Tables [0];
grdCourse.DataBind（）;
grdCourse.Visible = TRUE;
at last you will bind the the gridview grdCourse.DataSource = DS.Tables[0]; grdCourse.DataBind(); grdCourse.Visible = true;
在网格视图的PageIndexChanging（对象发件人，GridViewPageEventArgs e）你将通过
at the PageIndexChanging(object sender, GridViewPageEventArgs e) of the grid view you will pass the
protected void grdCourse_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
Pagenum = e.NewPageIndex;
--call this method public DataSet GetCollegeSerachData(int PageNum,int PageSize,out int TotalRecords)
}
这篇关于如何分页的GridView和SQL自定义查询使用ROW_NUMBER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
