通用SQL存储过程分页以及asp.net后台调用的方法-侯体宗的博客

通用SQL存储过程分页以及asp.net后台调用的方法
数据库 / 管理员发布于 5年前 154

创建表格并添加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 @i<3000000begininsert into UserInfo (UserName) values(@i)set @i=@i+1end

存储过程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    --计算总记录数       --返回总记录数 )asbegin  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='https://www.jb51.net//article/{0}'>首页</a> ", "?page=1" + queryString);      if (PageIndex > 1)      {        sp.AppendFormat(" <a href='https://www.jb51.net//article/{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='https://www.jb51.net//article/{1}'>{2}</a>", PageIndex == temp ? "active" : "", "?page=" + temp + queryString, temp);      }      if (PageIndex != rowCount)      {        sp.AppendFormat(" <a href='https://www.jb51.net//article/{0}'>下一页 ></a>", "?page=" + (PageIndex + 1) + queryString);      }      sp.AppendFormat(" <a href='https://www.jb51.net//article/{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后台调用的方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。

上一条：
SQL查询语句优化的实用方法总结
下一条：
SQL Server COALESCE函数详解及实例

0条评论 (评论内容有缓存机制,请悉知!)