数据库复习总结（20）-存储过程以及.net调用存储过程

一、存储过程（注意区分将一段select语句进行封装叫做视图）
（1）将一段t-sql脚本进行封装，以完成一个逻辑操作
（2）创建存储过程：
create proc 名称
参数列表
as
begin
...
end
（3）调用：exec 名称参数列表
（4）可以将参数指定为输出：output，调用时也需要加上output关键字
（5）在ado.net中调用存储过程

--'  abc  '   'abc'去掉空格
declare @temp varchar(10)
set @temp='  abc  '
select LTRIM(RTRIM(@temp))

--创建存储过程
create proc trim1
    @str1 varchar(10)
as
begin
    select LTRIM(RTRIM(@str1))
end

--执行存储过程'abc'
exec trim1 '  abc  '

View Code

--编写存储过程,查询表中的总数据,及当前页的数据
--pageindex,pagesize
create proc GetPageList--create/alter/drop
    @pageIndex int,
    @pageSize int,
    @rowsCount int output
as
begin
    SET NOCOUNT ON;--不会提示有几行受影响
    select @rowsCount=COUNT(*) from StudentInfo where IsDelete=0
    
    select * from 
    (select *,ROW_NUMBER() over(order by sid desc) as rowIndex
    from StudentInfo where IsDelete=0) as t1
    where rowindex between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
end

--执行
declare @temp int
exec GetPageList 1,2,@temp output
print @temp

View Code

二、.net调用存储过程

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;
using System.Windows.Forms.VisualStyles;

namespace t1_ProcTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private int pageIndex,pageSize;
        private void Form1_Load(object sender, EventArgs e)
        {
            pageIndex = 1;//设置默认是第一页
            pageSize = 3;
            LoadList();
        }

        private void LoadList()
        {
            string sql = "GetPageList";//存储过程的名称

            SqlParameter pCount = new SqlParameter("@rowsCount", SqlDbType.Int);
            pCount.Direction = ParameterDirection.Output;//将参数设置为输出

            using (SqlConnection conn = new SqlConnection("server=.;database=dbtest;uid=sa;pwd=123"))
            {
                SqlCommand cmd=new SqlCommand(sql,conn);
                //指定命令类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;

                //根据存储过程来构造参数
                SqlParameter pIndex=new SqlParameter("@pageIndex",pageIndex);
                SqlParameter pSize = new SqlParameter("@pageSize",pageSize);
                
                //为cmd添加参数
                cmd.Parameters.Add(pIndex);
                cmd.Parameters.Add(pSize);
                cmd.Parameters.Add(pCount);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                List<StudentInfo> list=new List<StudentInfo>();
                while (reader.Read())
                {
                    list.Add(new StudentInfo()
                    {
                        Sid = Convert.ToInt32(reader["sid"]),
                        SName = reader["sname"].ToString()
                    });
                }

                dataGridView1.DataSource = list;
                
            }
            //当整个操作执行完成后，连接关闭了，再去获取参数的返回值
            txtCount.Text = pCount.Value.ToString();
        }

        private void 上一页ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            pageIndex--;

            if (pageIndex < 1)
            {
                pageIndex = 1;
            }

            LoadList();
        }

        private void 下一页ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            pageIndex++;

            int rowsCount = int.Parse(txtCount.Text);
            //1.1
            //计算总页数
            //天花板函数（向上取整，注意整数除以整数还是整数天花板函数不能用了，所以先要让其变成浮点数）
            int pageCount = (int) Math.Ceiling(rowsCount*1.0/pageSize);
            //修正页索引，不能超出总页数
            if (pageIndex > pageCount)
            {
                pageIndex = pageCount;
            }

            LoadList();
        }   
    }
}

View Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace t1_ProcTest
{
    public partial class StudentInfo
    {
        public int Sid { get; set; }
        public string SName { get; set; }
    }
}

View Code