SQL Server存储过程

-----------------1、创建自己的存储过程proc_helloworld--------------------------
create proc proc_helloworld
--alter proc proc_helloworld --已生成的存储过程，如需修改，将create改成alert，选中执行重新生成
as
begin
    print 'hello world'
end
---------鼠标选中以上内容，执行。生成存储过程。先生成再调用------
exec proc_helloworld --调用执行存储过程

-----------------2.1、创建带两个输入参数的存储过程proc_add-----------------------
create proc proc_add
@n1 int, --参数n1，整型
@n2 int  --参数n2，整型
--@n2 int=10  --n2带默认值10
as
begin
    select @n1+@n2
end
---------鼠标选中以上内容，执行。生成存储过程------
exec proc_add 3,5 --3+5
--exec proc_add @n1=3 --3+10
--exec proc_add @n1=3,@n2=11 --3+11，默认值10失效，以新赋值为准

-----------------2.2、创建带输出参数的存储过程proc_ShowStudent----------------
create proc proc_ShowStudent
@gender char(2),
@recordcount int output  --输出参数，记录查到的条数
as
begin
    select * from tb_Student where stGender=@gender
    set @recordcount=(select count(*) from tb_Student where stGender=@gender) --检索的结果条数赋值给recordcount
end
--调用执行存储过程：
declare @rc int --声明变量rc，用来存储recordcount的值
exec proc_ShowStudent @gender='男',@recordcount=@rc output
print @rc --结果为男的条数

-----------------3、选择性别男，年龄>15的学生--------------------------
alter proc proc_SelecteStudent
@gender char(2), --参数类型要与表tb_Student中列stGender一致，参数名随意
@age int  --参数类型要与表tb_Student中列stAge一致，参数名随意
as
begin
    --表tb_Student中有列stAge、stGender
    select * from tb_Student where stAge>=@age and stGender=@gender
end
--调用执行：
exec proc_SelecteStudent @gender='男',@age=15 --选择性别男，年龄>15的学生

-----------------4、分页查询，使用存储过程proc_SelectStudentByPage---------------------
--set nocount off  --不显示结果
--select * from tb_Student

create proc proc_SelectStudentByPage
@pagesize int=7,  --每页记录条数，默认7
@pageindex int=1,  --当前查看第几页，默认1
@recordcount int output,  --总条数，输出参数
@pagecount int output  --总页数，输出参数
as
begin
    --1、查询:
    select --只查询stID、stAge 
        t.stID,
        t.stAge
    from (select *, rn=row_number() over (order by stID asc) from tb_Student) as t
    where t.rn between (@pageindex-1)*@pagesize+1 and @pagesize*@pageindex
    --2、计算总条数：
    set @recordcount=(select count(*) from tb_Student)
    --3、计算总页数：
    set @pagecount=ceiling(@recordcount*1.0/@pagesize) --recordcount*1.0可变为小数，ceiling（天花板）向上取整
end
--调用执行：
--select * from tb_Student
declare @rc int,@pc int --声明rc、pc，存储两个输出参数
exec proc_SelectStudentByPage @recordcount=@rc output,@pagecount=@pc output --得到第1页的结果（显示7条）
--exec proc_SelectStudentByPage @pagesize=7,@pageindex=2, @recordcount=@rc output,@pagecount=@pc output --得到第2页的结果（显示7条）
print @rc  --条数
Print @pc  --页数

一、ado.net调用存储过程与调用带参数的SQL语句的区别：

1、把SQL语句变成了存储过程名

2、设置SqlCommand对象的CommandType属性为CommandType.StoredProcedure

3、根据存储过程的参数来设置SqlCommand对象的参数

4、如果有输出参数，需要设置输出参数的Direction属性，Direction=ParameterDirection.Output

二、如果是通过调用Command对象的ExecuteReader()方法来执行存储过程，那么必须关闭reader对象后，才能获取输出参数。

因为，close之后，才能得到返回值。（类似函数调用结束才能得到返回值）

案例：WinForm实现数据库的翻页

1、建立数据库和表

2、建立存储过程，并生成存储过程proc_SelectStudentByPage

