亚洲av毛片一区二区久久麻豆_ 分頁(yè)SQLServer存儲過(guò)程

/*--用存儲過(guò)程實(shí)現的分頁(yè)程序

顯示指定表、視圖、查詢(xún)結果的第X頁(yè)
對于表中主鍵或標識列的情況,直接從原表取數查詢(xún)，其它情況使用臨時(shí)表的方法
如果視圖或查詢(xún)結果中有主鍵,不推薦此方法

--鄒建 2003.09--*/

/*--調用示例
exec p_show ‘地區資料‘

exec p_show ‘地區資料‘,5,3,‘地區編號,地區名稱(chēng),助記碼‘,‘地區編號‘
--*/

/*
因為要顧及通用性,所以對帶排序的查詢(xún)語(yǔ)句有一定要求.如果先排序,再出結果.就是:

exec p_show ‘select top 100 percent * from 地區資料 order by 地區名稱(chēng)‘,5,3,‘地區編號,地區名稱(chēng),助記碼‘,‘地區名稱(chēng)‘

--查詢(xún)語(yǔ)句加上:top 100 percent //top時(shí)
*/

if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[p_show]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)
drop procedure [dbo].[p_show]
GO

CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、視圖名、查詢(xún)語(yǔ)句
@PageSize int=10, --每頁(yè)的大小(行數)
@PageCurrent int=1, --要顯示的頁(yè)
@FdShow nvarchar (4000)=‘‘, --要顯示的字段列表,如果查詢(xún)結果有標識字段,需要指定此值,且不包含標識字段
@FdOrder nvarchar (1000)=‘‘ --排序字段列表
as
declare @FdName nvarchar(250) --表中的主鍵或表、臨時(shí)表中的標識列名
,@Id1 varchar(20),@Id2 varchar(20) --開(kāi)始和結束的記錄號
,@Obj_ID int --對象ID
--表中有復合主鍵的處理
declare @strfd nvarchar(2000) --復合主鍵列表
,@strjoin nvarchar(4000) --連接字段
,@strwhere nvarchar(2000) --查詢(xún)條件

select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,‘‘) when ‘‘ then ‘ *‘ else ‘ ‘+@FdShow end
,@FdOrder=case isnull(@FdOrder,‘‘) when ‘‘ then ‘‘ else ‘ order by ‘+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ‘ ‘+@QueryStr else ‘ (‘+@QueryStr+‘) a‘ end

--如果顯示第一頁(yè)，可以直接用top來(lái)完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec(‘select top ‘+@Id1+@FdShow+‘ from ‘+@QueryStr+@FdOrder)
return
end

--如果是表,則檢查表中是否有標識更或主鍵
if @Obj_ID is not null and objectproperty(@Obj_ID,‘IsTable‘)=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))

select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中無(wú)標識列,則檢查表中是否有主鍵
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype=‘PK‘)
goto lbusetemp --如果表中無(wú)主鍵,則用臨時(shí)表處理

select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype=‘PK‘ and parent_obj=@Obj_ID
)))
if @@rowcount>1 --檢查表中的主鍵是否為復合主鍵
begin
select @strfd=‘‘,@strjoin=‘‘,@strwhere=‘‘
select @strfd=@strfd+‘,[‘+name+‘]‘
,@strjoin=@strjoin+‘ and a.[‘+name+‘]=b.[‘+name+‘]‘
,@strwhere=@strwhere+‘ and b.[‘+name+‘] is null‘
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype=‘PK‘ and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp

/*--使用標識列或主鍵為單一字段的處理方法--*/
lbuseidentity:
exec(‘select top ‘+@Id1+@FdShow+‘ from ‘+@QueryStr
+‘ where ‘+@FdName+‘ not in(select top ‘
+@Id2+‘ ‘+@FdName+‘ from ‘+@QueryStr+@FdOrder
+‘)‘+@FdOrder
)
return

/*--表中有復合主鍵的處理方法--*/
lbusepk:
exec(‘select ‘+@FdShow+‘ from(select top ‘+@Id1+‘ a.* from
(select top 100 percent * from ‘+@QueryStr+@FdOrder+‘) a
left join (select top ‘+@Id2+‘ ‘+@strfd+‘
from ‘+@QueryStr+@FdOrder+‘) b on ‘+@strjoin+‘
where ‘+@strwhere+‘) a‘
)
return

/*--用臨時(shí)表處理的方法--*/
lbusetemp:
select @FdName=‘[ID_‘+cast(newid() as varchar(40))+‘]‘
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))

exec(‘select ‘+@FdName+‘=identity(int,0,1),‘+@FdShow+‘
into #tb from‘+@QueryStr+@FdOrder+‘
select ‘+@FdShow+‘ from #tb where ‘+@FdName+‘ between ‘
+@Id1+‘ and ‘+@Id2
)

=============================================================================================

Sqlserver存儲過(guò)程和C#分頁(yè)類(lèi)簡(jiǎn)化你的代碼!
在最近的項目中，由于要用到自定義分頁(yè)的功能，本人就在網(wǎng)上找了個(gè)存儲過(guò)程。結合C#寫(xiě)了個(gè)分頁(yè)類(lèi)。由于本人第一次寫(xiě)文章。寫(xiě)得不好，大家不要扔雞蛋。。

