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

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

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

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

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

 use SMDBgoif exists (select * from sysobjects where name = 'usp_ScoreQuery1')drop procedure usp_ScoreQuery1gocreate procedure usp_ScoreQuery1as-- 查询考试成绩select Students.StudentId,StudentName,ClassName, ScoreSum = (CSharp + SqlserverDB) from Studentsinner join StudentClass on StudentClass.ClassId = Students.ClassIdinner join ScoreList on ScoreList.StudentId = Students.StudentIdorder by ScoreSum DESC-- 分析考试信息select ClassName,C#Avg=AVG(CSharp),DBAvg=AVG(SqlserverDB) from ScoreListinner join Students on Students.StudentId = ScoreList.StudentIdinner join StudentClass on StudentClass.ClassId = Students.ClassIdgroup by ClassNameorder by ClassNamego

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

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
asselect Students.StudentId,StudentName,CSharp as C#,SqlserverDB as DB from ScoreListinner join Students on Students.StudentId = ScoreList.StudentIdwhere 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 ScoreListinner join Students on Students.StudentId = ScoreList.StudentIdwhere 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;// 添加执行的sqlcmd.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"])});}//如果有第二个数据源 读取第二个selectif (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 设置输入参数//定义参数方法1SqlParameter csharp = new SqlParameter();csharp.ParameterName = "@CSharp";// 设置csharp是存储过程中对应@CSharp输入参数csharp.Direction = ParameterDirection.Input;//设置为输入参数csharp.Value = 180;// 设置参数的值为70csharp.SqlDbType = SqlDbType.Int; //设置参数类型cmd.Parameters.Add(csharp); // 把输入参数添加到参数列表里面// 定义参数方法2 @SqlserverDBcmd.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() + "人";
}