欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

通用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后台调用的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。

上一篇:

下一篇: