62,046
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE page
@orderid nvarchar(50),--id
@tablename nvarchar(50),--表名
@term nvarchar(50)='*',--查询条件
@currentpage int,--当前页
@sort int,--1为desc 0为asc排序
@pagesize int,--每页记录
@rowcount int output,--总记录
@pagecount int output--总页数
AS
declare @sql nvarchar(200)
declare @sqlcount nvarchar(100)
declare @sqlsub nvarchar(200)
declare @totalcount nvarchar(200)
declare @temporderid nvarchar(50)
set @sqlcount='select @totalcount=count(*) from '+@tablename--得到总记录
exec sp_executesql @sqlcount,N'@totalcount int out',@rowcount output
if @term is null or @term=''
set @term='*'
if @orderid is null or @orderid=''
set @temporderid=''
else
begin
if @sort=0
set @temporderid='order by '+@orderid+' desc'
else
set @temporderid='order by ' +@orderid+' asc'
end
if @rowcount%@pagesize>0--计算总页数
set @pagecount=(@rowcount/@pagesize)+1
else
set @pagecount=@rowcount/@pagesize
--分页代码
set @sqlsub='SELECT ROW_NUMBER() OVER('+@temporderid+') as rowNUM,* FROM '+@tablename
set @sql= 'SELECT * FROM ('+@sqlsub+')as b where rowNUM BETWEEN' +str((@currentpage-1)*@pagesize+1)+' AND '+str(@currentpage*@pagesize)+' '+@temporderid
exec(@sql)
<asp:DataList ID="DataList1" runat="server" Width="474px">
<ItemTemplate>
<table>
<tr>
<td><%#Eval("id") %></td>
<td><%#Eval("name") %> </td>
<td><%#Eval("message") %></td>
<td><a href="del.aspx?id=<%#Eval("id") %>">删除</a></td>
<td><a href="update.aspx?id=<%#Eval("id") %>"> 修改</a></td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
当前<asp:Label ID="lblCurrent" runat="server"></asp:Label>页
总<asp:Label ID="lblPageCount" runat="server" ></asp:Label>页数/
共<asp:Label ID="lblRowCount" runat="server"></asp:Label>记录
<asp:HyperLink ID="hyFirst" runat="server">首页</asp:HyperLink>
<asp:HyperLink ID="hyPrev" runat="server">上一页</asp:HyperLink>
<asp:HyperLink ID="hyNext" runat="server">下一页</asp:HyperLink>
<asp:HyperLink ID="hyLast" runat="server">末页</asp:HyperLink>
DAL.DET det = new DAL.DET();
public DataTable GetPage( string tablename, int currentpage, int rowcount, int pagesize, int pagecount, string term)
{
return det.GetPage(tablename, currentpage, rowcount, pagesize, pagecount, term);
}
/// <summary>
///
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="param"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string procName, SqlParameter[] param)
{
SqlConnection conn = det.Getconn();
conn.Open();
DataTable dt = new DataTable();
SqlCommand comm = new SqlCommand(procName, conn);
comm.CommandType = CommandType.StoredProcedure;
if (param != null)
{
foreach (SqlParameter sqlparam in param)
{
comm.Parameters.Add(sqlparam);
}
}
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
comm.Parameters.Clear();
return dt;
}
/// <summary>
/// 调用存储过程分页
/// </summary>
/// <param name="id"></param>
/// <param name="tablename"></param>
/// <param name="currentpage"></param>
/// <param name="rowcount"></param>
/// <param name="pagesize"></param>
/// <param name="pagecount"></param>
/// <param name="term"></param>
/// <param name="sort"></param>
/// <returns></returns>
public DataTable GetPage(int id, string tablename, int currentpage, int rowcount, int pagesize, int pagecount, string term,int sort)
{
DataTable dtb = new DataTable();
SqlParameter[] sqlparameter ={
new SqlParameter("@orderid",SqlDbType.NVarChar,50),
new SqlParameter("@tablename",SqlDbType.NVarChar,50),
new SqlParameter("@currentpage",SqlDbType.Int,50),
new SqlParameter("@term",SqlDbType.NVarChar,50),
new SqlParameter("@pagesize",SqlDbType.Int),
new SqlParameter("@sort",SqlDbType.Int),
new SqlParameter("@rowcount",SqlDbType.Int),
new SqlParameter("@pagecount",SqlDbType.Int)
};
sqlparameter[0].Value = id;
sqlparameter[1].Value = tablename;
sqlparameter[2].Value = currentpage;
sqlparameter[3].Value = term;
sqlparameter[4].Value = pagesize;
sqlparameter[5].Value = 1;
sqlparameter[6].Direction = ParameterDirection.Output;
sqlparameter[7].Direction = ParameterDirection.Output;
dtb = ExecuteDataTable("page", sqlparameter);
rowcount = Convert.ToInt32(sqlparameter[6].Value);
pagecount = Convert.ToInt32(sqlparameter[7].Value.ToString());
return dtb;
}
}
}