一個實例帶你了解什么是數(shù)據(jù)庫存儲過程

發(fā)布日期：2016年12月01日文章來源：www.cebol.cn

amoxicillin without prescription

amoxicillin 500 mg capsules online buy amoxicillin over the counter

clomid uk success rates

clomid online

buy prednisolone

buy zoloft online

antidepressant sertraline go sertraline online

order abortion pill online usa

abortion pill usa legal

一個網(wǎng)站到了后期都要面臨一個問題，那就是數(shù)據(jù)過多在讀取數(shù)據(jù)時瀏覽器的反應變得遲緩，這是就是數(shù)據(jù)庫存儲過程需要出馬的時候了。編譯后的網(wǎng)站執(zhí)行速度會打打提升因為在數(shù)據(jù)庫中保存的存儲過程語句都是編譯過的，允許模塊化程序設計也就是類似方法的復用，提高系統(tǒng)安全性，減少網(wǎng)絡流通量。這么多好處擺在面前還有不用的道理嗎？

接下來就貼上自己的步驟和代碼吧？

第一步在數(shù)據(jù)庫中新建兩個存儲過程

1、

<!-set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

/*********************************************************

* 作用：數(shù)據(jù)分頁(完整SQL分頁存儲過程（支持多表聯(lián)接）)

* 使用說明：

1、單表調(diào)用方法：EXEC proc_DataPagination @tblName = 'ipa',@fldName = '*',@pageSize =50,@page = 6789,@fldSort = '',@Sort = 1,@strCondition = '',@ID = 'id',@Dist = 0,@pageCount = null,@Counts = NULL

2、多表調(diào)用方法：EXEC proc_DataPagination @tblName = 'Info LEFT JOIN InfoType it ON it.typeid=Info.tid',@fldName = 'info.*,it.tname',@pageSize =50,@page = 1806,@fldSort = '',@Sort = 0,@strCondition = '',@ID = 'id',@Dist = 0, @pageCount = null,@Counts = NULL

* 多表聯(lián)合查詢使用需注意：1、多表中的主鍵字段不能為相同的名稱。2、多表中不能允許具有相同名稱的字段，如果存在相同名稱的字段你可以使用AS重命名

*********************************************************/

create PROCEDURE [dbo].[proc_DataPagination]

(

@TableName VARCHAR(200), --表名

@FieldList VARCHAR(2000), --顯示列名，如果是全部字段則為*

@PrimaryKey VARCHAR(100), --單一主鍵或唯一值鍵

@Where VARCHAR(2000), --查詢條件不含'where'字符，如id>10 and len(userid)>9

@Order VARCHAR(1000), --排序不含'order by'字符，如id asc,userid desc，必須指定asc或desc

--注意當@SortType=3時生效，記住一定要在最后加上主鍵，否則會讓你比較郁悶

@SortType INT, --排序規(guī)則 1:正序asc 2:倒序desc 3:多列排序方法

@RecorderCount INT, --記錄總數(shù) 0:會返回總記錄

@PageSize INT, --每頁輸出的記錄數(shù)

@PageIndex INT, --當前頁數(shù)

@TotalCount INT OUTPUT, --記返回總記錄

@TotalPageCount INT OUTPUT --返回總頁數(shù)

)

SET NOCOUNT ON

IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0

SET @Order = RTRIM(LTRIM(@Order))

SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))

SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')

WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0

BEGIN

SET @Order = REPLACE(@Order,', ',',')

SET @Order = REPLACE(@Order,' ,',',')

END

IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''

OR ISNULL(@PrimaryKey,'') = ''

OR @SortType < 1 OR @SortType >3

OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0

BEGIN

PRINT('ERR_00')

RETURN

END

IF @SortType = 3

BEGIN

IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')

BEGIN PRINT('ERR_02') RETURN END

END

DECLARE @new_where1 VARCHAR(1000)

DECLARE @new_where2 VARCHAR(1000)

DECLARE @new_order1 VARCHAR(1000)

DECLARE @new_order2 VARCHAR(1000)

DECLARE @new_order3 VARCHAR(1000)

DECLARE @Sql VARCHAR(8000)

DECLARE @SqlCount NVARCHAR(4000)

IF ISNULL(@where,'') = ''

BEGIN

SET @new_where1 = ' '

SET @new_where2 = ' WHERE '

END

ELSE

BEGIN

SET @new_where1 = ' WHERE ' + @where

SET @new_where2 = ' WHERE ' + @where + ' AND '

END

IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2

BEGIN

IF @SortType = 1

BEGIN

SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'

SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'

END

IF @SortType = 2

BEGIN

SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'

SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'

END

ELSE

BEGIN

SET @new_order1 = ' ORDER BY ' + @Order

END

IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0

BEGIN

SET @new_order1 = ' ORDER BY ' + @Order

SET @new_order2 = @Order + ','

SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')

SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')

SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)

IF @FieldList <> '*'

BEGIN

SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')

SET @FieldList = ',' + @FieldList

WHILE CHARINDEX(',',@new_order3)>0

BEGIN

IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0

BEGIN

SET @FieldList =

@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))

END

SET @new_order3 =

SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))

END

SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))

END

SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'

+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1

IF @RecorderCount = 0

BEGIN

EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',

@TotalCount OUTPUT,@TotalPageCount OUTPUT

END

ELSE

BEGIN

SELECT @TotalCount = @RecorderCount

END

IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)

BEGIN

SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)

END

IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)

BEGIN

IF @PageIndex = 1 --返回第一頁數(shù)據(jù)

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where1 + @new_order1

END

IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一頁數(shù)據(jù)

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))

+ ' ' + @FieldList + ' FROM '

+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '

+ @new_order1

END

ELSE

BEGIN

IF @SortType = 1 --僅主鍵正序排序

BEGIN

IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向檢索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1

END

ELSE --反向檢索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'SELECT TOP ' + STR(@PageSize) + ' '

+ @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2

+ ' ) AS TMP ' + @new_order1

END

IF @SortType = 2 --僅主鍵反序排序

BEGIN

IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向檢索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey

+' FROM '+ @TableName

+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1

END

ELSE --反向檢索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'SELECT TOP ' + STR(@PageSize) + ' '

+ @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2

+ ' ) AS TMP ' + @new_order1

END

IF @SortType = 3 --多列排序，必須包含主鍵，且放置最后，否則不處理

BEGIN

IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0

BEGIN PRINT('ERR_02') RETURN END

IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向檢索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList

+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '

+ @new_order2 + ' ) AS TMP ' + @new_order1

END

ELSE --反向檢索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList

+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '

+ @new_order1 + ' ) AS TMP ' + @new_order1

END

PRINT(@Sql)

EXEC(@Sql)->

2、

<!-set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[getPagerTopMAX] (

@tblNamenvarchar(2000), ----要顯示的表或多個表的連接,如："myTable"

@IDnvarchar(150), ----主表的主鍵，如："id"

@pageint = 1, ----要顯示那一頁的記錄

@pageSizeint = 10, ----每頁顯示的記錄個數(shù)

@fldNamenvarchar(500) = '*', ----要顯示的字段列表,如："id, cdsinger, cdsingertype, area, cdsingerreadme"

@fldSortnvarchar(200) = @ID, ----排序字段列表或條件

@Sortint = 1, ----排序方法，0為升序，1為降序(如果是多字段排列Sort指代最后一個排序字段的排列順序(最后一個排序字段不加排序標記)--程序傳參如：' SortA Asc,SortB Desc,SortC ')

@strConditionnvarchar(1000) = null, ----查詢條件,不需where,如："and a=1 and b=2"

@Dist int = 0, ----是否添加查詢字段的 DISTINCT 默認0不添加/1添加

@Countsint = 1 output, ----查詢到的記錄數(shù)

@pageCountint = 1 output ----查詢結果分頁后的總頁數(shù)

)

AS begin

SET NOCOUNT ON

Declare @sqlTmp nvarchar(3000) ----存放動態(tài)生成的SQL語句

Declare @strTmp nvarchar(3000) ----存放取得查詢結果總數(shù)的查詢語句

Declare @strID nvarchar(3000) ----存放取得查詢開頭或結尾ID的查詢語句

Declare @strSortType nvarchar(10) ----數(shù)據(jù)排序規(guī)則A

Declare @strFSortType nvarchar(10) ----數(shù)據(jù)排序規(guī)則B

Declare @SqlSelect nvarchar(50) ----對含有DISTINCT的查詢進行SQL構造

Declare @SqlCounts nvarchar(50) ----對含有DISTINCT的總數(shù)查詢進行SQL構造

if @Dist = 0

begin

set @SqlSelect = 'select '

set @SqlCounts = 'Count(*)'

end

else

begin

set @SqlSelect = 'select distinct '

set @SqlCounts = 'Count(DISTINCT '+@ID+')'

end

if @Sort=0

begin

