数据库开发(七)存储过程、事物、T-SQL编程

作者 Zhendong Ho 日期 2019-06-19
数据库开发(七)存储过程、事物、T-SQL编程

外连接

左外连接和右外连接

当需要把一张表的数据都显示出来的时候,需要用到外连接(左外连接或右外连接)。

左外连接:left join 或 left outer join

右外连接:right join 或 right outer join。

左表:出现在left join左边的表就叫做左表。

右表:出现在left join右边的表就叫做右表。

查询出所有参加了考试的同学的姓名,年龄,英语成绩,数学成绩(使用内连接)

select
t1.tsname,
t1.tsage,
t2.tEnglish,
t2.tMath
from TblStudent as t1
inner join TblScore as t2 on t1.tsid=t2.tsid

查询出所有同学的姓名,年龄,英语成绩,数学成绩(使用左外连接)

select
t1.tsid,
t1.tsname,
t1.tsage,
t2.tEnglish,
t2.tMath
from TblStudent as t1
left join TblScore as t2 on t1.tsid=t2.tsid

注意

  • 内连接,只显示那些两张表中可以匹配的数据。
  • 左外连接,会将所有左表中的数据全部显示。同时,对于左表中的数据,如果在右表中能找到匹配的数据,那么显示对应右表中的数据;如果在右表中找不到对应的匹配,则显示null。

查询出那些所有没有参加考试的同学的学生ID,姓名和年龄。

第一种,通过子查询实现。

select * from TblStudent where tsid not in (select tsid from TblStudent)

第二种,通过左外连接查询实现。

select
t1.*,
t2.*
from TblStudent as t1
left join TblScore as t2 on t1.tsid=t2.tsid
where t2.tscoreId is null

第三种,通过右外连接查询实现。

--右外连接,显示右表的所有记录(方法和左外连接一样,只是调换左表和右表的位置)
select
t1.*,
t2.*
from TblScore as t2
right join TblStudent as t1 on t1.tsid=t2.tsid
where t2.tscoreId is null

--右外连接,显示右表的所有记录(不调换左表和右表的位置)
select
t1.*,
t2.*
from TblStudent as t1
right join TblScore as t2 on t1.tsid=t2.tsid

外连接的原理

当使用连接查询的时候。如果同时要指定查询的条件,那么一定要使用where语句,不要直接在on条件后面跟and来编写其他查询条件。

下面这种写法是有问题的。

select
t1.*,
t2.*
from TblStudent as t1
left join TblScore as t2 on t1.tsid=t2.tsid -- and t1.tsgender = '男' --on条件后面跟and可能会有问题,left join会查询出TblStudent表中所有数据
where t1.tsgener = '男' --筛选条件要放到where子句中

下面这种写法可以正常显示要查询的数据。

select
t1.*,
t2.*
from TblStudent as t1
inner join TblScore as t2 on t1.tsid=t2.tsid and t1.tsgender = '男' --正常显示要查询的数据。因为inner join不需要增加外部列,而外连接是对笛卡儿积筛选完之后,再增加外部列

外连接的执行过程:

  1. 第一步,构建笛卡儿积。
  2. 根据left join on条件,从中进行数据筛选。
  3. 因为是外连接,所以要显示左表中的所有记录。所以接下来就要进行“添加外部行”(把TblStudent表中的没有筛选出来的那些数据,再添加到当前的查询结果集中)。

自连接

一张表自己和自己连接起来查询数据。使用自连接时必须给表起列名。

select
t1.AreaId 城市编号
t2.AreaName 城市名称
t3.AreaName 省份名称
from TblArea as t1
inner join TblArea as t2 on t1.AreaId=t2.AreaPid

多条件查询

当用户需要输入多条件模糊查询时,要根据用户输入的内容动态拼接SQL语句。

