笔记139 的GET存储过程解释 2012-5-10

笔记139 的GET存储过程解释 2012-5-10

  1 --的GET存储过程解释 2012-5-10
  2 
  3 --            lblRecordAll.Text = "共" + psi.TotalRecords.ToString() + "条记录";
  4 --            lblPageNum.Text = "共" + _PageCount.ToString() + "页";
  5 --            lblRecordPerPage.Text = "每页" + _PageSize.ToString() + "条";
  6 --            lblCurrentPage.Text = "第" + _CurrentPage.ToString() + "页";
  7 --            txbPageNum.Text = Convert.ToString(_CurrentPage);                前台CurrentPage就是DAL pageindex 存储过程的 @PageIndex
  8 --
  9 --                                                                             前台的PageSize就是DAL的TotalRecords 存储过程的@TotalRecords
 10 -- 前台代码  传入pagesize ,页码 pageindex  后台返回的时候前台自己计算一共有多少页PageCount 
 11 --         PageSetInfo psi = InHouseCardSearch(_PageSize,_CurrentPage);   
 12 --            if (psi.TotalRecords > 0)
 13 --            {
 14 --                if (psi.TotalRecords % _PageSize == 0)
 15 --                    _PageCount = psi.TotalRecords / _PageSize;
 16 --                else
 17 --                    _PageCount = psi.TotalRecords / _PageSize + 1;
 18 --            }
 19 --DAL代码
 20 -- public PageSetInfo Get(int TotalRecords, int PageIndex)
 21 --        {
 22 --            SqlCommand myCommand = new SqlCommand("CT_Append_Get", myConnection);
 23 --
 24 --            myCommand.CommandType = CommandType.StoredProcedure;
 25 --
 26 --            myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Value = TotalRecords;
 27 --            myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
 28 --
 29 --            myConnection.Open();
 30 --
 31 --            PageSetInfo _CT_AppendSetInfo = new PageSetInfo();
 32 --            try
 33 --            {
 34 --                SqlDataReader dr = myCommand.ExecuteReader();
 35 --
 36 --                while (dr.Read())
 37 --                    _CT_AppendSetInfo.CT_AppendInfo.Add(Populate(dr));
 38 --
 39 --                dr.NextResult();
 40 --
 41 --                while (dr.Read())
 42 --                    _CT_AppendSetInfo.TotalRecords = Convert.ToInt32(dr["TotalRecords"]);
 43 --
 44 --                dr.Close();
 45 --            }
 46 --            myConnection.Close();
 47 --
 48 --            return _CT_AppendSetInfo;
 49 --        }
 50 
 51 
 52 --
 53 -- 临时表测试
 54 -- USE GPOSDB
 55 --
 56 -- SET ANSI_NULLS ON
 57 --GO
 58 -- SET QUOTED_IDENTIFIER ON
 59 --GO
 60 ----
 61 -- DECLARE @PageLowerBound INT
 62 -- DECLARE @PageUpperBound INT
 63 -- DECLARE @RowsToReturn INT
 64 -- SET @PageUpperBound = 10
 65 -- SET @PageLowerBound = 20
 66 ---- SET ROWCOUNT 999999999
 67 --
 68 ---- Create a temp table to store the select results
 69 -- CREATE TABLE #PageIndex
 70 --    (
 71 --      IndexId INT IDENTITY(1, 1)
 72 --                  NOT NULL ,
 73 --      VC_A_SNNO VARCHAR(50) --老张这里是使用INT 类型
 74 --    )
 75 --    
 76 -- INSERT INTO #PageIndex
 77 --        ( VC_A_SNNO
 78 --        )
 79 --        SELECT  [VC_A_SNNO]
 80 --        FROM    [CT_Append]
 81 --         
 82 --   
 83 -- SELECT c.VC_A_SNNO ,
 84 --        [VC_A_AppendType] ,
 85 --        [VC_A_CardNO] ,
 86 --        [I_A_CardType] ,
 87 --        [I_A_PointToOil] ,
 88 --        [VC_TicketType] ,
 89 --        [VC_TicketNO] ,
 90 --        [DE_A_BAmount] ,
 91 --        [DE_A_AppendAmount] ,
 92 --        [DE_A_AAmount] ,
 93 --        [D_A_AppendDateTime] ,
 94 --        [VC_A_Remark] ,
 95 --        [VC_A_OperatorNO]
 96 -- FROM   [CT_Append] c ,             --相当于crossjoin
 97 --        #PageIndex PageIndex
 98 -- WHERE  c.VC_A_SNNO = PageIndex.VC_A_SNNO  
 99 --        AND PageIndex.IndexID > @PageLowerBound
100 --        AND PageIndex.IndexID < @PageUpperBound
101 --         
102 -- SELECT *
103 -- FROM   #PageIndex
104 
105 ALTER PROCEDURE [dbo].[CT_Append_Get]
106 (
107      @PageIndex int,
108      @TotalRecords int    --     @TotalRecords就是前台的pagesize
109 )
110 AS
111 BEGIN
112     DECLARE @Page int
113      DECLARE @PageLowerBound int
114      DECLARE @PageUpperBound int
115      DECLARE @RowsToReturn int
116 
117      SET @Page = (@PageIndex - 1)
118 
119      -- First set the rowcount
120      SET @RowsToReturn = @TotalRecords * (@Page + 1)
121      SET ROWCOUNT @RowsToReturn
122 
123      -- Set the page bounds
124      SET @PageLowerBound = @TotalRecords * @Page
125      SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1
126 
127      -- Create a temp table to store the select results
128      CREATE TABLE #PageIndex
129      (
130           IndexId int IDENTITY (1, 1) NOT NULL,
131           VC_A_SNNO int
132      )
133     
134      --Search存储过程在这里就开始不同了
135     
136      INSERT INTO #PageIndex (VC_A_SNNO)
137      SELECT
138           [VC_A_SNNO]
139      FROM
140           [CT_Append]
141          
142    
143      SELECT
144           c.VC_A_SNNO,
145           [VC_A_AppendType] ,
146           [VC_A_CardNO] ,
147           [I_A_CardType] ,
148           [I_A_PointToOil] ,
149           [VC_TicketType] ,
150           [VC_TicketNO] ,
151           [DE_A_BAmount] ,
152           [DE_A_AppendAmount] ,
153           [DE_A_AAmount] ,
154           [D_A_AppendDateTime] ,
155           [VC_A_Remark] ,
156           [VC_A_OperatorNO]
157      FROM [CT_Append] c , #PageIndex PageIndex    --相当于crossjoin
158      WHERE
159           c.VC_A_SNNO = PageIndex.VC_A_SNNO AND
160           PageIndex.IndexID > @PageLowerBound AND
161           PageIndex.IndexID < @PageUpperBound
162     
163      SELECT COUNT(VC_A_SNNO) AS TotalRecords FROM [CT_Append]
164     
165 END

 

posted @ 2013-08-03 23:03  桦仔  阅读(190)  评论(0)    收藏  举报