-----------------4、分页查询，使用存储过程proc_SelectStudentByPage---------------------
create proc proc_SelectStudentByPage
@pagesize int=7,  --每页记录条数，默认7
@pageindex int=1,  --当前查看第几页，默认1
@recordcount int output,  --总条数，输出参数
@pagecount int output  --总页数，输出参数
as
begin
    --1、查询:
    select --只查询stID、stAge 
        t.stID,
        t.stAge
    from (select *, rn=row_number() over (order by stID asc) from tb_Student) as t
    where t.rn between (@pageindex-1)*@pagesize+1 and @pagesize*@pageindex
    --2、计算总条数：
    set @recordcount=(select count(*) from tb_Student)
    --3、计算总页数：
    set @pagecount=ceiling(@recordcount*1.0/@pagesize) --recordcount*1.0可变为小数，ceiling（天花板）向上取整
end
--调用执行：
--select * from tb_Student
declare @rc int,@pc int --声明rc、pc，存储两个输出参数
exec proc_SelectStudentByPage @recordcount=@rc output,@pagecount=@pc output --得到第1页的结果（显示7条）
--exec proc_SelectStudentByPage @pagesize=7,@pageindex=2, @recordcount=@rc output,@pagecount=@pc output --得到第2页的结果（显示7条）
print @rc  --条数
Print @pc  --页数

3、建立WinForm项目，SQL_WinForm

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SQL_WinForm
{
    public partial class Form1 : Form
    {
        string strCon = "Data Source=.;Database=db_Student;integrated security=true;";
        private int pageIndex = 1; //当前要查看的页码
        private int pageSize = 7; //每页显示的条数
        private int pageCount; //总页数，存储过程的输出参数
        private int recordCount; //总条数，存储过程的输出参数
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)//加载就显示第1页数据
        {
            LoadData();
        }
        private void LoadData() //根据pageIndex来加载数据
        {
            #region 调用SqlDataAdapter对象来执行存储过程
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter=new SqlDataAdapter("proc_SelectStudentByPage",strCon))
            {
                //告诉SqlCommand对象，现在执行的存储过程，不是SQL语句。等效exec proc_SelectStudentByPage
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                //增加参数，存储过程中有几个参数就增加几个（类似调用函数）
                SqlParameter[] pms = new SqlParameter[]{
                        new SqlParameter("@pagesize",SqlDbType.Int){Value=pageSize},
                        new SqlParameter("@pageindex",SqlDbType.Int){Value=pageIndex},//输入参数要赋值，输出参数不需赋值需指定是输出参数
                        new SqlParameter("@recordcount",SqlDbType.Int){Direction=ParameterDirection.Output},
                        new SqlParameter("@pagecount",SqlDbType.Int){Direction=ParameterDirection.Output}
                    };
                adapter.SelectCommand.Parameters.AddRange(pms);
                adapter.Fill(dt);

                label1.Text = "总条数："+pms[2].Value.ToString();
                label2.Text = "总页数：" + pms[3].Value.ToString();
                label3.Text = "当前页：" +pageIndex;
                //数据绑定
                this.dataGridView1.DataSource = dt;
            }
            #endregion

            #region 调用Command对象的ExecuteReader()方法来执行存储过程
            //using (SqlConnection con=new SqlConnection(strCon))
            //{
            //    string sql = "proc_SelectStudentByPage"; //将sql语句，写成存储过程名
            //    using (SqlCommand cmd=new SqlCommand(sql,con))
            //    {
            //        //告诉SqlCommand对象，现在执行的存储过程，不是SQL语句
            //        cmd.CommandType = CommandType.StoredProcedure;
            //        //增加参数，存储过程中有几个参数就增加几个（类似调用函数）
            //        SqlParameter[] pms = new SqlParameter[]{
            //            new SqlParameter("@pagesize",SqlDbType.Int){Value=pageSize},
            //            new SqlParameter("@pageindex",SqlDbType.Int){Value=pageIndex},//输入参数要赋值，输出参数不需赋值需指定是输出参数
            //            new SqlParameter("@recordcount",SqlDbType.Int){Direction=ParameterDirection.Output},
            //            new SqlParameter("@pagecount",SqlDbType.Int){Direction=ParameterDirection.Output}
            //        };
            //        cmd.Parameters.AddRange(pms); //把参数加进去
            //        //打开连接
            //        con.Open();
            //        //执行
            //        using (SqlDataReader reader=cmd.ExecuteReader())
            //        {
            //            reader.Read();
            //        }
            //        //返回值放到reader之外，读完才有返回值
            //        label1.Text = "总条数：" + pms[2].Value.ToString();
            //        label2.Text = "总页数：" + pms[3].Value.ToString();
            //    }
            //}
            #endregion
        }

        private void button1_Click(object sender, EventArgs e)
        {
            pageIndex--;
            LoadData();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            pageIndex++;
            LoadData();
        }
    }
}