private void button1_Click(object sender, EventArgs e)
{
//假设表名:Books
//列名:BookName(书名)、Author(作者)、Pub(出版社)

//多条件查询,要根据用户输入的内容来动态拼接SQL语句。
//1.假设用户没有输入任何条件,那么就查询出所有的记录
StringBuilder sbSql = new StringBuilder("select * from Books");

//在wheres集合中保存查询的sql条件
List<string> wheres = new List<string>();
//把参数也放到一个集合当中
List<SqlParameter> listParameters = new List<SqlParameter>();

//2.如果用户输入了条件,则根据用户输入的条件动态拼接SQL语句
if (txtBookName.Text.Trim().Length > 0)
{
wheres.Add(" BookName like @bkName ");
listParameters.Add(new SqlParameter("@bkName", SqlDbType.NVarChar, 100) { Value = "%" + txtBookName.Text.Trim() + "%" });
}
if (txtAuthor.Text.Trim().Length > 0)
{
wheres.Add(" Author like @author ");
listParameters.Add(new SqlParameter("@author", SqlDbType.NVarChar, 100) { Value = "%" + txtAuthor.Text.Trim() + "%" });
}
if (txtPub.Text.Trim().Length > 0)
{
wheres.Add(" Pub like @pub ");
listParameter.Add(new SqlParameter("@Pub", SqlDbType.NVarChar, 100) { Value = "%" + txtAuthor.Text.Trim() + "%" });
}

//拼接SQL语句
//如果wheres集合当中的记录条数大于0,证明用户输入了条件
if (wheres.Count > 0)
{
sbSql.Append(" where ");//只要有查询条件就拼接一个where
//然后把后面的查询条件拼接起来。
sbSql.Append(string.Join(" and ", wheres));
}
SqlParameter[] pms = listParameters.ToArray();

//SqlHelper.ExecuteReader(sbSql.ToString(), pms);
}

视图

视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上。

视图和数据表存在本质的不同:数据表是实际存储记录的地方,然而视图并不保存任何记录。

视图的目的是方便查询,所以一般情况下不能对视图进行增删改。

创建视图

语法:create View 视图名 as 查询语句。

create view vw_TblArea
as
select
t1.AreaId 城市编号
t2.AreaName 城市名称
t3.AreaName 省份名称 --如果视图中的查询语句中包含了重名的列,此时必须为重名的列起别名
from TblArea as t1
inner join TblArea as t2 on t1.AreaId=t2.AreaPid

--查询视图
select * from vw_TblArea

视图的优点:

  • 筛选表中的行,降低数据库的复杂程度。
  • 防止未经许可的用户访问敏感数据。

注意

  1. 视图中的查询不能使用order by,除非指定了top语句。(视图被认为是一个虚拟表,表是一个集合,是不能有顺序的。而order by则返回的是一个有顺序的,是一个游标)
  2. 所有查询的列,必须有列名,且列名必须唯一。
  3. create view vw_name as 后面不能跟begin end。

T-SQL编程

声明变量

declare @name nvarchar(50)
declare @age int

--声明多个变量
declare @address nvarchar(500),@id int --一句话声明两个变量

为变量赋值

简单赋值

set @name='abc'
select @age=18

在查询中给变量赋值

declare @a int
set @a = (select count(*) from TblStudent) --查询语句结果赋值给变量,必须加括号
select @a = count(*) from TblStudent
print @a

当通过set为变量赋值的时候,如果查询语句返回的不止一个值,则报错。

declare @a int
set @a = (select tsage from TblStudent) --tsage返回多个值,报错

当通过select为变量赋值的时候,如果查询语句返回的不止一个值,那么会将最后一个结果赋值给该变量。

declare @a int
select @a = tsage from TblStudent --@a的值为select查询结果的最后一个值

输出

select '姓名',@name
select '年龄',@age

while循环

declare @i int=1 --声明变量的同时赋值
while @i<=100
begin
print 'hello'
set @i=@i+1
end

注意

  • 退出本次循环:continue
  • 退出循环:break
  • BEGIN和END相当于C#中的{}

计算1到100的和

declare @i int=1
declare @sum int=0 --必须赋初值
while @i<=100
begin
set @sum=@sum+@i
set @i=@i+1
end
select @sum

if…else…

declare @n int=10

if @n>10
begin
print '@n大于10'
end
else if @n>5
begin
print '@n大于5'
end
else
begin
print '@n小于等于5'
end

计算1-100之间所有奇数的和

declare @sum int=0, @i int=1

while @i<=100
begin
if @i%2<>0
begin
set @sum=@sum+@i
end
set @i=@i+1
end
print @sum --2500

系统变量

两个@@符号开头的一般都是系统变量。

