C# 高阶语法 —— Winfrom链接SQL数据库的存储过程

存储过程在应用程序端的使用的优点

        1 如果sql语句直接写在客户端，以一个字符串的形式体现的，提示不友好，会导致效率降低
        2 sql语句写在客户端，可以利用sql注入进行攻击，为了安全性，可以把sql封装在服务器存储过程，在客户端进行

SQL自定义无参数存储过程：

创建存储过程 usp_ScoreQuery1
-- 查询考试成绩，显示：学号、姓名、班级、总成绩，并按成绩的总分高低排序。
-- 统计分析考试成绩，显示班级名称、C#平均分、数据库平均分，按照班级分组实现。

 use SMDB
 go
 if exists (select * from sysobjects where name = 'usp_ScoreQuery1')
 drop procedure usp_ScoreQuery1
 go
 create procedure usp_ScoreQuery1
 as
 	   -- 查询考试成绩
 	   select Students.StudentId,StudentName,ClassName, ScoreSum = (CSharp + SqlserverDB) from Students
 	   inner join StudentClass on StudentClass.ClassId = Students.ClassId
 	   inner join ScoreList on ScoreList.StudentId = Students.StudentId
 	   order by ScoreSum DESC
 	   -- 分析考试信息
 	   select ClassName,C#Avg=AVG(CSharp),DBAvg=AVG(SqlserverDB) from ScoreList
 	   inner join Students on Students.StudentId = ScoreList.StudentId
 	   inner join StudentClass on StudentClass.ClassId = Students.ClassId
 	   group by ClassName
 	   order by ClassName
 go

针对上面实现的效果，继续修改，改为参数可以带默认值 

use SMDB
go
if exists (select * from sysobjects where name = 'usp_ScoreQuery4')
drop procedure usp_ScoreQuery4
go
create procedure usp_ScoreQuery4
	-- 带默认值
	@CSharp int = 60,
	@SqlserverDB int = 60
as
	select Students.StudentId,StudentName,CSharp as C#,SqlserverDB as DB from ScoreList
	inner join Students on Students.StudentId = ScoreList.StudentId
	where CSharp < @CSharp or SqlserverDB < @SqlserverDB
go

-- 调用参数带默认值的存储过程
use SMDB
go
exec usp_ScoreQuery4 -- 两个都走的默认值
exec usp_ScoreQuery4 70,80 -- 第二个参数取默认值
exec usp_ScoreQuery4 @SqlserverDB = 70 -- 第一个参数取默认值
exec usp_ScoreQuery4 default,70 -- 第一个参数取默认值(同上)

3. 自定义带输出参数的存储过程
-- 问题：查询考试成绩，要求自定义分数线，显示查询列表，并输出缺考总人数、不及格总人数?

use SMDB
go
if exists (select * from sysobjects where name = 'usp_ScoreQuery5')
drop procedure usp_ScoreQuery5
go
create procedure usp_ScoreQuery5
	-- 输出参数（习惯：输出参数放在输入参数的前面）
	@AbsentCount int output, -- 缺考总人数
	@FailCount int output, -- 不及格总人数
	-- 输入参数
	@CSharp int = 60, -- CSharp分数线
	@SqlserverDB int = 60 -- SqlserverDB分数线
as
	-- 查询考试成绩，要求自定义分数线
	select Students.StudentId,StudentName,CSharp as C#,SqlserverDB as DB from ScoreList
	inner join Students on Students.StudentId = ScoreList.StudentId
	where CSharp < @CSharp or SqlserverDB < @SqlserverDB
	-- 缺考总人数
	select @AbsentCount = count(*) from Students where StudentId not in (select StudentId from ScoreList)
	-- 不及格总人数
	select @FailCount = count(*) from ScoreList where CSharp < @CSharp or SqlserverDB < @SqlserverDB
go

-- 调用参数带默认值的存储过程
use SMDB
go
-- 首先定义输出参数（命名可以和上面的保持一致，不一样也行）
declare @AbsentCount int, @FailCount int
-- 调用存储过程时，输出参数后面也必须加上 output 关键字
exec usp_ScoreQuery5 @AbsentCount output,@FailCount output 
select @AbsentCount as 缺考总人数,@FailCount as 不及格总人数
-- 打印一下结果
print @AbsentCount
print @FailCount

链接SQL数据库

public string connString = @"Server=.;DataBase=SMDB;Uid=sa;Pwd=123456";

 搭建Winfrom窗体 

一：无参数的存储过程的调用 

  不带参数存储过程的使用 

        1指定存储过程名称
        string proceName = "usp_ScoreQuery5";
        2创建指令对象传递连接对象和存储过程
        SqlCommand cmd = new SqlCommand(proceName,conn);
        3 指定指令执行类型
        cmd.CommandType = CommandType.StoredProcedure;
        4 执行指令
        SqlDataReader dr = cmd.ExecuteReader();

 private void button1_Click(object sender, EventArgs e)
 {
     //1 定义存储过程名称
     string proceName = "usp_ScoreQuery1";
     //2 创建一个指令对象
     SqlCommand cmd = new SqlCommand();
     //3 添加执行sql语句和设置连接对象
     cmd.CommandText = proceName;// 添加执行的sql
     cmd.Connection = conn; //设置连接对象
     //4 执行命令类型
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     // 5 取出数据
     List<Model1> model1s = new List<Model1>();// 存储第一个表的数据
     List<Model2> models2 = new List<Model2>();//存储第二个表的数据
     try
     {
         SqlDataReader dr = cmd.ExecuteReader(); // 读取数据库数据
         while (dr.Read())
         {
             model1s.Add(new Model1() { 
                 StudentId =Convert.ToInt32( dr["StudentId"]),
                 StudentName = dr["StudentName"].ToString(),
                 ClassName = dr["ClassName"].ToString(),
                 ScoreNum = Convert.ToInt32(dr["ScoreSum"])
             });
         }
         //如果有第二个数据源 读取第二个select
         if (dr.NextResult())
         {
             while (dr.Read())
             {
                 models2.Add(new Model2() {
                     ClassName = dr["ClassName"].ToString(),
                     CSharpAvg = Convert.ToInt32(dr["C#Avg"]),
                     DBAvg   = Convert.ToInt32(dr["DBAvg"]),
                 });
             }
         }
         dr.Close();
         this.dataGridView1.DataSource = model1s;
         this.dataGridView2.DataSource = models2;
     }
     catch
     {
         throw;
     }
 }

