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

二、在C#中对存储过程进行调用，并且进行参数化和断开式连接查询，并利用DataGridView插件将结果显示在窗体中.

 

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[0].Value = 1;
            parameter[1].Value = 10;
            //标明输出参数
            parameter[2].Direction = ParameterDirection.Output;
            parameter[3].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[0];
            //显示在下方的lable中
            int rowcount = Convert.ToInt32(parameter[2].Value);
            int pagecoun = Convert.ToInt32(parameter[3].Value);
            this.label1.Text = "总共有" +rowcount+"条数据，共有" +pagecoun+"页";
        }
    }
}