print @@version

@@error,表示最后一个T-SQL错误的错误号。

set @@version = 'jk sql serevr 1998' --错误102,系统变量不能赋值
print @@error --102

@@language,表示当前使用的语言名称。

print @@language --简体中文

@@max_connections,表示可以创建的同时连接的最大数目。

print @@max_connections --32767

@@rowcount,表示受上一个SQL语句影响的行数。C#中ExecuteNonQuery返回的是增删改影响的行数,而T-SQL中可以用系统变量@@rowcount,获取上一次SQL语句影响的行数(增删改查)。

select * from TblClass --21行受影响
print @@rowcount --21

@@servername,表示本地服务器的名称。

print @@servername --QH-20140622LMWU

事务(transaction)

什么是事务

事务是指,访问并可能更新数据库中各种数据项的一个程序执行单元。也就是由多个SQL语句组成,必须作为一个整体执行。这些SQL语句作为一个整体一起向系统提交,要么都执行,要么都不执行。

语法步骤

  • 开始事务:BEGIN TRANSACTION(可以省略后面的SACTION)
  • 事务提交:COMMIT TRANSACTION
  • 回滚事务:ROLLBACK TRANSACTION

如何保证两条SQL语句同时执行成功或者同时执行失败呢?使用事务来保证。假设以银行账户转账为例:一个账户给另一个账户转账100。则必须保证一个账户的余额扣除100并且另一个账户的余额增加100,才能转账成功。(两条SQL语句同时执行成功或执行失败)

update bank set balance = balance - 100 where cid = '0002'
update bank set balance = balance + 100 where cid = '0001'

通过事务执行转账

--1.打开一个事务
begin transaction
declare @sum int = 0
--在转账之前,最好通过if-else判断,不要让程序发生异常或者错误。
--第一条SQL语句
update bank set balance = balance - 100 where cid = '0002'
set @sum = @sum + @@error

--第二条SQL语句
update bank set balance = balance + 100 where cid = '0001'
set @sum = @sum + @@error

--只要有任何一条SQL语句执行出错,那么最后的@sum就不是0
if @sum <> 0
begin
--表示程序执行出错
--回滚
rollback
end
else
begin
--如果没有出错,则提交事务
commit
end

注意

  • 事务使用begin来开始事务。事务开始后没有end,只有两种结果,就是commitrollback
  • 判断某条语句执行是否出错。使用系统变量@error。由于@error只能判断当前一条T-SQL语句执行是否有错,为了判断事务中所有T-SQL语句是否有错,需要对错误进行累计。如SET @sum = @sum + @@error
  • 即使使用事务,也尽量不要让程序发生异常或者报错(使用if-else避免)。

SQL Server中事务的分类

自动提交事务

当执行一条SQL语句时,数据库自动帮我们打开一个事务。当语句执行成功,数据库自动提交事务;执行失败,数据库自动回滚事务。如最普通的insert语句。默认情况下,SQL Server使用自动提交事务

insert into bbbb values(aaa)

隐式事务

每次执行一条SQL语句的时候,数据库自动帮我们打开一个事务,但是需要我们手动提交事务,或者回滚事务

打开或关闭隐式事务:SET IMPLICIT_TRANSACTIONS { ON | OFF }

--打开隐式事务
set implicit_transactions on
insert into bank values('0003', 1000000)
commit --需要手动提交事务,否则查询该表会一直等待
set implicit_transactions off --关闭隐式事务

显式事务

需要手动打开事务,手动提交事务或者回滚事务。即用begin tran代码。

begin tran
--代码
commit tran
--代码
--rollback transaction

事务的特性

事务的四个属性,也称为事务的ACID属性。

原子性(Actomicity):事务是一个完整的操作,事务的各步操作是不可分的(原子的)。即,要么执行,要么不执行。

一致性(Consistency):当数据完成时,数据必须处于一致状态。

隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务。

永久性(Durability):事务完成后,它对数据库的修改被永久保持,事务日志能够保持事务的永久性。

存储过程

存储过程Procedure是一组为了完成特定功能而封装好的的SQL语句集合,就像数据库中运行的方法(函数),经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。

