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.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection conn;
private int recordCount; //获得数据总条数
private int pageCount; //总页数 (DataGrid1.PageCount无法取得正确的总页数)
protected void Page_Load(object sender, EventArgs e)
{
conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnStr"]);
GetRecordCount(); //获得数据总条数
if (!(IsPostBack))
{
if (ViewState["PageIndex"] == null)
ViewState["PageIndex"] = 1;
BindGrid();
}
}
void BindGrid()
{
SqlCommand MyCommand=new SqlCommand();
MyCommand.Connection = conn;
MyCommand.CommandText = "prcPageResult"; //在SQL中该存储过程的名称
MyCommand.CommandType=CommandType.StoredProcedure;
//以下是该存储过程中声明的字段要先赋值
MyCommand.Parameters.AddWithValue("@currPage", (int)ViewState["PageIndex"]);
MyCommand.Parameters.AddWithValue("@showColumn ", "OrderID,CustomerID,ShipName");
MyCommand.Parameters.AddWithValue("@tabName ", "Orders");
MyCommand.Parameters.AddWithValue("@strCondition ", "");
MyCommand.Parameters.AddWithValue("@ascColumn ", "OrderID");
MyCommand.Parameters.AddWithValue("@bitOrderType ", "1");
MyCommand.Parameters.AddWithValue("@pkColumn ", "OrderID");
MyCommand.Parameters.AddWithValue("@pageSize ", "200");
conn.Open();
SqlDataReader MyReader = MyCommand.ExecuteReader();
DataGrid1.DataSource = MyReader;
DataGrid1.DataBind();
MyReader.Close();
conn.Close();
SetPageStyle(); //设置分机按扭是否可用
}
private void GetRecordCount()
{
SqlCommand MyCommand2 = new SqlCommand();
MyCommand2.Connection = conn;
MyCommand2.CommandText = "prcRowsCount"; //在SQL中该存储过程的名称
MyCommand2.CommandType = CommandType.StoredProcedure;
//以下是该存储过程中声明的字段要先赋值
MyCommand2.Parameters.AddWithValue("@tabName", "Orders");
MyCommand2.Parameters.AddWithValue("@colName ", "OrderID");
MyCommand2.Parameters.AddWithValue("@condition", "");
conn.Open();
recordCount = (int)MyCommand2.ExecuteScalar();
pageCount = (int)(recordCount / DataGrid1.PageSize) + 1;
conn.Close();
}
public void PagerButtonClick(object sender, EventArgs e)
{
LinkButton btnPager = (LinkButton)sender;
switch (btnPager.CommandName)
{
case "FirstPage":
ViewState["PageIndex"] = 1;
break;
case "PrePage":
ViewState["PageIndex"] = (int)ViewState["PageIndex"] - 1;
break;
case "NextPage":
ViewState["PageIndex"] = (int)ViewState["PageIndex"] + 1;
break;
case "LastPage":
ViewState["PageIndex"] = pageCount;
break;
}
BindGrid();
}
//设置分机按扭是否可用
private void SetPageStyle()
{
if ((int)ViewState["PageIndex"] == 1)
{
firstPage.Enabled = false;
prePage.Enabled = false;
nextPage.Enabled = true;
lastPage.Enabled = true;
}
if ((int)ViewState["PageIndex"] == pageCount)
{
firstPage.Enabled = true;
prePage.Enabled = true;
nextPage.Enabled = false;
lastPage.Enabled = false;
}
if ((int)ViewState["PageIndex"] > 1 && (int)ViewState["PageIndex"] < pageCount)
{
firstPage.Enabled = true;
prePage.Enabled = true;
nextPage.Enabled = true;
lastPage.Enabled = true;
}
}
}
/**//*----------------------------------------------
*procedure name : prcPageResult
* author : FuChun
* create date : 2006-10-04
*/
CREATE PROCEDURE prcPageResult
-- 获得某一页的数据 --
@currPage int = 1, --当前页页码 (即Top currPage)
@showColumn varchar(2000) = '*', --需要得到的字段 (即 column1,column2,......)
@tabName varchar(2000), --需要查看的表名 (即 from table_name)
@strCondition varchar(2000) = '', --查询条件 (即 where condition......) 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '', --主键名称
@pageSize int = 20 --分页大小
AS
BEGIN -- 存储过程开始
-- 该存储过程需要用到的几个变量
DECLARE @strTemp varchar(1000)
DECLARE @strSql varchar(4000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 -- bitOrderType=1即执行降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currPage = 1 -- 如果是第一页
BEGIN
IF @strCondition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @strCondition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END -- 存储过程结束
------------------------------------------------
GO
/**//*----------------------------------------------
*procedure name : prcRowsCount
* author : FuChun
* create date : 2006-09-22
*/
CREATE PROC prcRowsCount
@tabName varchar(200), --需要查询的表名
@colName varchar(200)='*', --需要查询的列名
@condition varchar(200)='' --查询条件
AS
BEGIN
DECLARE @strSql varchar(255)
IF @condition = ''
SET @strSql='select count('+@colName+') from '+@tabName
ELSE
SET @strSql='select count('+@colName+') from '+@tabName+' where '+@condition
EXEC (@strSql)
END
------------------------------------------------
GO
浙公网安备 33010602011771号