笔记141 的GET分页存储过程可以在SSMS里运行的（2012-5-10）
1 --的GET分页存储过程可以在SSMS里运行的（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
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 --@TotalRecords就是前台的pagesize
115
116 DECLARE @Page INT
117 DECLARE @PageLowerBound INT
118 DECLARE @PageUpperBound INT
119 DECLARE @RowsToReturn INT
120 SET @PageIndex = 2
121 SET @TotalRecords = 10
122
123 SET @Page = ( @PageIndex - 1 )
124
125 -- First set the rowcount
126 SET @RowsToReturn = @TotalRecords * ( @Page + 1 )
127 SET ROWCOUNT @RowsToReturn
128
129 -- Set the page bounds
130 SET @PageLowerBound = @TotalRecords * @Page
131 SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1
132
133 -- Create a temp table to store the select results
134 DROP TABLE #PageIndex
135 CREATE TABLE #PageIndex
136 (
137 IndexId INT IDENTITY(1, 1)
138 NOT NULL ,
139 VC_A_SNNO VARCHAR( 50)
140 )
141
142 --Search存储过程在这里就开始不同了
143
144 INSERT INTO #PageIndex
145 ( VC_A_SNNO
146 )
147 SELECT [VC_A_SNNO]
148 FROM [CT_Append]
149
150
151 SELECT
152 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]
