C# 调用带有输出参数的分页存储过程

use StudentMISDB
go
select * from Course
alter table Course
add IsDelete int not null default 0
go
select * from Course where IsDelete=1 --update Course set IsDelete=0 ---循环 添加数据
--declare @index int
--set @index =0
--while(@index<2)
--begin
-- insert into Course select Name,ByName,IsDelete from Course
-- set @index=@index+1;
--end --分页 显示 第 11 到20条的数据 select * from(select ROW_NUMBER() over(order by courseId)as rowNUM, * from Course
where IsDelete=0)as temp
where rowNUM>=11 and rowNUM<=20 select count(1)as rowRount from Course where IsDelete=0 --pageIndex 从 0 开始
--pageSize 每页显示的数据条数【10 条】
--((pageIndex) *pageSize)+1 (pageIndex * pageSize)+pageSize
--11 20 --总页数 pageCount = 总的记录/pageSize
---分页的 要素:1.当前 页码 pageIndex
--2. 每一页的 容量 pageSize
--3. 总条数 count
--4. 总页数 pageCount=count/pageSize --创建分页的存储过程
create proc Proc_GetPageData
@pageIndex int,
@pageSize int,
@rowCount int out,
@pageCount int out
as
begin
select * from(select ROW_NUMBER() over(order by courseId)as rowNUM, * from Course where IsDelete=0)as temp
where rowNUM>(@pageIndex*@pageSize) and rowNUM<= (@pageIndex*@pageSize+@pageSize) select @rowCount =count(1) from Course where IsDelete=0
set @pageCount=@rowCount/@pageSize
end
go declare @pageIndex int,
@pageSize int,
@rowCount int,
@pageCount int --select @pageIndex=3,@pageSize=10
exec Proc_GetPageData @pageIndex,@pageSize,@rowCount out,@pageCount out
--select @rowCount,@pageCount select * from Students
go

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient; namespace Demo01
{
public partial class UseSaveProcess : Form
{
public UseSaveProcess()
{
InitializeComponent();
} private void Form1_Load(object sender, EventArgs e)
{
//数据库连接字符
string connstring = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
//连接数据库
SqlConnection conn = new SqlConnection(connstring);
//调用存储过程
string sql = "Proc_GetPageData";
//定义参数
SqlParameter[] parameter = new SqlParameter[] {
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@rowCount",SqlDbType.Int),
new SqlParameter("@pageCount",SqlDbType.Int)
};
//给参数赋值
parameter[].Value = ;
parameter[].Value = ;
//标明输出参数
parameter[].Direction = ParameterDirection.Output;
parameter[].Direction = ParameterDirection.Output;
//执行命令
SqlCommand cmd = new SqlCommand(sql,conn);
//声明SQL语句是存储过程
cmd.CommandType = CommandType.StoredProcedure;
//添加参数
cmd.Parameters.AddRange(parameter);
//断开式连接查询
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//建立数据集（缓冲区）
DataSet ds = new DataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
//绑定数据源
this.dataGridView1.DataSource = ds.Tables[];
//显示在下方的lable中
int rowcount = Convert.ToInt32(parameter[].Value);
int pagecoun = Convert.ToInt32(parameter[].Value);
this.label1.Text = "总共有" +rowcount+"条数据，共有" +pagecoun+"页";
}
}
}