利用存儲過程分頁

USE [iDB002]

GO

/****** Object:  StoredProcedure [dbo].[PICM_Page]    Script Date: 05/24/2013 14:00:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author       : PICM

-- Create date  : 2013/04/18

-- Description  : 燒錄產能新增

-- =============================================

ALTER PROCEDURE [dbo].[PICM_Page]

 

@SQL            NVARCHAR(4000),

@CurrentPage    int,

@PageSize       int = 10,

@OrderBy        NVARCHAR(400),

@ReturnData   NVARCHAR(50)    OUTPUT

 

AS

BEGIN

    ----初始化----------

    SET @ReturnData='0'

   

    if @CurrentPage = 1

        BEGIN

            exec('select top '+@PageSize+' * from ('+@SQL+') r')

            return

        EnD

    else

        begin

            declare @sqlCount nvarchar(4000)

            declare @sqlReturn nvarchar(4000)

            declare @TotalCount int

            declare @TotalPage int

           

             

            set @sqlCount = 'select @count=count(*) from (' + @SQL + ') r'

           

            exec sp_executesql@sqlCount, N'@count int out', @TotalCount out

           

            set @TotalPage = Ceiling(@TotalCount*1.0/@PageSize)

             

            if @TotalPage < @CurrentPage

                Set @CurrentPage = @TotalPage

             

            SET @ReturnData=cast(@TotalCount as nvarchar)+'_'+cast(@TotalPage as nvarchar)+'_'+cast(@CurrentPage as nvarchar)

           

            SET @sqlReturn = 'select  *  from (select row_number() OVER ('+@OrderBy+') AS

                 row_number,a.* from ('+@SQL+') a

                 ) r where row_number>'+ cast(((@CurrentPage -1)*@PageSize) as nvarchar)+

                 ' and row_number<'+cast((@CurrentPage*@PageSize +1) as nvarchar)+@OrderBy

                 

            exec(@sqlReturn)   

           

            return

        end

       

    IF(@@ERROR<>0)

        BEGIN

            SET @ReturnData='ERR'

            return

        END

END

 

 

using (SqlConnection sqlConn = new SqlConnection(ConnCollection.GetSqlConnStr()))

        {

            sqlConn.Open();

             

            SqlCommand cmd = sqlConn.CreateCommand();

 

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "PICM_Page";

           

            cmd.Parameters.Add("@SQL", SqlDbType.NVarChar,4000).Value = @"select  a.* from HR_Emp_Now a

                 union

                 select  a.* from HR_Emp  a";

 

            cmd.Parameters.Add("@CurrentPage", SqlDbType.Int).Value = 2;

            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = 10;

            cmd.Parameters.Add("@OrderBy", SqlDbType.NVarChar,400).Value = " order by empid desc ";

            cmd.Parameters.Add("@ReturnData", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output;

         

            cmd.ExecuteNonQuery();

            Response.Write(cmd.Parameters["@ReturnData"].Value.ToString());

 

            DataSet ds = new DataSet();

 

            SqlDataAdapter adt = new SqlDataAdapter();

 

            adt.SelectCommand = cmd;

            adt.Fill(ds);

            SqlDataReader dr = cmd.ExecuteReader();

            gv.DataSource = ds.Tables[0];

            gv.DataBind();

                  sqlConn.Close();

            

        }