存儲過程 + AspNetPager 實作分頁

/////////////存儲過程////////////////////////////////////////////////////////////////////////////////////////////

/****** 對象:  StoredProcedure [dbo].[sp_AllQuery]    腳本日期: 10/26/2009 11:27:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

-- [sp_AllQuery] '','','','','','',1,10,0

ALTER procedure [dbo].[sp_AllQuery]

(

@tableName nvarchar(500),

@keyField nvarchar(100),

@keyField_NO nvarchar(100),

@queryField nvarchar(1000),

@orderField nvarchar(200),

@strWhere nvarchar(500),

@startIndex int,

@endIndex int,

@docount bit

)

as

set nocount on

if(@docount=1)

begin

exec('select count(' + @keyField + ') from ' + @tableName + ' where '+ @strWhere +'')

end

else

--declare @indextable table(id int identity(1,1),nid int)

create table #indextable (id int identity(1,1),nid int)

set rowcount @endIndex

exec('insert into #indextable(nid)  select ' + @keyField + ' from ' + @tableName + ' where '+ @strWhere +' order by ' + @orderField +' ')

exec('select t.id as noiid,a.* from (select top 10000000 '+@queryField+' from '+@tableName+' where '+@strWhere+') a,#indextable t where a.' + @keyField_NO +'=t.nid and t.id between ' + @startIndex +' and ' + @endIndex + ' order by t.id ')

drop table #indextable

///////////////////實作代碼///////////////////////////////////////////////////////////////////////////////////////

 ArrayList arr = new ArrayList();

        if (tableName == "")

        {

            tableName = " "; //表名 或 多表連接配接  table1 a left join table2 b on a.id=b.id

        }

        if (keyField == "")

            keyField = " "; //a 表的主鍵  a.id

        if (keyField_NO == "")

            keyField_NO = " ";//a 表的主鍵 ,但寫的時候去掉[a.]  ,直接寫  id     

        if (queryField == "")

            queryField = " "; //查詢的字段  a.id,b.id,a......

        if (orderField == "")

            orderField = " "; //排序字段 a.addtime desc

        if (strWhere == "")

            strWhere = " "; //判斷條件 1=1 and a.isok=1

        arr.Insert(0, tableName);

        arr.Insert(1, keyField);

        arr.Insert(2, keyField_NO);

        arr.Insert(3, queryField);

        arr.Insert(4, orderField);

        arr.Insert(5, strWhere);

        ViewState["QueryStr"] = arr;

        if (ShowPageCount)

            AspNetPager1.CurrentPageIndex = 1;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DataBaseString"].ConnectionString))

            SqlCommand myCmd = new SqlCommand("sp_AllQuery", connection);

            myCmd.CommandType = CommandType.StoredProcedure;

            SqlParameter spParamenter;

            spParamenter = myCmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 500);

            myCmd.Parameters["@tableName"].Value = tableName;

            spParamenter = myCmd.Parameters.Add("@keyField", SqlDbType.NVarChar, 100);

            myCmd.Parameters["@keyField"].Value = keyField;

            spParamenter = myCmd.Parameters.Add("@keyField_NO", SqlDbType.NVarChar, 100);

            myCmd.Parameters["@keyField_NO"].Value = keyField_NO;

            spParamenter = myCmd.Parameters.Add("@queryField", SqlDbType.NVarChar, 1000);

            myCmd.Parameters["@queryField"].Value = queryField;

            spParamenter = myCmd.Parameters.Add("@orderField", SqlDbType.NVarChar, 200);

            myCmd.Parameters["@orderField"].Value = orderField;

            spParamenter = myCmd.Parameters.Add("@strWhere", SqlDbType.NVarChar, 500);

            myCmd.Parameters["@strWhere"].Value = strWhere;

            spParamenter = myCmd.Parameters.Add("@startIndex", SqlDbType.Int);

            myCmd.Parameters["@startIndex"].Value = Convert.ToString((this.AspNetPager1.CurrentPageIndex - 1) * Convert.ToInt32(Cls_System.getWebListCount()) + 1);

            spParamenter = myCmd.Parameters.Add("@endIndex", SqlDbType.Int);

            myCmd.Parameters["@endIndex"].Value = Convert.ToString(this.AspNetPager1.CurrentPageIndex * Convert.ToInt32(Cls_System.getWebListCount()));

            spParamenter = myCmd.Parameters.Add("@docount", SqlDbType.Int);

            myCmd.Parameters["@docount"].Value = 1;

            try

            {

                connection.Open();

                if (ShowPageCount)

                {

                    int totalOrders = (int)myCmd.ExecuteScalar(); //得到總記錄數

                    AspNetPager1.RecordCount = totalOrders;

                    AspNetPager1.CurrentPageIndex = 1;

                    AspNetPager1.PageSize = Convert.ToInt32(Cls_System.getWebListCount());//每頁數量

                }

                myCmd.Parameters["@docount"].Value = 0;

                SqlDataReader dr = myCmd.ExecuteReader();

                this.rep_JobList.DataSource = dr;

                this.rep_JobList.DataBind();

                dr.Close();

            }

            catch

                Page.ClientScript.RegisterStartupScript(this.Page.GetType(), "error1", "<script>alert('網絡異常，請稍候重新整理重試1！');</script>");

                return;

            finally

                connection.Close();

////////////////////////////////////////////////////////////////////////////////////////////////

附件為 aspnetpager.dll