笔记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