下面是存儲過(guò)程（sqlserver2000下通過(guò))

--最通用的分頁(yè)存儲過(guò)程
-- 獲取指定頁(yè)的數據

CREATE PROCEDURE Pagination

@tblName   varchar(255),       -- 表名

@strGetFields varchar(1000) = ‘*‘, -- 需要返回的列

@fldName varchar(255)=‘‘,      -- 排序的字段名

@PageSize   int = 10,          -- 頁(yè)尺寸

@PageIndex int = 1,           -- 頁(yè)碼

@doCount bit = 0,   -- 返回記錄總數, 非 0 值則返回

@OrderType bit = 0, -- 設置排序類(lèi)型, 非 0 值則降序

@strWhere varchar(1500) = ‘‘ -- 查詢(xún)條件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)       -- 主語(yǔ)句

declare @strTmp   varchar(110)        -- 臨時(shí)變量

declare @strOrder varchar(400)        -- 排序類(lèi)型

if @doCount != 0

begin

   if @strWhere !=‘‘

   set @strSQL = ‘select count(*) as Total from [‘+ @tblName +‘] where ‘+ @strWhere

   else

   set @strSQL = ‘select count(*) as Total from [‘+ @tblName +‘]‘

end

--以上代碼的意思是如果@doCount傳遞過(guò)來(lái)的不是0，就執行總數統計。以下的所有代碼都
--是@doCount為0的情況

else

begin

if @OrderType != 0

begin

   set @strTmp = ‘<(select min‘

set @strOrder = ‘ order by [‘+ @fldName +‘] desc‘

--如果@OrderType不是0，就執行降序，這句很重要！

end

else

begin

   set @strTmp = ‘>(select max‘

   set @strOrder = ‘ order by [‘+ @fldName +‘] asc‘

end

if @PageIndex = 1

begin

   if @strWhere != ‘‘

    set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘+ @tblName +‘] where ‘ + @strWhere + ‘ ‘ + @strOrder

    else

    set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘+ @tblName +‘] ‘+ @strOrder

--如果是第一頁(yè)就執行以上代碼，這樣會(huì )加快執行速度

end

else

begin

--以下代碼賦予了@strSQL以真正執行的SQL代碼

set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘ + @tblName +‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘+ @fldName + ‘])
from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘+ @fldName + ‘] from [‘+ @tblName +‘]‘ + @strOrder + ‘) as tblTmp)‘+ @strOrder

if @strWhere != ‘‘

   set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘+ @tblName +‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘+ @fldName + ‘]) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘+ @fldName + ‘]
from [‘+ @tblName +‘] where ‘ + @strWhere + ‘ ‘ + @strOrder + ‘) as tblTmp) and ‘ + @strWhere + ‘ ‘ + @strOrder

end

end

exec ( @strSQL)
GO

下面是C#的代碼

using System.Data ;
using System.Data.SqlClient ;
using Microsoft.ApplicationBlocks.Data ;
using System.Web ;
using System.Web.UI ;
namespace RssLayer.PageHelper
{
    /**//// <summary>
    /// 分頁(yè)類(lèi)PagerHelper 的摘要說(shuō)明。
    /// </summary>
    public class PagerHelper
    {
        private string connectionString;



        public PagerHelper(string tblname,string sortname,bool docount,string connectionString)
        {
            this.tblName = tblname;
            this.fldName = sortname ;
            this.connectionString = connectionString ;
            this.docount = docount;
        }

        public PagerHelper(string tblname,bool docount,
            string strGetFields,    string fldName,int pagesize,
            int pageindex,bool ordertype,string strwhere,string connectionString
            )
        {
            this.tblName = tblname ;
            this.docount = docount ;
            this.strGetFields = strGetFields ;
            this.fldName = fldName;
            this.pagesize = pagesize ;
            this.pageindex = pageindex;
            this.ordertype = ordertype ;
            this.strwhere = strwhere ;
            this.connectionString = connectionString ;

        }

        /**//// <summary>
        /// 得到記錄集的構造函數
        /// </summary>
        /// <param name="tblname"></param>
        /// <param name="strwhere"></param>
        /// <param name="connectionString"></param>
        public PagerHelper(string tblname,string strwhere,string connectionString)
        {
            this.tblName = tblname;
            this.strwhere = strwhere ;
            this.docount = true;
            this.connectionString = connectionString ;
        }

        private string tblName;
        public string TblName
        {
            get{return tblName;}
            set{tblName =value;}
        }

        private string strGetFields="*";
        public string StrGetFields
        {
            get{return strGetFields ;}
            set{strGetFields =value;}
        }

        private string fldName=string.Empty;
        public string FldName
        {
            get{return fldName ;}
            set{fldName =value;}
        }



        private int pagesize =10;
        public int PageSize
        {
            get{return pagesize ;}
            set{pagesize =value;}
        }

