通用SQL存儲過程分頁以及asp.net后臺調用

2024-07-21 02:46:03

字體：大中小

來源：轉載

供稿：網友

創建表格并添加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

USE [Stored]GO/****** Object:  StoredProcedure [dbo].[GetDataList]    Script Date: 08/17/2015 16:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER  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(4000);        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", SqlD

上一篇：用Sybase advantage清除melinets系統日志

下一篇：SQL2008使用json.net實現XML與JSON互轉