1 --张的GET分页存储过程row_number over实现结果集跟张不一样（2012-5-11）
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
106
107 USE GPOSDB
108 SET ANSI_NULLS ON
109 GO
110 SET QUOTED_IDENTIFIER ON
111 GO
112
113 DECLARE @PageIndex INT
114 DECLARE @TotalRecords INT
115 --@TotalRecords就是前台的pagesize
116
117 DECLARE @Page INT
118 DECLARE @PageLowerBound INT
119 DECLARE @PageUpperBound INT
120 DECLARE @RowsToReturn INT
121 SET @PageIndex = 2
122 SET @TotalRecords = 10
123
124 SET @Page = ( @PageIndex - 1 )
125
126 -- First set the rowcount
127 SET @RowsToReturn = @TotalRecords * ( @Page + 1 )
128 SET ROWCOUNT @RowsToReturn
129
130 -- Set the page bounds
131 SET @PageLowerBound = @TotalRecords * @Page
132 SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1
133
134 -- Create a temp table to store the select results
135 DROP TABLE #PageIndex
136 CREATE TABLE #PageIndex
137 (
138 IndexId INT IDENTITY(1, 1)
139 NOT NULL ,
140 VC_A_SNNO VARCHAR(50)
141 )
142
143 --Search存储过程在这里就开始不同了
144
145 --INSERT INTO #PageIndex
146 -- ( VC_A_SNNO
147 -- )
148 -- SELECT [VC_A_SNNO]
149 -- FROM [CT_Append]
150
151
152 --SELECT PageIndex.IndexId ,
153 -- PageIndex.VC_A_SNNO AS PI_VC_A_SNNO ,
154 -- c.VC_A_SNNO AS CT_VC_A_SNNO ,
155 -- [VC_A_AppendType] ,
156 -- [VC_A_CardNO] ,
157 -- [I_A_CardType] ,
158 -- [I_A_PointToOil] ,
159 -- [VC_TicketType] ,
160 -- [VC_TicketNO] ,
161 -- [DE_A_BAmount] ,
162 -- [DE_A_AppendAmount] ,
163 -- [DE_A_AAmount] ,
164 -- [D_A_AppendDateTime] ,
165 -- [VC_A_Remark] ,
166 -- [VC_A_OperatorNO]
167 --FROM [CT_Append] c ,
168 -- #PageIndex PageIndex --相当于crossjoin
169 --WHERE c.VC_A_SNNO = PageIndex.VC_A_SNNO
170 -- AND PageIndex.IndexID > @PageLowerBound
171 -- AND PageIndex.IndexID < @PageUpperBound
172
173 SELECT COUNT(VC_A_SNNO) AS TotalRecords
174 FROM [CT_Append]
175
176
177 SELECT ROWID ,
178 VC_A_SNNO ,
179 [VC_A_AppendType] ,
180 [VC_A_CardNO] ,
181 [I_A_CardType] ,
182 [I_A_PointToOil] ,
183 [VC_TicketType] ,
184 [VC_TicketNO] ,
185 [DE_A_BAmount] ,
186 [DE_A_AppendAmount] ,
187 [DE_A_AAmount] ,
188 [D_A_AppendDateTime] ,
189 [VC_A_Remark] ,
190 [VC_A_OperatorNO]
191 FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY VC_A_SNNO ) AS ROWID ,
192 VC_A_SNNO ,
193 [VC_A_AppendType] ,
194 [VC_A_CardNO] ,
195 [I_A_CardType] ,
196 [I_A_PointToOil] ,
197 [VC_TicketType] ,
198 [VC_TicketNO] ,
199 [DE_A_BAmount] ,
200 [DE_A_AppendAmount] ,
201 [DE_A_AAmount] ,
202 [D_A_AppendDateTime] ,
203 [VC_A_Remark] ,
204 [VC_A_OperatorNO]
205 FROM [CT_Append] c
206 ) AS ProductsWithRowNumbers
207 WHERE RowID > @PageLowerBound
208 AND RowID < @PageUpperBound