        private int pageindex =1;
        public int PageIndex
        {
            get{return pageindex ;}
            set{pageindex =value;}
        }

        private bool docount=false;
        public bool DoCount
        {
            get{return docount ;}
            set{docount =value;}
        }

        private bool ordertype=false;
        public bool OrderType
        {
            get{return ordertype ;}
            set{ordertype =value;}
        }

        private string strwhere=string.Empty ;
        public string StrWhere
        {
            get{return strwhere ;}
            set{strwhere =value;}
        }





        public IDataReader GetDataReader()
        {

            if(this.docount)
            {
                throw new ArgumentException("要返回記錄集，DoCount屬性一定為false");
            }



        //    System.Web.HttpContext.Current.Response.Write(pageindex);

            return SqlHelper.ExecuteReader(connectionString,CommandType.StoredProcedure,"Pagination",
                new SqlParameter("@tblName",this.tblName),
                new SqlParameter("@strGetFields",this.strGetFields),
                new SqlParameter("@fldName",this.fldName),
                new SqlParameter("@PageSize",this.pagesize),
                new SqlParameter("@PageIndex",this.pageindex),
                new SqlParameter("@doCount",this.docount),
                new SqlParameter("@OrderType",this.ordertype),
                new SqlParameter("@strWhere",this.strwhere)
                );
        }

        public DataSet GetDataSet()
        {
            if(this.docount)
            {
                throw new ArgumentException("要返回記錄集，DoCount屬性一定為false");
            }

            return SqlHelper.ExecuteDataset(connectionString,CommandType.StoredProcedure,"Pagination",
                new SqlParameter("@tblName",this.tblName),
                new SqlParameter("@strGetFields",this.strGetFields),
                new SqlParameter("@fldName",this.fldName),
                new SqlParameter("@PageSize",this.pagesize),
                new SqlParameter("@PageIndex",this.pageindex),
                new SqlParameter("@doCount",this.docount),
                new SqlParameter("@OrderType",this.ordertype),
                new SqlParameter("@strWhere",this.strwhere)
                );
        }

        public int GetCount()
        {
            if(!this.docount)
            {
                throw new ArgumentException("要返回總數統計，DoCount屬性一定為true");
            }



            return (int)SqlHelper.ExecuteScalar(connectionString,CommandType.StoredProcedure,"Pagination",
                new SqlParameter("@tblName",this.tblName),
                new SqlParameter("@strGetFields",this.strGetFields),
                new SqlParameter("@fldName",this.fldName),
                new SqlParameter("@PageSize",this.pagesize),
                new SqlParameter("@PageIndex",this.pageindex),
                new SqlParameter("@doCount",this.docount),
                new SqlParameter("@OrderType",this.ordertype),
                new SqlParameter("@strWhere",this.strwhere)
                );
        }

    }



}

如何調用？？？

假如我已經(jīng)建立了2個(gè)類(lèi)。一個(gè)是FavList數據庫實(shí)體類(lèi)，一個(gè)FavListCollection集合類(lèi)。FavListCollection存儲了FavList實(shí)體類(lèi)的集合。

我可以這樣寫(xiě)一個(gè)方法。

/**//// <summary>
        /// 返回FavList集合,使用存儲過(guò)程自定義分頁(yè)。
        /// </summary>
        /// <param name="userid">數據庫FavList的字段，用戶(hù)ID</param>
        /// <param name="strwhere">查找的條件</param>
        /// <param name="ordertype">排序，true表示Desc,false表示asc</param>
        /// <param name="fldname">排序的字段，只能是一個(gè)字段</param>
        /// <param name="pagesize">每頁(yè)的記錄數</param>
        /// <param name="pageindex">到第幾頁(yè)的參數，由1開(kāi)始。1表示第一頁(yè)，以此類(lèi)推。</param>
        /// <param name="recordcount">總記錄數。</param>
        /// <returns></returns>
        public override FavListCollection GetFavListsByUser(int userid, string strwhere,
            bool ordertype, string fldname, int pagesize,
            int pageindex,out int recordcount
            )
        {
            recordcount = 0;
            PagerHelper helper = new PagerHelper("Vfavlist",strwhere,ConnectionString); //VFavList是View
            recordcount = helper.GetCount();

            PagerHelper helper2 = new PagerHelper("Vfavlist",false," * ",fldname,
                pagesize,pageindex,ordertype,strwhere,ConnectionString);

            IDataReader dr = helper2.GetDataReader();

            FavListCollection list = new FavListCollection();

            while(dr.Read())
            {
                list.Add(PopulateFavList(dr));
            }

            dr.Close();

            return list;
        }
DataGrid調用就不用說(shuō)了吧。。

關(guān)于該分頁(yè)的Bug和局限性

Bug：當排序那個(gè)字段內容相同的時(shí)候（例如：按時(shí)間來(lái)排序，而時(shí)間是一樣的話(huà)。后面的記錄會(huì )顯示不出來(lái)。本人測試過(guò))

局限性：排序只能一個(gè)字段，不能超過(guò)一個(gè)

出處：BLOG永不言拜

本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請點(diǎn)擊舉報。

欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久