存储过程的优点

  • 执行速度快 —— 在数据库中保存的存储过程语句都是编译过的。
  • 允许模块化程序设计 —— 类似方法的复用。
  • 提高系统安全性 —— 防止SQL注入。
  • 减少网络流通量 —— 只要传输存储过程的名称。(如果使用ADO.NET,则要传输很长的SQL语句给数据库服务器)

系统存储过程

系统存储过程由数据库定义,存放在master数据库中。系统存储过程名称一般以“sp_”开头或“xp_”开头。自定义的存储过程可以以“usp_”开头。常用的存储过程如下。

系统存储过程 说明
sp_databases 列出服务器上的所有数据库。
sp_helpdb 报告有关指定数据库或所有数据库的信息。
sp_renamedb 更改数据库的名称。
sp_tables 返回当前环境下可查询的对象的列表。
sp_columns 返回某个表列的信息。
sp_help 查看某个表的所有信息。
sp_helpconstraint 查看某个表的约束。
sp_helpindex 查看某个表的索引。
sp_stored_procedures 列出当前环境中的所有存储过程。
sp_password 添加或修改登录账户的密码。
sp_helptext 显示默认值、未加密的存储过程、用户定义的
存储过程、触发器或视图的实际文本。

调用存储过程

执行存储过程。语法:exec 存储过程名称 参数

一般来说,系统存储过程可以省略exec,自定义的存储过程不能省略exec

--返回当前实例中的所有的数据库的基本信息
exec sp_databases
--返回当前数据库下的所有的表
exec sp_tables
--返回某张表下的所有的列
exec sp_columns 'tblPerson'
--查看某个存储过程的源代码
exec sp_helptext 'sp_databases'

创建存储过程

创建存储过程。语法:create procedure 存储过程名称 as begin 存储过程内容 end

--创建自己的存储过程
create procedure usp_helloworld
as
begin
print 'hello world!'
end

修改存储过程

一般来说有两种方法修改存储过程。一种是先使用drop删除存储过程,再用create创建存储过程。另一种是直接使用alter修改存储过程。

--先删除存储过程,再创建存储过程
drop proc usp_select_tblStudent
create proc usp_select_tblStudent
as
begin
select * from TblStudent where tsgender = '男'
end

--直接使用alter修改存储过程
alter proc usp_select_tblStudent
as
begin
select * from TblStudent where tsgender = '女'
end

创建带参数的存储过程

创建一个带两个参数的存储过程。

--创建一个带两个参数的存储过程
create proc usp_add_number
@n1 int,
@n2 int
as
begin
select @n1 + @n2
end

--执行存储过程
exec usp_add_number 100,500 --600

根据参数条件查询表中的数据。

create proc usp_select_tblStudent_by_condition
@gender char(2),
@age int
as
begin
select * from tblStudent where tsgender = @gender and tsage >= @age
end

--执行存储过程
exec usp_select_tblStudent_by_condition @gender = '男', @age = 20 --指定参数名称

设置存储过程的参数的默认值。

alter proc usp_add_number
@n1 int = 100, --n1的默认值是100
@n2 int
as
begin
select @n1 + @n2
end

--执行存储过程
--exec usp_add_number 80 --错误,80默认传给n1,若要传给n2,必须指定参数名称
exec usp_add_number @n2 = 80 --结果是180,这里必须指定参数名称

带输出参数的存储过程。

create proc usp_show_students
@gender char(2),
@recordcount int output --输出参数
as
begin
select * from MyStudent where fgender = @gender
--把查询语句查询到的记录的条数赋值给变量@recordcount
set @recordcount = (select count(*) from MyStudent where fgender = @gender)
end

--执行存储过程
declare @rc int
exec usp_show_students @gender = '男', @recordcount = @rc output

print @rc --输出记录条数

使用存储过程编写分页查询

create proc usp_getMyStudentsDataByPage
@pagesize int = 7, --每页记录条数
@pageindex int = 1, --当前要查看第几页的记录
@recordcount int output, --总的记录的条数
@pagecount int output --总的页数
as
begin
--1.编写查询语句,把用户要的数据查询出来
select
t.fid,
t.fname,
t.fage,
t.fgender,
t.fmath,
t.fclassid,
t.fbirthday
from (select *, rn = row_number() over(order by fid asc) from MyStudent) as t
where t.rn between (@pageindex - 1) * @pagesize + 1 and @pagesize * @pageindex

