目录
涉及知识点: MySQL事务 -- MySQL存储过程 -- MySqlTransaction配置
一、前期准备
1、配置App.config文件 -- 若文件未找到,则 Ctrl+Shift+A --> 添加 " 应用程序配置文件 "
<connectionStrings>
<add name="connStr"
connectionString="server=localhost;database=mysql_student;uid=root;pwd=123456"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
2、导入Nuget包
3、引入using
using MySql.Data.MySqlClient;
using System;
using System.Configuration;
using System.Data;
4、创建MySQL 数据表
create table tb_user(
id int primary key,
name varchar(10),
age int
);
insert into tb_user(id,name,age)
values (1,'小王',22),
(2,'小白',20),
(3,'小思',19),
(4,'小囡',21);
二、事务概念
事务一般使用 增删改 操作
事务执行过程:
开启
提交 -- 正常
回滚 -- 出现问题
三、MySQL配置事务 -- 小数据
修改前数据库数据:
MySQL数据库中的事务配置: -- MySQL 代码
create procedure UpdataName(p_id_1 int, p_name_1 varchar(10),
p_id_2 int, p_name_2 varchar(10))
begin
#捕获异常 -- 若报错,则 @err_flag = true
declare continue handler for sqlexception set @err_flag = true;
set @err_flag = false;
set @@autocommit = 0; #设置手动
start transaction; #开始事务
update tb_user as tb set tb.name = p_name_1 where tb.id = p_id_1;
#select * from XXX; #验证回滚时使用
update tb_user as tb set tb.name = p_name_2 where tb.id = p_id_2;
if @err_flag then #若报错
rollback; #回滚
set @@autocommit = 1; #设为自动
select @err_flag;
else #若正确
commit; #提交
set @@autocommit = 1; #设为自动
select @err_flag;
end if;
end $$
C#对MySQL的操作: -- C#代码
internal class Program
{
static void Main(string[] args)
{
//获取外部文件字符串
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (MySqlConnection connect = new MySqlConnection(connStr))
{
MySqlCommand cmd = new MySqlCommand("UpdataName", connect);
cmd.CommandType = CommandType.StoredProcedure; //命令类型:存储过程
MySqlParameter[] parames = {
new MySqlParameter("p_id_1",1),
new MySqlParameter("p_name_1","小兰"),
new MySqlParameter("p_id_2",2),
new MySqlParameter("p_name_2","小紫"),
};
cmd.Parameters.AddRange(parames);
connect.Open();
object obj = cmd.ExecuteScalar();
connect.Close();
if (obj.ToString().Equals("0"))
{
Console.WriteLine("修改成功");
}
else
{
Console.WriteLine("修改失败");
}
}
Console.WriteLine("Hello World!");
}
}
结果:
四、C#配置事务 -- 大数据
修改前数据库内容
C#对MySQL的操作: -- C#代码
internal class Program
{
static void Main(string[] args)
{
//获取外部文件字符串
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (MySqlConnection connect = new MySqlConnection(connStr))
{
MySqlTransaction trans = null;
try
{
connect.Open();
//开启事务
trans = connect.BeginTransaction();
MySqlCommand cmd = connect.CreateCommand();
//设置要执行的事务
cmd.Transaction = trans;
cmd.CommandText = "update tb_user as tb set tb.name = @name_1 where tb.id = @id_1;";
cmd.Parameters.Clear();
MySqlParameter[] parames = new MySqlParameter[] {
new MySqlParameter("@name_1","小王"),
new MySqlParameter("@id_1",1)
};
cmd.Parameters.AddRange(parames);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//验证回滚时使用
//cmd.CommandText = "select * from XXX";
//cmd.ExecuteNonQuery();
cmd.CommandText = "update tb_user as tb set tb.name = @name_2 where tb.id = @id_2;";
cmd.Parameters.Clear();
MySqlParameter[] parames1 = new MySqlParameter[] {
new MySqlParameter("@name_2","小白"),
new MySqlParameter("@id_2",2)
};
cmd.Parameters.AddRange(parames1);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit(); //提交
Console.WriteLine("!!!成功!!!");
}
catch (MySqlException ex)
{
trans.Rollback(); //回滚
Console.WriteLine("!!!错误!!!" + ex.Message);
}
finally
{
trans.Dispose(); //释放
connect.Close();
}
}
Console.WriteLine("Hello World!");
}
}
结果:
如有错误,烦请批评指正