通用SQL存储过程分页以及asp.net后台调用的方法
程序员文章站
2023-12-17 14:31:04
创建表格并添加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后台调用的方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。