--2.计算总的记录条数
set @recordcount = (select count(*) from MyStudent)

--3.计算总页数
set @pagecount = ceiling(@recordcount * 1.0 / @pagesize)
end

通过ADO.NET调用存储过程

通过ADO.NET调用存储过程与调用带参数的SQL语句的区别

  1. 把SQL语句变成存储过程的名称。
  2. 设置SqlCommand对象的CommandType为CommandType.StoredProcedure。
  3. 根据存储过程的参数来设置SqlCommand对象的参数。
  4. 如果有输出参数需要设置输出参数的Direction属性为:Direction=ParameterDirection.Output。

使用SqlDataReader执行存储过程

private int pageIndex = 1;//当前要查看的页码
private int pageSize = 7;//每页显示的记录条数
private int pageCount;//总页数
private int recordCount;//总条数

//窗体加载的时候显示第一页的数据
private void Form1_Load(object sender, EventArgs e)
{
LoadData();
}

private void LoadData()
{
//根据pageIndex来加载数据
string constr = "Data Source=.;Initial Catalog=Lottery;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(constr))
{
//将SQL语句变成存储过程名称
string sql = "usp_getUserInfoByPage";

using (SqlCommand cmd = new SqlCommand(sql, conn))
{
//告诉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);
//打开连接
conn.Open();
//执行
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(reader[i] + " | ");
}
Console.WriteLine();
}
}
else
{
Console.WriteLine("没有数据");
}
//不能在此获取输出参数
}
//获取输出参数
recordCount = pms[2].Value.ToString();
pageCount = pms[3].Value.ToString();
}
}
}

注意:如果是通过Command对象的ExecuteReader方法来执行的存储过程,那么要想获取输出参数,必须得等到关闭reader对象后,才能获取输出参数

使用SqlDataAdapter执行存储过程

private void LoadData()
{
//根据pageIndex来加载数据
string constr = "Data Source=.;Initial Catalog=Lottery;Integrated Security=True";
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter("usp_getUserInfoByPage", constr))
{
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);

//获取输出参数并且赋值给label
label1.Text = pms[2].Value.ToString();
label2.Text = pms[3].Value.ToString();
//数据绑定
this.dataGridView1.DataSource = dt;
}
}

注意:ADO.NET执行存储过程时,需要设置CommandType.StoredProcedure。设置完毕该属性后,相当于在存储过程的名字前面增加exec。如果没有设置该属性,则必须在存储过程名字前加exec,如“exec usp_getUserInfoByPage”。

调用存储过程实现转账

把事务转账封装成存储过程

--创建存储过程实现转账
create proc usp_transfer_bank
@from char(4),
@to char(4),
@balance money, --转账金额
@resultNumber int output --转账是否成功(1表示成功,2表示失败,3表示余额不足)
as
begin
--1.判断金额是否足够转账
declare @money money
select @money = balance from bank where cid = @from
if @money - @balance >= 10
begin
--开始转账
begin transaction
declare @sum int = 0
--2.账户1扣钱
update bank set balance = balance - @balance where cid = @from
set @sum = @sum + @@error
--3.账户2加钱
update bank set balance = balance + @balance where cid = @to
set @sum = @sum + @@error
--4.判断执行是否成功,进行提交或者回滚
if @sum <> 0
begin
set @resultNumber = 2 --转账失败
rollback
end
else
begin
set @resultNumber = 1 --转账成功
commit
end
end
else
begin
set @resultNumber = 3 --余额不足
end
end

测试存储过程是否转账成功

--@from char(4),
--@to char(4),
--@balance money, --转账金额
--@resultNumber bit output
declare @r int
exec usp_transfer_bank @from = '0001', @to = '0002', @balance = 900, @resultNumber = @r output
print @r

ADO.NET调用转账存储过程转账

private void button1_click(object sender, EventArgs e)
{
string from = textBox1.Text.Trim();
string to = textBox2.Text.Trim();
double money = double.Parse(textBox3.Text.Trim());

SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@from", SqlDbType.Char, 4) { Value = from },
new SqlParameter("@to", SqlDbType.Char, 4) { Value = to },
new SqlParameter("@balance", SqlDbType.Money) { Value = money },
new SqlParameter("@resultNumber", SqlDbType.Int) { Direction = ParameterDirection.Output }
};

