CREATE PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主鍵
@PageSize int = 10, -- 頁面尺寸
@PageIndex int = 1, -- 第幾頁
@IsReCount bit = 0, -- 是否返回記錄總數,0表示不返回
@OrderType bit = 0, -- 是否更具主鍵排序,0表示不
@strWhere varchar(2000) = '' -- 查詢條件 (注意: 不加 where)
AS
declare @strSQL varchar(6000) -- 主語句
declare @strTmp varchar(2000) -- 零時語句
declare @strOrder varchar(400) -- 排序
--------------------------
--可以在這以前判斷有沒有SQL注入式攻擊
--建設中。。。。
--如果有,終止執行。
-------------------------
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * 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) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
begin
set @strSQL = @strSQL+ ' select count(*) as Total from [' + @tblName + ']'
if @strWhere != ''
set @strSQL = @strSQL+ ' where ' + @strWhere
end
exec (@strSQL)
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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;
using BLL;
public partial class fenye : System.Web.UI.Page
{
BLL.Class1B mybll;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind();
}
}
public void Bind()
{
mybll = new Class1B();
int _pageSize=this.grdview.PageSize;
int _page =int.Parse(this.lbl_page_current.Text);
//設置查詢的條件
string strWhere = "";
string iRoleID = this.txtRoleID.Text.Trim();
string iRoleName = this.txtRoleName.Text.Trim();
strWhere = "('" + iRoleID + "'='' or RoleID='" + iRoleID + "') and ('" + iRoleName + "'='' or RoleName='" + iRoleName + "')";
grdview.DataSource = mybll.GetList(_pageSize, _page, strWhere);
grdview.DataBind();
lbl_page_count.Text=(mybll.pageCount).ToString();
lbl_record_count.Text=(mybll.RecordCount).ToString();
}
#region 將讀取的ChangeFlag轉換為容易讀取的中文
public string ChangeFlag(string strDeleteFlag)
{
switch (strDeleteFlag)
{
case "True":
return "是";
break;
default:
return "否";
break;
}
}
#endregion
#region 分頁
public void go_page(int type)
{
//獲取當前頁和總的頁數
int pageCurrent = int.Parse(this.lbl_page_current.Text);
int pageCount = int.Parse(this.lbl_page_count.Text);
switch (type)
{
case 1:
//首頁
pageCurrent = 1;break;
case 3:
//上一頁
if (pageCurrent < pageCount)
{
pageCurrent++;
}
break;
case 2:
//下一頁
if(pageCurrent>1)
{
pageCurrent--;
}
break;
case 4:
//末頁
pageCurrent = pageCount;
break;
}
this.lbl_page_current.Text = pageCurrent.ToString();
Bind();
}
protected void lbl_last_Click(object sender, EventArgs e)
{
go_page(4);
}
protected void lbl_down_Click(object sender, EventArgs e)
{
go_page(3);
}
protected void lbl_up_Click(object sender, EventArgs e)
{
go_page(2);
}
protected void lbl_first_Click(object sender, EventArgs e)
{
go_page(1);
}
protected void txt_goto_TextChanged(object sender, EventArgs e)
{
int pageCurrent = int.Parse(this.lbl_page_current.Text.Trim());
int pageCount = int.Parse(this.lbl_page_count.Text.Trim());
int Pagegoto=int.Parse(this.txt_goto.Text.Trim());
if(Pagegoto<1 || Pagegoto>pageCount)
{
return;
}
this.lbl_page_current.Text = this.txt_goto.Text.ToString();
Bind();
}
#endregion
protected void btnsearch_Click(object sender, EventArgs e)
{
Bind();
}
}