利用aspnetpage控件结合高效SQL存储过程分页

web层

//------------------------------------------------------------//

// 利用AspNetPage控件翻页,结合高效ＳＱＬ存储过程 //---------

//-----------------------------------------------------------//

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Text;

using HKOA.BusinessFacade;

namespace HKOA.web

{

public class WebForm2 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.DataGrid DataGrid1;

protected HKOA.WebControl.AspNetPager pager;

public string ErrorMsg;

public int PageCount;

private void Page_Load(object sender, System.EventArgs e)

{

if (!Page.IsPostBack)

{

CreateDatasource(1);

pager.RecordCount = PageCount;

}

// @tb varchar(50), --表名

// @col varchar(50), --按该列来进行分页

// @coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型

// @orderby bit, --排序,0-顺序,1-倒序

// @collist varchar(800),--要查询出的字段列表,*表示全部字段

// @pagesize int, --每页记录数

// @CurrentPage int, --指定页

// @Where varchar(800),--查询条件

// @PageCount int OUTPUT --总页数

private void CreateDatasource(int CurrentPage)

{

string tb, col, collist, condition;

tb = "Customers";

//排序列的列名

col = "CustomerID";

//返回的列名列表

collist = "*";

//查询的表件

condition = "CustomerID > 0";

int coltype, orderby;

coltype = 0;

orderby = 0;

pagesize = 10;

DocTree doctree = new DocTree();

DataTable dt = doctree.SP_PageList(tb,col,coltype,orderby,collist,pager.PageSize,pager.CurrentPageIndex,condition,ref PageCount);

DataGrid1.VirtualItemCount = PageCount;

DataGrid1.DataSource = dt.DefaultView;

DataGrid1.DataBind();

}

Web 窗体设计器生成的代码Web 窗体设计器生成的代码Web 窗体设计器生成的代码

// private void DataGrid1_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)

// {

// ListItemType elemType = e.Item.ItemType;

// //定制分页导航行,样式为[1] [2] 第 3 页 [4]

// if (elemType == ListItemType.Pager)

// {

// TableCell pager = (TableCell) e.Item.Controls[0];

// for (int i=0; i<pager.Controls.Count; i+=2)

// {

// Object o = pager.Controls[i];

// if (o is LinkButton)

// {

// LinkButton h = (LinkButton) o;

// h.Text = " " + h.Text + " ";

// }

// else

// {

// Label l = (Label) o;

// l.Text = "第" + l.Text + "页";

// }

// private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

// {

// DataGrid1.CurrentPageIndex = e.NewPageIndex;

// CreateDatasource(DataGrid1.CurrentPageIndex+1);

// }

private void pager_PageChanged(object src, HKOA.WebControl.PageChangedEventArgs e)

{

pager.CurrentPageIndex=e.NewPageIndex;

CreateDatasource(DataGrid1.CurrentPageIndex+1);

System.Text.StringBuilder sb=new StringBuilder("<script Language=\"Javascript\"><!--\n");

sb.Append("var el=document.all;");

sb.Append(DataGrid1.ClientID);

sb.Append(".scrollIntoView(true);");

sb.Append("<");

sb.Append("/");

sb.Append("script>");

if(!Page.IsStartupScriptRegistered("scrollScript"))

Page.RegisterStartupScript("scrollScript",sb.ToString());

}

--------------------------------------------

逻辑层代码

--------------------------------------------

public DataTable SP_PageList(string tb,string col,int coltype,int orderby,string collist,int pagesize,int CurrentPage,string Where,ref int PageCount)

{

FileA acc = new FileA();

return acc.SP_PageList(tb,col,coltype,orderby,collist,pagesize,CurrentPage,Where,ref PageCount).Tables[0];

}

------------------------------------------

数据层代码

------------------------------------------

public DataSet SP_PageList(string tb,string col,int coltype,int orderby,string collist,int pagesize,int CurrentPage,string Where,ref int PageCount)

{

SqlCommand cmd= new SqlCommand("sp_page",mySqlConnection);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter sp_temp ;

sp_temp = cmd.Parameters.Add("@tb", SqlDbType.VarChar, 50);