ASP.NET 分页存储过程 及 调用
浏览数：25 /
时间：2015年06月09日
废话不多说，只说代码，及过程
1.创建存储过程
<span style="font-size:18px;">create procedure [dbo].[sp_PagingTabs]
@TableName nvarchar(200), /* 表名 */
@FieldName nvarchar(500), /* 要查询的字段 */
@where nvarchar(500), /* 查询的条件 */
@OrderField nvarchar(500), /* 排序指定的字段 */
@Order nvarchar(50), /* 排序 只能是 asc desc */
@PageIdORField nvarchar(50), /* 指定字段来分页 */
@PageSize int , /* 每页个数 */
@PageIndex int /* 当前页码 */
as
begin
/* 先清除字符串左右的空格 */
set @TableName = LTRIM(rtrim(@TableName));
set @FieldName = LTRIM(RTRIM(@FieldName));
set @where = LTRIM(RTRIM(@where));
set @Order = LTRIM(RTRIM(@Order));
set @OrderField=LTRIM(RTRIM(@OrderField));
set @PageIdORField =LTRIM(rtrim(@PageIdORField));
/* 然后对非空传值进行判断 */
if ISNULL(@TableName,'')='' return ;
if ISNULL(@FieldName,'')='' set @FieldName='*';
if ISNULL(@Order,'')='' set @Order = 'desc';
/* 查询分页的数据 */
declare @sql nvarchar(1000)
set @sql=' select top ' + cast(@PageSize as nvarchar(50)) + ' ' + @FieldName + ' from ' + @TableName + ' where 1=1' + @where
+ ' and ' + @PageIdORField + ' not in(select top ' + cast((@PageSize * (@PageIndex-1)) as nvarchar(50)) + @PageIdORField + ' from ' + @TableName + ' where 1=1 '+
@where + ' order by ' + @OrderField + ' ' + @Order+') order by ' + @OrderField + ' ' + @Order;
/* 总页数 */
set @sql+=' select count(*) from ' + @TableName + ' where 1=1 ' + @where
exec(@sql)
end</span>2.创建方法类
/// <summary>
/// 分页存储过程
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="FieldName">字段名</param>
/// <param name="wheres">where条件</param>
/// <param name="order">只能是desc or asc</param>
/// <param name="PageSize">每页条数</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="TotalCount">总页码</param>
/// <param name="PageIdORField">指定字段来分页</param>
/// <param name="OrderField">排序指定的字段</param>
public static DataSet LinkProce(string TableName, string FieldName, string wheres, string order,string PageIdORField,string OrderField, ref int PageSize,ref int PageIndex)
{
using (SqlConnection conn = new SqlConnection(Conn.ConnString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "sp_PagingTabs";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TableName", (TableName.Trim() == "User" ? "[User]" : TableName));
cmd.Parameters.AddWithValue("@FieldName", FieldName);
cmd.Parameters.AddWithValue("@where", wheres);
cmd.Parameters.AddWithValue("@Order", order);
cmd.Parameters.AddWithValue("@OrderField", OrderField);
cmd.Parameters.AddWithValue("@PageIdORField", PageIdORField);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.AddWithValue("@PageIndex", PageIndex);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds);
return ds;
}
catch
{
return null;
}
finally
{
ds.Dispose();
conn.Close();
}
}
}
}3.调用方法
public DataSet LinkServer(string TableName, string FieldName, string wheres, string order,string PageIdORField,string OrderField, ref int PageSize, ref int PageIndex)
{
return SQLHelperMe.LinkProce(TableName, FieldName, wheres, order, PageIdORField, OrderField,ref PageSize,ref PageIndex);
}4.正式使用
namespace Web.WebForm1
{
public partial class WebForm1 : System.Web.UI.Page
{
private int PageIndex = 0;
public DataTable list;
public string DivPager = "";
protected void Page_Load(object sender, EventArgs e)
{
GetParams();
showData();
}
private void GetParams()
{
if (!String.IsNullOrEmpty(Request["page"]))
{
PageIndex = Convert.ToInt32(Request["Page"]);
}
else
{
PageIndex = 1;
}
}
private void showData()
{
list = new DataTable();
int PageSize = 10;
Pager pager = new Pager(PageIndex);
DivPager = pager.GetDivPager("", pager.LinkServer(" dbo.ze_user ", "*",""," desc "," Id "," Id ",ref PageSize,ref PageIndex), out list);
}
}
}<strong><span style="font-size:24px;">5.</span><span style="font-size:18px; font-family: Arial, Helvetica, sans-serif;">GetDivPager拼接字符串</span></strong>
<span style="font-family: Arial, Helvetica, sans-serif;"> </span>
<span style="font-family: Arial, Helvetica, sans-serif;">queryString 如果需要在URL加参数，比如：&charset=utf-8</span>
<span style="font-family: Arial, Helvetica, sans-serif;">ds 从存储过程的取得数据集</span>
<span style="font-family: Arial, Helvetica, sans-serif;">dt 将列表数据返回</span>
<span style="font-family: Arial, Helvetica, sans-serif;">返回值 HTML标签 </span>
<span style="font-family:Arial, Helvetica, sans-serif;">
</span><span style="font-family: Arial, Helvetica, sans-serif;"></span><pre name="code" class="csharp"> public string GetDivPager(string queryString, DataSet ds, out DataTable dt)
{
StringBuilder sp = new StringBuilder();
int PageSize = 10;
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
int TotalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
int rowCount = (TotalCount % PageSize != 0) ? TotalCount / PageSize + 1 : TotalCount / PageSize;
if (dt != null && dt.Rows.Count > 0)
{
sp.AppendFormat(" <p class=\"fl\">总记录：<span id=\"sum\">{0}</span>", TotalCount);
sp.AppendFormat(" 页码：<em><b id=\"current\">{0}</b>/<span id=\"count\">{1}</span></em> ", PageIndex, rowCount);
sp.AppendFormat(" 每页：<span id=\"eachPage\">{0}</span></p> ", PageSize);
sp.AppendFormat(" <div class=\"pagination fr\"> ");
sp.AppendFormat(" <a class=\"disabled\" href='{0}'>首页</a> ", "?page=1" + queryString);
if (PageIndex > 1)
{
sp.AppendFormat(" <a href='{0}'>< 上一页 </a>", "?page=" + (PageIndex - 1) + queryString);
}
int temp = 0;
int loopc = rowCount > 10 ? 10 : rowCount;
for (int i = 0; i < loopc; i++)
{
temp = i + 1;
if (PageIndex > 10) { temp = (PageIndex - 10) + i + 1; }
sp.AppendFormat(" <a class=\"{0}\" href='{1}'>{2}</a>", PageIndex == temp ? "active" : "", "?page=" + temp + queryString, temp);
}
if (PageIndex != rowCount)
{
sp.AppendFormat(" <a href='{0}'>下一页 ></a>", "?page=" + (PageIndex + 1) + queryString);
}
sp.AppendFormat(" <a href='{0}'>尾页</a>", "?page=" + rowCount + queryString);
sp.AppendFormat(" </div>");
}
}
else
{
dt = null;
}
return sp.ToString();
}6.页面显示
<span style="white-space:pre"> </span><table width="100%">
<tr>
<th>
序号
</th>
<th>
用户名
</th>
<th>
密码
</th>
<th>
创建时间
</th>
</tr>
<%if (list != null && list.Rows.Count > 0)
{
int abc = 1;
foreach (System.Data.DataRow item in list.Rows)
{%>
<tr>
<td><input type="checkbox" name="checkbox2" value="<%=item["ID"] %>" /></td>
<td><%=abc++ %></td>
<td><%=item["UserName"]%></td>
<td><%=item["PASSWORD"]%></td>
<td><%=Convert.ToDateTime(item["CreateTime"]).ToString("yyyy-MM-dd hh:mm:ss") %></td>
</tr>
<%}
}%>
</table>
<!--分页 -->
<div runat="server" id="runPageDiv" class="page"></div>OK！在此大功告成！去浏览你的页面吧。。。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
