通用SQL存储过程分页以及asp.net后台调用的方法
程序员文章站
2023-12-13 10:45:28
创建表格并添加300万数据
use stored
create table userinfo( --创建表
id int identity(1,1) prim...
创建表格并添加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" %> <!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:gridview id="gridview1" runat="server"> </asp:gridview> <asp:label id="lbl_page" runat="server" text="label"></asp:label> </div> </form> </body> </html>
后台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(" <p>总记录:<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(" <a 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); } else { ds = null; } return sp.tostring(); } #endregion #region 获取分页的数据 /// <summary> /// 获取分页的数据 /// </summary> /// <param name="tblname">数据表名</param> /// <param name="fields">要读取的字段</param> /// <param name="orderfield">排序字段</param> /// <param name="ordertype">排序方式</param> /// <param name="sqlwhere">查询条件</param> /// <param name="pagesize">每页显示多少条数据</param> /// <param name="pageindex">当前页码</param> /// <param name="totalpage">返回值,共有多少页</param> /// <param name="totalrecord">返回值,总有多少条记录</param> /// <returns></returns> public static dataset pagedata(string tblname, string fields, string orderfield, string ordertype, string sqlwhere, int pagesize, int pageindex, out int totalpage, out int totalrecord) { 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后台调用的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。