通用SQL存储过程分页以及asp.net后台调用的方法
创建表格并添加300万数据
useStored CREATETABLEUserInfo(--创建表 idintIDENTITY(1,1)PRIMARYKEYnotnull,--添加主键和标识列 UserNamevarchar(50) ) declare@iint--添加3百万数据，大概4分钟时间 set@i=1 while@i<3000000 begin insertintoUserInfo(UserName)values(@i) set@i=@i+1 end
存储过程T-SQL
createPROCEDURE[dbo].[GetDataList] ( @TableNamevarchar(5000),--表名 @Fieldsvarchar(5000)='*',--字段名(全部字段为*) @OrderFieldvarchar(5000),--排序字段(必须!支持多字段) @OrderTypevarchar(5000),--排序类型 @sqlWherevarchar(5000)=Null,--条件语句(不用加where) @pageSizeint,--每页多少条记录 @pageIndexint=1,--指定当前为第几页 @TotalPageintoutput,--返回总页数 @totalRecordintoutput--计算总记录数--返回总记录数 ) as beginBeginTran--开始事务 Declare@sqlnvarchar(500); if(@SqlWhere=''or@sqlWhere=NULL) set@sql='select@totalRecord=count(*)from'+@TableName else set@sql='select@totalRecord=count(*)from'+@TableName+'where'+@sqlWhere EXECsp_executesql@sql,N'@totalRecordintOUTPUT',@totalRecordOUTPUT--计算总记录数 --计算总页数 select@TotalPage=CEILING((@totalRecord+0.0)/@PageSize)if(@SqlWhere=''or@sqlWhere=NULL) set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+''+@Ordertype+')asrowId,'+@Fields+'from'+@TableName else set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+''+@Ordertype+')asrowId,'+@Fields+'from'+@TableName+'where'+@SqlWhere --处理页数超出范围情况 if@PageIndex<=0 Set@pageIndex=1 if@pageIndex>@TotalPage Set@pageIndex=@TotalPage--处理开始点和结束点 Declare@StartRecordint Declare@EndRecordint set@StartRecord=(@pageIndex-1)*@PageSize+1 set@EndRecord=@StartRecord+@pageSize-1--继续合成sql语句 set@Sql=@Sql+')as'+@TableName+'whererowidbetween'+Convert(varchar(50),@StartRecord)+'and'+Convert(varchar(50),@EndRecord) --print@Sql Exec(@Sql) --------------------------------------------------- If@@Error<>0 Begin RollBackTran Return-1 End Else Begin CommitTran Return@totalRecord---返回记录总数 End end --execGetDataList'Userinfo','*','id','desc','',10,1,3,3000000
前台页面Default2.aspx
<%@PageLanguage="C#"AutoEventWireup="true"CodeFile="Default2.aspx.cs"Inherits="Default2"%> <!DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML1.0Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <htmlxmlns="http://www.w3.org/1999/xhtml"> <headrunat="server"> <title></title> </head> <body> <formid="form1"runat="server"> <div> <asp:GridViewID="GridView1"runat="server"> </asp:GridView> <asp:LabelID="lbl_page"runat="server"Text="Label"></asp:Label> </div> </form> </body> </html>
后台CS代码Default2.aspx.cs
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Web;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Configuration;
usingSystem.Text;
publicpartialclassDefault2:System.Web.UI.Page
{
privateintPageIndex=0;//当前页码
privateintPageSize=50;//每页几条记录
privateintTotalPage=1;//总分页数
privateintTotalRecord=0;//总记录
privatestringOrderType="desc";//排序方式默认正序
protectedvoidPage_Load(objectsender,EventArgse)
{
if(!IsPostBack)
{
GetParams();
DataSetds=PageData("UserInfo","*","id",OrderType,"",PageSize,PageIndex,outTotalPage,outTotalRecord);
GridView1.DataSource=ds;
GridView1.DataBind();
lbl_page.Text=GetDivPager("",ds);
}
}
//数据库连接字符
publicstaticstringStrConn()
{
//returnstring.Format("{0}","server=.;database=Stored;user=sa;password=123456");
returnConfigurationSettings.AppSettings["ConnString"].ToString();
}
//Get方式获得下一页
privatevoidGetParams()
{
if(!String.IsNullOrEmpty(Request["page"]))
{
PageIndex=Convert.ToInt32(Request["Page"]);
}
else
{
PageIndex=1;
}
}
#region获得分页字符
publicstringGetDivPager(stringqueryString,DataSetds)
{
StringBuildersp=newStringBuilder();
intTotalCount=TotalRecord;
introwCount=TotalPage;
if(ds!=null)
{
sp.AppendFormat("<p>总记录：<spanid=\"sum\">{0}</span>",TotalCount);
sp.AppendFormat("页码：<em><bid=\"current\">{0}</b>/<spanid=\"count\">{1}</span></em>",PageIndex,rowCount);
sp.AppendFormat("每页：<spanid=\"eachPage\">{0}</span></p>",PageSize);
sp.AppendFormat("<ahref='{0}'>首页</a>","?page=1"+queryString);
if(PageIndex>1)
{
sp.AppendFormat("<ahref='{0}'><上一页</a>","?page="+(PageIndex-1)+queryString);
}
inttemp=0;
intloopc=rowCount>10?10:rowCount;
for(inti=0;i<loopc;i++)
{
temp=i+1;
if(PageIndex>10){temp=(PageIndex-10)+i+1;}
sp.AppendFormat("<aclass=\"{0}\"href='{1}'>{2}</a>",PageIndex==temp?"active":"","?page="+temp+queryString,temp);
}
if(PageIndex!=rowCount)
{
sp.AppendFormat("<ahref='{0}'>下一页></a>","?page="+(PageIndex+1)+queryString);
}
sp.AppendFormat("<ahref='{0}'>尾页</a>","?page="+rowCount+queryString);
}
else
{
ds=null;
}
returnsp.ToString();
}
#endregion
#region获取分页的数据
///<summary>
///获取分页的数据
///</summary>
///<paramname="TblName">数据表名</param>
///<paramname="Fields">要读取的字段</param>
///<paramname="OrderField">排序字段</param>
///<paramname="OrderType">排序方式</param>
///<paramname="SqlWhere">查询条件</param>
///<paramname="PageSize">每页显示多少条数据</param>
///<paramname="pageIndex">当前页码</param>
///<paramname="TotalPage">返回值，共有多少页</param>
///<paramname="TotalRecord">返回值，总有多少条记录</param>
///<returns></returns>
publicstaticDataSetPageData(stringTblName,stringFields,stringOrderField,stringOrderType,stringSqlWhere,intPageSize,intpageIndex,outintTotalPage,outintTotalRecord)
{
SqlConnectionconn=newSqlConnection(StrConn());
SqlCommandcomm=newSqlCommand("GetDataList",conn);
comm.Parameters.Add(newSqlParameter("@TableName",SqlDbType.NVarChar,100)).Value=TblName;
comm.Parameters.Add(newSqlParameter("@Fields",SqlDbType.NVarChar,1000)).Value=Fields;
comm.Parameters.Add(newSqlParameter("@OrderField",SqlDbType.NVarChar,1000)).Value=OrderField;
comm.Parameters.Add(newSqlParameter("@OrderType",SqlDbType.NVarChar,1000)).Value=OrderType;
comm.Parameters.Add(newSqlParameter("@sqlWhere",SqlDbType.NVarChar,1000)).Value=SqlWhere;
comm.Parameters.Add(newSqlParameter("@pageSize",SqlDbType.Int)).Value=PageSize;
comm.Parameters.Add(newSqlParameter("@pageIndex",SqlDbType.Int)).Value=pageIndex;
comm.Parameters.Add(newSqlParameter("@TotalPage",SqlDbType.Int));
comm.Parameters["@TotalPage"].Direction=ParameterDirection.Output;//获得out出来的参数值
comm.Parameters.Add(newSqlParameter("@totalRecord",SqlDbType.Int));
comm.Parameters["@totalRecord"].Direction=ParameterDirection.Output;
comm.CommandType=CommandType.StoredProcedure;
SqlDataAdapterdataAdapter=newSqlDataAdapter(comm);
DataSetds=newDataSet();
dataAdapter.Fill(ds);
TotalPage=(int)comm.Parameters["@TotalPage"].Value;
TotalRecord=(int)comm.Parameters["@totalRecord"].Value;
conn.Close();
conn.Dispose();
comm.Dispose();
returnds;
}
#endregion
}
以上这篇通用SQL存储过程分页以及asp.net后台调用的方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。