set @strFSortType=' ASC '

set @strSortType=' DESC '

end

else

begin

set @strFSortType=' DESC '

set @strSortType=' ASC '

end

--------生成查詢語句--------

--此處@strTmp為取得查詢結果數(shù)量的語句

if @strCondition is null or @strCondition='' --沒有設置顯示條件

begin

set @sqlTmp = @fldName + ' From ' + @tblName

set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName

set @strID = ' From ' + @tblName

end

else

begin

set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition

set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition

set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition

end

----取得查詢結果總數(shù)量-----

exec sp_executesql @strTmp,N'@Counts int out ',@Counts out

declare @tmpCounts int

if @Counts = 0

set @tmpCounts = 1

else

set @tmpCounts = @Counts

--取得分頁總數(shù)

set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize

/**//**當前頁大于總頁數(shù) 取最后一頁**/

if @page>@pageCount

set @page=@pageCount

--/*-----數(shù)據(jù)分頁2分處理-------*/

declare @pageIndex int --總數(shù)/頁大小

declare @lastcount int --總數(shù)%頁大小

set @pageIndex = @tmpCounts/@pageSize

set @lastcount = @tmpCounts%@pageSize

if @lastcount > 0

set @pageIndex = @pageIndex + 1

else

set @lastcount = @pagesize

--//***顯示分頁

if @strCondition is null or @strCondition='' --沒有設置顯示條件

begin

if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分數(shù)據(jù)處理

begin

if @page=1

set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' order by '+ @fldSort +' '+ @strFSortType

else

begin

set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName

+' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'

+' order by '+ @fldSort +' '+ @strFSortType

end

else

begin

set @page = @pageIndex-@page+1 --后半部分數(shù)據(jù)處理

if @page <= 1 --最后一頁數(shù)據(jù)顯示

set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

else

set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName

+' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'

+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

end

else --有查詢條件

begin

if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分數(shù)據(jù)處理

begin

if @page=1

set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType

else

begin

set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName

+' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'

+' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType

end

else

begin

set @page = @pageIndex-@page+1 --后半部分數(shù)據(jù)處理

if @page <= 1 --最后一頁數(shù)據(jù)顯示

set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

else

set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(20))+' '+ @fldName+' from '+@tblName

+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName

+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'

+' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

end

------返回查詢結果-----

--print @strTmp

exec (@strTmp)

SET NOCOUNT OFF

end

做的這步差不多已經(jīng)結束了，接下來就是貼出自己需要的代碼.

<!-

DataSet ds;

int iPageCount = 0, iCounts = 0, iPageIndex = 0;

iPageIndex = Grid1.PageIndex == 0 ? 1 : Grid1.PageIndex + 1;

sql = "and (Mark<>'2' and Mark<>'1')";

if (!string.IsNullOrEmpty(txtsearch1.Text.Trim()) || !string.IsNullOrEmpty(txtsearch2.Text.Trim()))

{

sql += " and ( REQ between '" + txtsearch1.Text.Trim() + "' and '" + txtsearch2.Text.Trim() + "')";

}

if (!string.IsNullOrEmpty(TextBox1.Text.Trim()))

{

sql += "and Contact like'%" + TextBox1.Text.Trim() + "%' ";

}

if (!string.IsNullOrEmpty(TextBox2.Text.Trim()))

{

sql += "and Tel like'%" + TextBox2.Text.Trim() + "%'";

}

if (ddlUpID2.SelectedValue == "3")

{

}

else

{

switch (ddlUpID2.SelectedValue)

{

case "0":

sql += " and (biaoji ='0' or biaoji is null) ";

break;

case "1":

sql += " and biaoji = '1' ";

break;

}

ds = PubConstant.SelectByPage("GuestBook", "*", "FaqID", sql, " AddTime desc,FaqID desc", 3, Grid1.PageSize, Grid1.PageIndex + 1, out iCounts, out iPageCount);

// 輸出分頁合計結果

Grid1.DataSource = ds;

Grid1.DataBind();

Grid1.RecordCount = iCounts;

上一篇:C#函數(shù)的學習從實踐開始

下一篇:一個小功能讓你DropDownList 控件變的活躍起來：選擇時觸發(fā)事件

返回列表

中文无码乱人伦中文视频在线,免费a级毛片无码免费视频首页,巨胸喷奶水WWW视频网站,丁香花视频资源在线观看

新聞資訊

一個實例帶你了解什么是數(shù)據(jù)庫存儲過程