通用SQL存储过程分页以及asp.net后台调用的方法
创建表格并添加300万数据 use StoredCREATE TABLE UserInfo( --创建表id int IDENTITY(1,1) PRIMARY KEY not null,--添加主键和标识列UserName varchar(50) ) declare @i int --添加3百万数据，大概4分钟时间set @i=1while
创建表格并添加300万数据
use Stored CREATE TABLE UserInfo( --创建表 id int IDENTITY(1,1) PRIMARY KEY not null,--添加主键和标识列 UserName varchar(50) )
declare @i int --添加3百万数据，大概4分钟时间 set @i=1 while @i<3000000 begin insert into UserInfo (UserName) values(@i) set @i=@i+1 end
存储过程T-SQL
create PROCEDURE [dbo].[GetDataList] ( @TableName varchar(5000), --表名 @Fields varchar(5000) = '', --字段名(全部字段为) @OrderField varchar(5000), --排序字段(必须!支持多字段) @OrderType varchar(5000), --排序类型 @sqlWhere varchar(5000) = Null, --条件语句(不用加where) @pageSize int, --每页多少条记录 @pageIndex int = 1 , --指定当前为第几页 @TotalPage int output, --返回总页数 @totalRecord int output --计算总记录数 --返回总记录数 ) as begin Begin Tran --开始事务
Declare @sql nvarchar(500);
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count() from ' + @TableName
else
set @sql = 'select @totalRecord = count() from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'Select FROM (select ROW_NUMBER() Over(order by ' + @OrderField +' ' + @Ordertype+' ) as rowId,' + @Fields + ' from ' + @TableName else set @sql = 'Select FROM (select ROW_NUMBER() Over(order by ' + @OrderField +' ' + @Ordertype+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况 if @PageIndex<=0 Set @pageIndex = 1
if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --处理开始点和结束点 Declare @StartRecord int Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句 set @Sql = @Sql + ') as ' + @TableName + ' where rowid between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) --print @Sql
Exec(@Sql)
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
--exec GetDataList 'Userinfo','*','id','desc','',10,1,3,3000000
前台页面Default2.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">;
http://www.w3.org/1999/xhtml">;
后台CS代码Default2.aspx.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Text;
public partial class Default2 : System.Web.UI.Page
{
private int PageIndex = 0;//当前页码
private int PageSize = 50;//每页几条记录
private int TotalPage = 1;//总分页数
private int TotalRecord = 0;//总记录
private string OrderType = " desc";//排序方式 默认正序
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetParams();
DataSet ds = PageData("UserInfo", "*", "id", OrderType, "", PageSize, PageIndex, out TotalPage, out TotalRecord);
GridView1.DataSource = ds;
GridView1.DataBind();
lbl_page.Text = GetDivPager("", ds);
}
}
//数据库连接字符 public static string StrConn() { //return string.Format("{0}","server=.;database=Stored;user=sa;password=123456"); return ConfigurationSettings.AppSettings["ConnString"].ToString(); } //Get方式获得下一页 private void GetParams() { if (!String.IsNullOrEmpty(Request["page"])) { PageIndex = Convert.ToInt32(Request["Page"]); } else { PageIndex = 1; } } #region 获得分页字符 public string GetDivPager(string queryString, DataSet ds) { StringBuilder sp = new StringBuilder(); int TotalCount = TotalRecord; int rowCount = TotalPage; if (ds != null) { sp.AppendFormat("
总记录：{0}", TotalCount); sp.AppendFormat(" 页码：{0}/{1} ", PageIndex, rowCount); sp.AppendFormat(" 每页：{0}
", PageSize); sp.AppendFormat(" <a href='http://news.558idc.com/{0}'>首页</a> ", "?page=1" + queryString);
if (PageIndex > 1)
{
sp.AppendFormat(" <a href='http://news.558idc.com/{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='http://news.558idc.com/{1}'>{2}</a>", PageIndex == temp ? "active" : "", "?page=" + temp + queryString, temp);
}
if (PageIndex != rowCount)
{
sp.AppendFormat(" <a href='http://news.558idc.com/{0}'>下一页 ></a>", "?page=" + (PageIndex + 1) + queryString);
}
sp.AppendFormat(" <a href='http://news.558idc.com/{0}'>尾页</a>", "?page=" + rowCount + queryString);
}
else
{
ds = null;
}
return sp.ToString();}
#endregion
#region 获取分页的数据
///
SqlConnection conn = new SqlConnection(StrConn());
SqlCommand comm = new SqlCommand("GetDataList", conn);
comm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar, 100)).Value = TblName;
comm.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar, 1000)).Value = Fields;
comm.Parameters.Add(new SqlParameter("@OrderField", SqlDbType.NVarChar, 1000)).Value = OrderField;
comm.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.NVarChar, 1000)).Value = OrderType;
comm.Parameters.Add(new SqlParameter("@sqlWhere", SqlDbType.NVarChar, 1000)).Value = SqlWhere;
comm.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = PageSize;
comm.Parameters.Add(new SqlParameter("@pageIndex", SqlDbType.Int)).Value = pageIndex;
comm.Parameters.Add(new SqlParameter("@TotalPage", SqlDbType.Int));
comm.Parameters["@TotalPage"].Direction = ParameterDirection.Output;//获得out出来的参数值
comm.Parameters.Add(new SqlParameter("@totalRecord", SqlDbType.Int));
comm.Parameters["@totalRecord"].Direction = ParameterDirection.Output;
comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
TotalPage = (int)comm.Parameters["@TotalPage"].Value;
TotalRecord = (int)comm.Parameters["@totalRecord"].Value;
conn.Close();
conn.Dispose();
comm.Dispose();
return ds;} #endregion }
以上这篇通用SQL存储过程分页以及asp.net后台调用的方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持每日运维。
