偶尔发现了这篇文章,真不错.
-------------------------------------------存储
CREATE Proc PageShowOne
@PageSize int=10 ,--每页显示的记录数
@PageCurrent int=1 ,--当前要显示的页号
@FdName varchar(100)='' ,--主键名或者标识列名
@SelectStr varchar(2000)='', --select子句,不包含select关键字,如:*或者Id,UserId,UserName等。
@FromStr varchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable
@WhereStr varchar(2000)='', --Where子句,不包含where关键字,如空的,或者 id>2 等
@OrderByStr varchar(1000)='',--order by 子句,不包含order by 子句 ,如id desc,UserId asc 等
@CountRows int=0 output, --返回记录总数
@CountPage int=0 output --返回总页数
as
--------定义局部变量---------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
declare @TmpStr varchar(2000) --临时
----------------------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by '+@OrderByStr
else
set @OrderBySqls = ''
--------
if @WhereStr <> ''
set @WhereSqls = ' where ( '+@WhereStr+')'
else
set @WhereSqls = ''
--------
set @TmpStr = @WhereSqls
--如果显示第一页,可以直接用top来完成
if @PageCurrent<=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+' '+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls)
goto LabelRes
end
---------------------------
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
----------
if @WhereSqls <> ''
set @WhereSqls = @WhereSqls + ' and (' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))'
else
set @WhereSqls = ' where ' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
----------
set @Sqls = 'select top '+@Id1+ ' '+ @SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls
exec (@Sqls)
-----------
LabelRes:
-----返回总记录数
set @Sqls = 'select @a=count(1) from '+@FromStr+@TmpStr
exec sp_executesql @sqls,N'@a int output',@CountRows output
-----返回总页数
if @CountRows <= @PageSize
set @CountPage = 1
else
begin
set @CountPage = @CountRows/@PageSize
if (@CountRows%@PageSize) > 0
set @CountPage = @CountPage + 1
end
return
GO
-------------------------------------------存储结束
-------------------------------------------存储调用
//执行分页式存储过程,返回DataTable
public static DataTable GetDataTableUseProcedure(int PageSize,int PageCurrent,string FdName,string SelectStr,string FromStr,string WhereStr,string OrderByStr,out int CountRows,out int CountPage)
{
try
{
SqlConnection conn = new SqlConnection(ConnString);
//设置参数
SqlCommand cmd = new SqlCommand("PageShowOne", conn);
cmd.CommandType = CommandType.StoredProcedure;
//
cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4));
cmd.Parameters.Add(new SqlParameter("@PageCurrent", SqlDbType.Int, 4));
cmd.Parameters.Add(new SqlParameter("@FdName", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@SelectStr", SqlDbType.VarChar, 2000));
cmd.Parameters.Add(new SqlParameter("@FromStr", SqlDbType.VarChar, 1000));
cmd.Parameters.Add(new SqlParameter("@WhereStr", SqlDbType.VarChar, 2000));
cmd.Parameters.Add(new SqlParameter("@OrderByStr", SqlDbType.VarChar, 1000));
cmd.Parameters.Add(new SqlParameter("@CountRows", SqlDbType.Int, 4));
cmd.Parameters.Add(new SqlParameter("@CountPage", SqlDbType.Int, 4));
//
cmd.Parameters["@PageSize"].Value = PageSize;
cmd.Parameters["@PageCurrent"].Value = PageCurrent;
cmd.Parameters["@FdName"].Value = FdName;
cmd.Parameters["@SelectStr"].Value = SelectStr;
cmd.Parameters["@FromStr"].Value = FromStr;
cmd.Parameters["@WhereStr"].Value = WhereStr;
cmd.Parameters["@OrderByStr"].Value = OrderByStr;
cmd.Parameters["@CountRows"].Direction = ParameterDirection.Output;
cmd.Parameters["@CountPage"].Direction = ParameterDirection.Output;
//设置参数结束
SqlDataAdapter dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
DataSet ds = new DataSet();
//执行
dp.Fill(ds);
//记录总数
CountRows = int.Parse(cmd.Parameters["@CountRows"].Value.ToString().Trim());
//总页数
CountPage = int.Parse(cmd.Parameters["@CountPage"].Value.ToString().Trim());
//
return ds.Tables[0];
}
catch (Exception ex)
{
throw (ex);
}
}
//调用凼数
//get the new list
private void NewsBind(int page)
{
//
string fdname="XZID";
string selectstr = "XZID,XZBiaoti,XZShiJian";
string fromstr = "XZNews";
string wherestr = "";
string rderby="XZShiJian Desc";
if (TB.Text.ToString().Trim() != "")
{
wherestr += "XZBiaoti Like '%" + TB.Text.ToString().Trim() + "%'";
}
if(DRL_XZ.SelectedValue.ToString().Trim()!="")
{
if(wherestr.Trim()!="")
{
wherestr+=" And XZFenlei="+DRL_XZ.SelectedValue.ToString().Trim();
}
else
{
wherestr+="XZFenlei="+DRL_XZ.SelectedValue.ToString().Trim();
}
}
//
int pageitem = BaseClass.PageItem;//每页显示记录数
int countrows = 0;//总记录数
int countpages = 0;//总页数
//
DataTable dt = DBOperate.GetDataTableUseProcedure(pageitem, page, fdname, selectstr, fromstr, wherestr, orderby, out countrows, out countpages);
//DataTable dt1 = DBOperate.GetDataTable("Select CuisineCategory.CuisineCategoryID,Cuisine.CuisineName,Category.CategoryName from Cuisine,Category,CuisineCategory where Cuisine.CuisineID=CuisineCategory.CuisineID and Category.CategoryID=CuisineCategory.CategoryID");
GV_Dish.DataSource = dt.DefaultView;
GV_Dish.DataBind();
//分页设置
LB_Items.Text = countrows.ToString();
LB_Page.Text = page.ToString() + "/" + countpages.ToString();
if (page != 1)
{
HL_HomePage.NavigateUrl = "NewsList.aspx?Page=1";
HL_PrePage.NavigateUrl = "NewsList?Page=" + (page - 1) ;
}
if (page != countpages)
{
HL_NextPage.NavigateUrl = "NewsList?Page=" + (page + 1) ;
HL_LastPage.NavigateUrl = "NewsList?Page=" + countpages ;
}
//下拉分面
ListItem item;
//clear the drl item
DRL_Page.Items.Clear();
for (int i = 1; i <= countpages; i++)
{
item = new ListItem(i.ToString(), i.ToString());
DRL_Page.Items.Add(item);
}//for end
}
-------------------------------------------调用结束
CREATE Proc PageShowOne
@PageSize int=10 ,--每页显示的记录数
@PageCurrent int=1 ,--当前要显示的页号
@FdName varchar(100)='' ,--主键名或者标识列名
@SelectStr varchar(2000)='', --select子句,不包含select关键字,如:*或者Id,UserId,UserName等。
@FromStr varchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable
@WhereStr varchar(2000)='', --Where子句,不包含where关键字,如空的,或者 id>2 等
@OrderByStr varchar(1000)='',--order by 子句,不包含order by 子句 ,如id desc,UserId asc 等
@CountRows int=0 output, --返回记录总数
@CountPage int=0 output --返回总页数
as
--------定义局部变量---------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
declare @TmpStr varchar(2000) --临时
----------------------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by '+@OrderByStr
else
set @OrderBySqls = ''
--------
if @WhereStr <> ''
set @WhereSqls = ' where ( '+@WhereStr+')'
else
set @WhereSqls = ''
--------
set @TmpStr = @WhereSqls
--如果显示第一页,可以直接用top来完成
if @PageCurrent<=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+' '+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls)
goto LabelRes
end
---------------------------
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
----------
if @WhereSqls <> ''
set @WhereSqls = @WhereSqls + ' and (' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))'
else
set @WhereSqls = ' where ' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
----------
set @Sqls = 'select top '+@Id1+ ' '+ @SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls
exec (@Sqls)
-----------
LabelRes:
-----返回总记录数
set @Sqls = 'select @a=count(1) from '+@FromStr+@TmpStr
exec sp_executesql @sqls,N'@a int output',@CountRows output
-----返回总页数
if @CountRows <= @PageSize
set @CountPage = 1
else
begin
set @CountPage = @CountRows/@PageSize
if (@CountRows%@PageSize) > 0
set @CountPage = @CountPage + 1
end
return
GO
-------------------------------------------存储结束
-------------------------------------------存储调用
//执行分页式存储过程,返回DataTable
public static DataTable GetDataTableUseProcedure(int PageSize,int PageCurrent,string FdName,string SelectStr,string FromStr,string WhereStr,string OrderByStr,out int CountRows,out int CountPage)
{
try
{
SqlConnection conn = new SqlConnection(ConnString);
//设置参数
SqlCommand cmd = new SqlCommand("PageShowOne", conn);
cmd.CommandType = CommandType.StoredProcedure;
//
cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4));
cmd.Parameters.Add(new SqlParameter("@PageCurrent", SqlDbType.Int, 4));
cmd.Parameters.Add(new SqlParameter("@FdName", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@SelectStr", SqlDbType.VarChar, 2000));
cmd.Parameters.Add(new SqlParameter("@FromStr", SqlDbType.VarChar, 1000));
cmd.Parameters.Add(new SqlParameter("@WhereStr", SqlDbType.VarChar, 2000));
cmd.Parameters.Add(new SqlParameter("@OrderByStr", SqlDbType.VarChar, 1000));
cmd.Parameters.Add(new SqlParameter("@CountRows", SqlDbType.Int, 4));
cmd.Parameters.Add(new SqlParameter("@CountPage", SqlDbType.Int, 4));
//
cmd.Parameters["@PageSize"].Value = PageSize;
cmd.Parameters["@PageCurrent"].Value = PageCurrent;
cmd.Parameters["@FdName"].Value = FdName;
cmd.Parameters["@SelectStr"].Value = SelectStr;
cmd.Parameters["@FromStr"].Value = FromStr;
cmd.Parameters["@WhereStr"].Value = WhereStr;
cmd.Parameters["@OrderByStr"].Value = OrderByStr;
cmd.Parameters["@CountRows"].Direction = ParameterDirection.Output;
cmd.Parameters["@CountPage"].Direction = ParameterDirection.Output;
//设置参数结束
SqlDataAdapter dp = new SqlDataAdapter();
dp.SelectCommand = cmd;
DataSet ds = new DataSet();
//执行
dp.Fill(ds);
//记录总数
CountRows = int.Parse(cmd.Parameters["@CountRows"].Value.ToString().Trim());
//总页数
CountPage = int.Parse(cmd.Parameters["@CountPage"].Value.ToString().Trim());
//
return ds.Tables[0];
}
catch (Exception ex)
{
throw (ex);
}
}
//调用凼数
//get the new list
private void NewsBind(int page)
{
//
string fdname="XZID";
string selectstr = "XZID,XZBiaoti,XZShiJian";
string fromstr = "XZNews";
string wherestr = "";
string rderby="XZShiJian Desc";
if (TB.Text.ToString().Trim() != "")
{
wherestr += "XZBiaoti Like '%" + TB.Text.ToString().Trim() + "%'";
}
if(DRL_XZ.SelectedValue.ToString().Trim()!="")
{
if(wherestr.Trim()!="")
{
wherestr+=" And XZFenlei="+DRL_XZ.SelectedValue.ToString().Trim();
}
else
{
wherestr+="XZFenlei="+DRL_XZ.SelectedValue.ToString().Trim();
}
}
//
int pageitem = BaseClass.PageItem;//每页显示记录数
int countrows = 0;//总记录数
int countpages = 0;//总页数
//
DataTable dt = DBOperate.GetDataTableUseProcedure(pageitem, page, fdname, selectstr, fromstr, wherestr, orderby, out countrows, out countpages);
//DataTable dt1 = DBOperate.GetDataTable("Select CuisineCategory.CuisineCategoryID,Cuisine.CuisineName,Category.CategoryName from Cuisine,Category,CuisineCategory where Cuisine.CuisineID=CuisineCategory.CuisineID and Category.CategoryID=CuisineCategory.CategoryID");
GV_Dish.DataSource = dt.DefaultView;
GV_Dish.DataBind();
//分页设置
LB_Items.Text = countrows.ToString();
LB_Page.Text = page.ToString() + "/" + countpages.ToString();
if (page != 1)
{
HL_HomePage.NavigateUrl = "NewsList.aspx?Page=1";
HL_PrePage.NavigateUrl = "NewsList?Page=" + (page - 1) ;
}
if (page != countpages)
{
HL_NextPage.NavigateUrl = "NewsList?Page=" + (page + 1) ;
HL_LastPage.NavigateUrl = "NewsList?Page=" + countpages ;
}
//下拉分面
ListItem item;
//clear the drl item
DRL_Page.Items.Clear();
for (int i = 1; i <= countpages; i++)
{
item = new ListItem(i.ToString(), i.ToString());
DRL_Page.Items.Add(item);
}//for end
}
-------------------------------------------调用结束
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21022866/viewspace-592125/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21022866/viewspace-592125/