ADO.NET复习笔记

-----------------------------------
数据库中写存储过程

CREATE PROC usp_pageSplit2
@pageIndex int,
@pageSize int,
@pageCount int OUTPUT,
@records int output
AS
begin
SELECT @pageCount = COUNT(*) FROM dbo.S
SET @records = CEILING(@pageCount/@pageSize)
SELECT * FROM 
(SELECT ROW_NUMBER() OVER(ORDER BY SNO DESC) AS num , * FROM dbo.S) AS t
WHERE t.num BETWEEN (@pageIndex-1)*@pageSize+1 AND @pageIndex*@pageSize 
END

DECLARE @pc INT ,@rc INT
EXEC usp_pageSplit2 2,3,@pc OUTPUT ,@RC OUTPUT
PRINT @pc
PRINT @rc

　　

-------------------------------------
ADO中调用

 1 private void StoreProces_Load(object sender, EventArgs e)
 2 {
 3 GetData(1);
 4 }
 5 
 6 private int pageIndex = 1;
 7 void GetData(int index)
 8 {
 9 SqlConnection conn = new SqlConnection(@"server=.\SQLEXPRESS;database=studentdb;uid=sa;pwd=123456");
10 SqlCommand comm = new SqlCommand("usp_pageSplit2", conn);
11 //SqlParameter[] para = { 
12 // new SqlParameter("@pageIndex",index),
13 // new SqlParameter("@pageSize",5), 
14 // };
15 SqlParameter p1 = new SqlParameter("@pageIndex",index);
16 SqlParameter p2 = new SqlParameter("@pageSize",3);
17 SqlParameter p3 = new SqlParameter("@pageCount",SqlDbType.Int);
18 SqlParameter p4 = new SqlParameter("@records", SqlDbType.Int);
19 
20 comm.Parameters.Add(p1);
21 comm.Parameters.Add(p2);
22 comm.Parameters.Add(p3);
23 comm.Parameters.Add(p4);
24 
25 p3.Direction = ParameterDirection.Output;
26 p4.Direction = ParameterDirection.Output;
27 
28 SqlDataAdapter sda = new SqlDataAdapter(comm);
29 sda.SelectCommand.CommandType = CommandType.StoredProcedure;
30 // comm.Parameters.AddRange(para);
31 
32 DataTable dt = new DataTable();
33 sda.Fill(dt);
34 this.dataGridView1.DataSource = dt;
35 
36 this.labPageCurrent.Text = index +"/"+comm.Parameters[3].Value.ToString();
37 }
38 
39 private void btnLast_Click(object sender, EventArgs e)
40 {
41 pageIndex--;
42 if (pageIndex<1)
43 {
44 pageIndex = 1;
45 }
46 GetData(pageIndex);
47 }
48 
49 private void btnNext_Click(object sender, EventArgs e)
50 {
51 pageIndex++;
52 if (pageIndex > 3)
53 {
54 pageIndex = 1;
55 }
56 GetData(pageIndex);
57 }
posted @ 2012-09-17 22:14  麦田HH  阅读(313)  评论(0)  编辑  收藏  举报