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中调用

private void StoreProces_Load(object sender, EventArgs e)
{
GetData(1);
}

private int pageIndex = 1;
void GetData(int index)
{
SqlConnection conn = new SqlConnection(@"server=.\SQLEXPRESS;database=studentdb;uid=sa;pwd=123456");
SqlCommand comm = new SqlCommand("usp_pageSplit2", conn);
//SqlParameter[] para = { 
// new SqlParameter("@pageIndex",index),
// new SqlParameter("@pageSize",5), 
// };
SqlParameter p1 = new SqlParameter("@pageIndex",index);
SqlParameter p2 = new SqlParameter("@pageSize",3);
SqlParameter p3 = new SqlParameter("@pageCount",SqlDbType.Int);
SqlParameter p4 = new SqlParameter("@records", SqlDbType.Int);

comm.Parameters.Add(p1);
comm.Parameters.Add(p2);
comm.Parameters.Add(p3);
comm.Parameters.Add(p4);

p3.Direction = ParameterDirection.Output;
p4.Direction = ParameterDirection.Output;

SqlDataAdapter sda = new SqlDataAdapter(comm);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
// comm.Parameters.AddRange(para);

DataTable dt = new DataTable();
sda.Fill(dt);
this.dataGridView1.DataSource = dt;

this.labPageCurrent.Text = index +"/"+comm.Parameters[3].Value.ToString();
}

private void btnLast_Click(object sender, EventArgs e)
{
pageIndex--;
if (pageIndex<1)
{
pageIndex = 1;
}
GetData(pageIndex);
}

private void btnNext_Click(object sender, EventArgs e)
{
pageIndex++;
if (pageIndex > 3)
{
pageIndex = 1;
}
GetData(pageIndex);
}