//通过SqlHelper调用存储过程实现转账
SqlHelper.ExecuteNonQuery("usp_transfer_bank", CommandType.StoreProcedure, pms);
//获取输出参数
int result = Convert.ToInt32(pms[3].Value);
if (result == 1)
{
MessageBox.Show("成功!");
}
else if (result == 2)
{
MessageBox.Show("失败!");
}
else
{
MessageBox.Show("余额不足!");
}
}

注意:ADO.NET调用存储过程时,ExecuteReader、ExecuteNonQuery、ExecuteScalar无论是调用哪个方法都可以执行成功。要调用哪个方法,主要取决于执行完存储过程后希望获取到的结果

通过存储过程对表实现增删改查

--1.向TblClass表中插入数据
create proc usp_insert_TblClass
@name varchar(50),
@desc varchar(50)
as
begin
insert into TblClass values(@name, @desc)
end

--2.删除TblClass表中的记录(根据主键ID)
create proc usp_delete_TblClass
@tsclassid int
as
begin
delete from TblClass where tsClassId = @tsclassid
end

--3.更新TblClass表
create proc usp_update_TblClass
@tsclassid int
@name varchar(50),
@desc varchar(50),
as
begin
update TblClass set tClassName = @name, tClassDesc = @desc where tsClassId = @tsclassid
end

--4.查询
create proc usp_selectAll_TblClass
as
begin
select * from TblClass
end

T-SQL中的try…catch语句

语法:begin try … end try begin catch … end catch

begin try
update bank set balance = -100
end try
begin catch
print '出异常了'
end catch

问题:同一个SqlParameter参数不能同时用于多个Command对象

static void Main(string[] args)
{
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@name", System.Data.SqlDbType.Varchar, 50),
new SqlParameter("@desc", System.Data.SqlDbType.Varchar, 50)
};

//第一次执行SQL语句
string sql = "insert into TblClass values(@name, @desc)";
pms[0].Value = "aaa";
pms[1].Value = "aaa";
SqlHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, pms);

//第二次执行SQL语句
string sqlTwo = "insert into TblClass values(@name, @desc)";
pms[0].Value = "bbb";
pms[1].Value = "bbb";
SqlHelper.ExecuteNonQuery(sqlTwo, System.Data.CommandType.Text, pms);//报错:另一个SqlParameterCollection中已经包含SqlParameter。
//因为pms在别的Command对象中已经使用过了

Console.WriteLine("ok");
Console.ReadKey();
}

触发器Trigger

触发器是一种特殊的存储过程(不能传参数),它通过事件进行触发而被执行

触发器的作用:自动化操作,减少手动操作以及出错的概率。

DML触发器

对表中的数据进行操作时触发。

  • insert、delete、update(不支持select)。
  • after触发器(for)、instead of触发器(不支持before触发器)。

DDL触发器

对表的结构定义修改时触发。

  • create table、create database、alter、drop等。

inserted表和deleted表

insert操作用到了inserted表

delete操作用到了deleted表

update操作用到了inserted表和deleted表

基本语法

after触发器:在语句执行完毕后触发(after和for都表示after触发器)。

instead of触发器:要用来替换原本的操作。

语法:create trigger 触发器名 on 表名 after(for) | instead of update|insert|delete (insert,update,delete) as begin … end

创建一个删除触发器。删除一个表的数据时,把删除的数据插入到另一个表中

create trigger tri_delete_tblclass on TblClass
after delete
as
begin
--从deleted表中读取删除的数据
insert into TblClassBackup(tsClassName, tsClassdesc)
select tsClassName, tsClassDesc from deleted --deleted表只有在触发器里面才能访问
--rollback --可以在触发器中使用rollback,使用后数据回滚
end

触发器建议

  • 尽量避免在触发器中执行耗时操作。因为触发器会与SQL语句认为在同一个事务中。(事务不结束,就无法释放锁)
  • 避免在触发器中做复杂的操作。
  • deleted表和inserted表,只能在触发器中访问,不能直接访问。
  • 可以在触发器中使用rollback,使用后数据会进行回滚。