存储过程在应用程序端的使用的优点
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() + "人";
}
