通用SQL存储过程分页以及asp.net后台调用的方法

来源：动视网责编：小采时间：2020-11-09 20:42:41

通用SQL存储过程分页以及asp.net后台调用的方法

通用SQL存储过程分页以及asp.net后台调用的方法:创建表格并添加300万数据 use Stored CREATE TABLE UserInfo( --创建表 id int IDENTITY(1,1) PRIMARY KEY not null,--添加主键和标识列 UserName varchar(50) ) declare @i int --添加3百万数据，大概4分钟时间 set

推荐度：

点击下载本文 文档为doc格式

导读通用SQL存储过程分页以及asp.net后台调用的方法:创建表格并添加300万数据 use Stored CREATE TABLE UserInfo( --创建表 id int IDENTITY(1,1) PRIMARY KEY not null,--添加主键和标识列 UserName varchar(50) ) declare @i int --添加3百万数据，大概4分钟时间 set

创建表格并添加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后台调用的方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。