C# 调用带有输出参数的分页存储过程
一、创建带有输出参数的分页存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 use StudentMISDB 2 go 3 select * from Course 4 alter table Course 5 add IsDelete int not null default 0 6 go 7 select * from Course where IsDelete=1 8 9 --update Course set IsDelete=0 10 11 ---循环 添加数据 12 --declare @index int 13 --set @index =0 14 --while(@index<2) 15 --begin 16 -- insert into Course select Name,ByName,IsDelete from Course 17 -- set @index=@index+1; 18 --end 19 20 --分页 显示 第 11 到20条的数据 21 22 select * from(select ROW_NUMBER() over(order by courseId)as rowNUM, * from Course 23 where IsDelete=0)as temp 24 where rowNUM>=11 and rowNUM<=20 25 26 select count(1)as rowRount from Course where IsDelete=0 27 28 --pageIndex 从 0 开始 29 --pageSize 每页显示的数据条数【10 条】 30 --((pageIndex) *pageSize)+1 (pageIndex * pageSize)+pageSize 31 --11 20 32 33 --总页数 pageCount = 总的记录/pageSize 34 ---分页的 要素:1.当前 页码 pageIndex 35 --2. 每一页的 容量 pageSize 36 --3. 总条数 count 37 --4. 总页数 pageCount=count/pageSize 38 39 --创建分页的存储过程 40 create proc Proc_GetPageData 41 @pageIndex int, 42 @pageSize int, 43 @rowCount int out, 44 @pageCount int out 45 as 46 begin 47 select * from(select ROW_NUMBER() over(order by courseId)as rowNUM, * from Course where IsDelete=0)as temp 48 where rowNUM>(@pageIndex*@pageSize) and rowNUM<= (@pageIndex*@pageSize+@pageSize) 49 50 select @rowCount =count(1) from Course where IsDelete=0 51 set @pageCount=@rowCount/@pageSize 52 end 53 go 54 55 declare @pageIndex int, 56 @pageSize int, 57 @rowCount int, 58 @pageCount int 59 60 --select @pageIndex=3,@pageSize=10 61 exec Proc_GetPageData @pageIndex,@pageSize,@rowCount out,@pageCount out 62 --select @rowCount,@pageCount 63 64 65 66 select * from Students 67 go
二、在C#中对存储过程进行调用,并且进行参数化和断开式连接查询,并利用DataGridView插件将结果显示在窗体中.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 using System.Windows.Forms; 10 using System.Data.SqlClient; 11 12 namespace Demo01 13 { 14 public partial class UseSaveProcess : Form 15 { 16 public UseSaveProcess() 17 { 18 InitializeComponent(); 19 } 20 21 private void Form1_Load(object sender, EventArgs e) 22 { 23 //数据库连接字符 24 string connstring = "server=.;database=StudentMISDB;uid=sa;pwd=123456"; 25 //连接数据库 26 SqlConnection conn = new SqlConnection(connstring); 27 //调用存储过程 28 string sql = "Proc_GetPageData"; 29 //定义参数 30 SqlParameter[] parameter = new SqlParameter[] { 31 new SqlParameter("@pageIndex",SqlDbType.Int), 32 new SqlParameter("@pageSize",SqlDbType.Int), 33 new SqlParameter("@rowCount",SqlDbType.Int), 34 new SqlParameter("@pageCount",SqlDbType.Int) 35 }; 36 //给参数赋值 37 parameter[0].Value = 1; 38 parameter[1].Value = 10; 39 //标明输出参数 40 parameter[2].Direction = ParameterDirection.Output; 41 parameter[3].Direction = ParameterDirection.Output; 42 //执行命令 43 SqlCommand cmd = new SqlCommand(sql,conn); 44 //声明SQL语句是存储过程 45 cmd.CommandType = CommandType.StoredProcedure; 46 //添加参数 47 cmd.Parameters.AddRange(parameter); 48 //断开式连接查询 49 SqlDataAdapter adapter = new SqlDataAdapter(cmd); 50 //建立数据集(缓冲区) 51 DataSet ds = new DataSet(); 52 conn.Open(); 53 adapter.Fill(ds); 54 conn.Close(); 55 //绑定数据源 56 this.dataGridView1.DataSource = ds.Tables[0]; 57 //显示在下方的lable中 58 int rowcount = Convert.ToInt32(parameter[2].Value); 59 int pagecoun = Convert.ToInt32(parameter[3].Value); 60 this.label1.Text = "总共有" +rowcount+"条数据,共有" +pagecoun+"页"; 61 } 62 } 63 }
存在的就是合理的,总有问题要解决!