二：带输入参数的存储过程的使用

带输入参数的存储过程的调用

        1指定存储过程名称
                string proceName = "usp_ScoreQuery5";
        2创建指令对象传递连接对象和存储过程
                SqlCommand cmd = new SqlCommand(proceName,conn);
        3 指定指令执行类型
                cmd.CommandType = CommandType.StoredProcedure;
        4 添加输入参数
                cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@CSharp",
                        Direction = ParameterDirection.Input,
                        SqlDbType = SqlDbType.Int,
                        Value = 80
                    });
        5执行指令
                SqlDataReader dr = cmd.ExecuteReader();

private void button2_Click(object sender, EventArgs e)
{
    //1 指明存储过程
    string proceName = "usp_ScoreQuery4";
    //2 创建指令对象
    SqlCommand cmd = new SqlCommand();
    //3 设置连接和设置执行过程
    cmd.Connection = conn; 
    cmd.CommandText = proceName;
    //4  执行类型
    cmd.CommandType = CommandType.StoredProcedure;

    //5 设置输入参数
    //定义参数方法1
    SqlParameter csharp = new SqlParameter();
    csharp.ParameterName = "@CSharp";// 设置csharp是存储过程中对应@CSharp输入参数
    csharp.Direction = ParameterDirection.Input;//设置为输入参数
    csharp.Value = 180;// 设置参数的值为70
    csharp.SqlDbType = SqlDbType.Int; //设置参数类型
    cmd.Parameters.Add(csharp); // 把输入参数添加到参数列表里面

    // 定义参数方法2 @SqlserverDB
    cmd.Parameters.Add(new SqlParameter() { 
        ParameterName = "@SqlserverDB",
        Direction = ParameterDirection.Input,
        Value = 180,
        SqlDbType = SqlDbType.Int,
    });

    // 6 获取数据
    List<Model3> list = new List<Model3>();
    try
    {
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            list.Add(new Model3()
            {
                StudentId = Convert.ToInt32(dr["StudentId"]),
                StudentName = dr["StudentName"].ToString(),
                CSharp = Convert.ToInt32(dr["C#"]),
                DB = Convert.ToInt32(dr["DB"]),
            });
        }
        dr.Close();
        this.dataGridView1.DataSource = list;
    }
    catch
    {
        throw;
    }
}

三：调用带输出参数的存储过程 对应的是model3对象

带输入参数带输出参数的调用

添加输出参数即可
     cmd.Parameters.Add(new SqlParameter()
            {
                ParameterName = "@AbsentCount",
                Direction = ParameterDirection.Output,
                SqlDbType = SqlDbType.Int,
            });
   获取输出参数的值
        cmd.Parameters["@AbsentCount"].Value.ToString()

private void button3_Click(object sender, EventArgs e)
{
    string proceName = "usp_ScoreQuery5";
    SqlCommand cmd = new SqlCommand(proceName,conn);
    //cmd.CommandText = proceName;
    //cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;

    //设置输出参数
    cmd.Parameters.Add(new SqlParameter() { 
        ParameterName= "@AbsentCount",
        Direction = ParameterDirection.Output,
        SqlDbType=SqlDbType.Int,
    });
    cmd.Parameters.Add(new SqlParameter()
    {
        ParameterName = "@FailCount",
        Direction = ParameterDirection.Output,
        SqlDbType = SqlDbType.Int,
    });
    //输入参数
    cmd.Parameters.Add(new SqlParameter()
    {
        ParameterName = "@CSharp",
        Direction = ParameterDirection.Input,
        SqlDbType = SqlDbType.Int,
        Value = 80
    });
    cmd.Parameters.Add(new SqlParameter()
    {
        ParameterName = "@SqlserverDB",
        Direction = ParameterDirection.Input,
        SqlDbType = SqlDbType.Int,
        Value = 80
    });

    //取数据
    List<Model3> list = new List<Model3>();
    SqlDataReader dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        list.Add(new Model3()
        {
            StudentId = Convert.ToInt32(dr["StudentId"]),
            StudentName = dr["StudentName"].ToString(),
            CSharp = Convert.ToInt32(dr["C#"]),
            DB = Convert.ToInt32(dr["DB"]),
        });
    }
    dr.Close();
    this.dataGridView1.DataSource = list;
    //输出参数的值怎么取？ 缺考总人数? 不及格人数?
    this.label1.Text = "缺考总人数:" + cmd.Parameters["@AbsentCount"].Value.ToString() + "人";
    this.label2.Text = "不及格总人数:" + cmd.Parameters["@FailCount"].Value.ToString() + "人";
}