笔记141 的GET分页存储过程可以在SSMS里运行的（2012-5-10）

笔记141 的GET分页存储过程可以在SSMS里运行的（2012-5-10）

--的GET分页存储过程可以在SSMS里运行的（2012-5-10）

--            lblRecordAll.Text = "共" + psi.TotalRecords.ToString() + "条记录";
--            lblPageNum.Text = "共" + _PageCount.ToString() + "页";
--            lblRecordPerPage.Text = "每页" + _PageSize.ToString() + "条";
--            lblCurrentPage.Text = "第" + _CurrentPage.ToString() + "页";
--            txbPageNum.Text = Convert.ToString(_CurrentPage);                前台CurrentPage就是DAL pageindex 存储过程的 @PageIndex
--
--                                                                             前台的PageSize就是DAL的TotalRecords 存储过程的@TotalRecords
-- 前台代码  传入pagesize ,页码 pageindex  后台返回的时候前台自己计算一共有多少页PageCount 
--         PageSetInfo psi = InHouseCardSearch(_PageSize,_CurrentPage);   
--            if (psi.TotalRecords > 0)
--            {
--                if (psi.TotalRecords % _PageSize == 0)
--                    _PageCount = psi.TotalRecords / _PageSize;
--                else
--                    _PageCount = psi.TotalRecords / _PageSize + 1;
--            }
--DAL代码
-- public PageSetInfo Get(int TotalRecords, int PageIndex)
--        {
--            SqlCommand myCommand = new SqlCommand("CT_Append_Get", myConnection);
--
--            myCommand.CommandType = CommandType.StoredProcedure;
--
--            myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Value = TotalRecords;
--            myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
--
--            myConnection.Open();
--
--            PageSetInfo _CT_AppendSetInfo = new PageSetInfo();
--            try
--            {
--                SqlDataReader dr = myCommand.ExecuteReader();
--
--                while (dr.Read())
--                    _CT_AppendSetInfo.CT_AppendInfo.Add(Populate(dr));
--
--                dr.NextResult();
--
--                while (dr.Read())
--                    _CT_AppendSetInfo.TotalRecords = Convert.ToInt32(dr["TotalRecords"]);
--
--                dr.Close();
--            }
--            myConnection.Close();
--
--            return _CT_AppendSetInfo;
--        }


--
-- 临时表测试
-- USE GPOSDB
--
-- SET ANSI_NULLS ON
--GO
-- SET QUOTED_IDENTIFIER ON
--GO
----
-- DECLARE @PageLowerBound INT
-- DECLARE @PageUpperBound INT
-- DECLARE @RowsToReturn INT
-- SET @PageUpperBound = 10
-- SET @PageLowerBound = 20
---- SET ROWCOUNT 999999999
--
---- Create a temp table to store the select results
-- CREATE TABLE #PageIndex
--    (
--      IndexId INT IDENTITY(1, 1)
--                  NOT NULL ,
--      VC_A_SNNO VARCHAR(50) --老张这里是使用INT 类型
--    )
--    
-- INSERT INTO #PageIndex
--        ( VC_A_SNNO
--        )
--        SELECT  [VC_A_SNNO]
--        FROM    [CT_Append]
--         
--   
-- SELECT c.VC_A_SNNO ,
--        [VC_A_AppendType] ,
--        [VC_A_CardNO] ,
--        [I_A_CardType] ,
--        [I_A_PointToOil] ,
--        [VC_TicketType] ,
--        [VC_TicketNO] ,
--        [DE_A_BAmount] ,
--        [DE_A_AppendAmount] ,
--        [DE_A_AAmount] ,
--        [D_A_AppendDateTime] ,
--        [VC_A_Remark] ,
--        [VC_A_OperatorNO]
-- FROM   [CT_Append] c ,             --相当于crossjoin
--        #PageIndex PageIndex
-- WHERE  c.VC_A_SNNO = PageIndex.VC_A_SNNO  
--        AND PageIndex.IndexID > @PageLowerBound
--        AND PageIndex.IndexID < @PageUpperBound
--         
-- SELECT *
-- FROM   #PageIndex



USE GPOSDB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    
DECLARE @PageIndex INT
DECLARE @TotalRecords INT     --@TotalRecords就是前台的pagesize

DECLARE @Page INT
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @RowsToReturn INT
SET @PageIndex = 2
SET @TotalRecords = 10

SET @Page = ( @PageIndex - 1 )

     -- First set the rowcount
SET @RowsToReturn = @TotalRecords * ( @Page + 1 )
SET ROWCOUNT @RowsToReturn

     -- Set the page bounds
SET @PageLowerBound = @TotalRecords * @Page
SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1

     -- Create a temp table to store the select results
     DROP TABLE #PageIndex
CREATE TABLE #PageIndex
    (
      IndexId INT IDENTITY(1, 1)
                  NOT NULL ,
      VC_A_SNNO VARCHAR( 50)
    )
    
     --Search存储过程在这里就开始不同了
    
INSERT  INTO #PageIndex
        ( VC_A_SNNO
        )
        SELECT  [VC_A_SNNO]
        FROM    [CT_Append]
         
   
SELECT 
        PageIndex.IndexId ,
        PageIndex.VC_A_SNNO AS PI_VC_A_SNNO,
        c.VC_A_SNNO AS CT_VC_A_SNNO,
        [VC_A_AppendType] ,
        [VC_A_CardNO] ,
        [I_A_CardType] ,
        [I_A_PointToOil] ,
        [VC_TicketType] ,
        [VC_TicketNO] ,
        [DE_A_BAmount] ,
        [DE_A_AppendAmount] ,
        [DE_A_AAmount] ,
        [D_A_AppendDateTime] ,
        [VC_A_Remark] ,
        [VC_A_OperatorNO]
FROM    [CT_Append] c ,
        #PageIndex PageIndex    --相当于crossjoin
WHERE   c.VC_A_SNNO = PageIndex.VC_A_SNNO
        AND PageIndex.IndexID > @PageLowerBound
        AND PageIndex.IndexID < @PageUpperBound
    
SELECT  COUNT(VC_A_SNNO) AS TotalRecords
FROM    [CT_Append]