利用存储过程进行分页
一、 存储过程
ALTER PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
@strWhere varchar(1500) = '' -- 查询条件(注意: 不要加where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--以上代码的意思是如果@doCount传递过来的不是，就执行总数统计。以下的所有代码都是@doCount为的情况
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--如果@OrderType不是，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
RETURN
二、代码部份DataAccess.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
private const String TABLE_NAME_PARM = "@tblName";
private const String FIELDS_PARM = "@strGetFields";
private const String FLD_PARM = "@fldName"; //排序字段名
private const String PAGE_SIZE_PARM = "@PageSize";
private const String PAGE_INDEX_PARM = "@PageIndex";
private const String DO_COUNT_PARM = "@doCount";
private const String ORDER_TYPE_PRAM = "@OrderType";
private const String STR_WHERE_PRAM = "@strWhere";
public DataAccess()
{
//
// TODO: Add constructor logic here
//
}
private SqlCommand GetCommand()
{
SqlCommand cmd = new SqlCommand("pagination", new SqlConnection("Data Source=localhost;Initial Catalog=anju;Persist Security Info=True;User ID=sa;Password=sa"));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(TABLE_NAME_PARM, System.Data.SqlDbType.NVarChar, 255);
cmd.Parameters.Add(FIELDS_PARM, System.Data.SqlDbType.NVarChar, 1000);
cmd.Parameters.Add(FLD_PARM, System.Data.SqlDbType.NVarChar, 255);
cmd.Parameters.Add(PAGE_SIZE_PARM, System.Data.SqlDbType.Int, 32);
cmd.Parameters.Add(PAGE_INDEX_PARM, System.Data.SqlDbType.Int, 32);
cmd.Parameters.Add(DO_COUNT_PARM, System.Data.SqlDbType.Bit);
cmd.Parameters.Add(ORDER_TYPE_PRAM, System.Data.SqlDbType.Bit);
cmd.Parameters.Add(STR_WHERE_PRAM, System.Data.SqlDbType.NVarChar, 1500);
return cmd;
}
public DataSet ExecSql(string SqlString)
{
DataSet data = new DataSet();
SqlCommand cmd = new SqlCommand(SqlString, new SqlConnection("Data Source=localhost;Initial Catalog=anju;Persist Security Info=True;User ID=sa;Password=sa"));
SqlDataAdapter dsCommand = new SqlDataAdapter();
dsCommand.SelectCommand = cmd;
try
{
dsCommand.Fill(data);
}
catch
{
data=null;
}
return data;
}
public DataSet ReturnData(string tablename, string fields, string fld, int pagesize, int pageindex, bool docount, bool ordertype, string strwhere)
{
DataSet data = new DataSet();
SqlDataAdapter dsCommand = new SqlDataAdapter();
dsCommand.SelectCommand = GetCommand();
dsCommand.SelectCommand.Parameters[TABLE_NAME_PARM].Value = tablename;
dsCommand.SelectCommand.Parameters[FIELDS_PARM].Value = fields;
dsCommand.SelectCommand.Parameters[FLD_PARM].Value = fld;
dsCommand.SelectCommand.Parameters[PAGE_SIZE_PARM].Value = pagesize;
dsCommand.SelectCommand.Parameters[PAGE_INDEX_PARM].Value = pageindex;
dsCommand.SelectCommand.Parameters[DO_COUNT_PARM].Value = docount;
dsCommand.SelectCommand.Parameters[ORDER_TYPE_PRAM].Value = ordertype;
dsCommand.SelectCommand.Parameters[STR_WHERE_PRAM].Value = strwhere;
try
{
dsCommand.Fill(data);
}
catch
{
data = null;
}
return data;
}
}
三、逻辑层Bussiness.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for Bussiness
/// </summary>
public class Bussiness
{
public Bussiness()
{
//
// TODO: Add constructor logic here
//
}
public DataSet GetNewsAll(int t_pageindex)
{
string table = "news";
string fields = "*";
string fld = "newsid";
int pagesize = 10 ;
int pageindex = t_pageindex;
bool docount = false;
bool ordertype = true;
string strwhere = "";
DataSet result = new DataAccess().ReturnData(table, fields, fld, pagesize, pageindex, docount, ordertype, strwhere);
return result;
}
public DataSet GetbbssAll(int t_pageindex)
{
string table = "bbs";
string fields = "topic";
string fld = "ID";
int pagesize = 10;
int pageindex = t_pageindex;
bool docount = false;
bool ordertype = true;
string strwhere = "";
DataSet result = new DataAccess().ReturnData(table, fields, fld, pagesize, pageindex, docount, ordertype, strwhere);
return result;
}
public String GetRecordcount(string tableName,string idField)
{
string Sqlstring = " select count(" + idField + ") from " + tableName;
DataSet result = new DataAccess().ExecSql(Sqlstring);
return result.Tables[0].Rows[0][0].ToString();
}
}
四、default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string outstr = new Bussiness().GetRecordcount("bbs", "id");
int MaxpageNumber =(int)decimal.Parse(outstr)/10;
ViewState["pageNumber"] = MaxpageNumber.ToString();
this.Literal1.Text = "共" + ViewState["pageNumber"] + "页";
}
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
DataSet data = new Bussiness().GetbbssAll(1);
GridView1.DataSource = data.Tables[0].DefaultView;
GridView1.DataBind();
}
protected void LinkButton2_Click(object sender, EventArgs e)
{
DataSet data = new Bussiness().GetbbssAll(int.Parse(ViewState["pageNumber"].ToString()));
GridView1.DataSource = data.Tables[0].DefaultView;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
int gotopage = int.Parse(TextBox1.Text);
if (gotopage <= 0)
{
gotopage = 0;
}
if (gotopage >= int.Parse(ViewState["pageNumber"].ToString()))
{
gotopage =int.Parse(ViewState["pageNumber"].ToString());
}
DataSet data = new Bussiness().GetbbssAll(gotopage);
GridView1.DataSource = data.Tables[0].DefaultView;
GridView1.DataBind();
}
}