事务:
ACID 原子性,一致性,隔离性,持久性
一、链接内的事务
1.创建事务对象。使用链接对象的BeginTransaction()
SqlTransaction trans = conn.BeginTransaction();
注意:必须在链接打开的时候创建事务。
2.把事务对象挂到命令对象上
cmd.Transaction = trans;
3.执行命令,提交与回滚事务
trans.Commit() //一般放在try块中
trans.Rollback() //一般放在catch块中
二、跨链接的事务 TransactionScope对象。
1.启动服务:DTC 分布式事务协调器。
2.引用事务程序集。在解决方案管理器中,右键添加引用。System.Transactions
3.使用using System.Transactions 把命名空间导进来。
4.使用TransactionScope类来控制事务。
using (TransactionScope ts = new TransactionScope())
{
DeleteWork("p001");
DeleteFamily("p001");
DeleteInfo("p001");
ts.Complete(); //必须。它是用来提交事务的。
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
namespace lian_CommandBehavior
{
class Program
{
public static string Ab = "server=.;database=wuzi;uid=sa;pwd=123";
static SqlDataReader Getkc(string name)
{
SqlConnection con = new SqlConnection(Ab);
//try
//{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from kc where WzName='"+name+"'";
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
//}
//catch (Exception)
//{
// Console.WriteLine("文件错误!");
//}
}
static void Main(string[] args)
{
string cun="";
Console.WriteLine("发放物资:");
Console.Write("请输入要发放的物资的名称:");
string na = Console.ReadLine();
cun = Yanname(na);
if (cun == "ke")
{
Jixu(na);
}
else
{
Console.WriteLine("该物资不存在!");
}
Console.ReadLine();
}
public static string Yanname(string na)
{
string ok = "ke";
SqlDataReader dr =Getkc(na);
dr.Read();
if (dr["WzName"].ToString() == na)
{ ok = "ke"; }
else ok = "bu";
dr.Close();
return ok;
}
public static void Jixu(string na)
{
int num;
string unit;
decimal price;
SqlDataReader dr = Getkc(na);
dr.Read();
num = (int)dr["Num"];
unit = dr["Unit"].ToString();
price=(decimal)dr["Price"];
Console.WriteLine("当前单价:"+price+"元");
Console.WriteLine("当前库存:"+num+unit);
Console.Write("请输入发放数量:");
int numm = Convert.ToInt32(Console.ReadLine());
Console.Write("请输入接受人:");
string ren = Console.ReadLine();
if (num >= numm)
{
int num1 = num - numm;
using (TransactionScope tr = new TransactionScope())
{
Gaikcnum(na, num1);
Chafafang(na, numm, unit, price, ren);
tr.Complete();
}
}
else
{ Console.WriteLine("您输入的数量已超过该物资的库存数量!"); }
}
public static void Gaikcnum(string name,int num)
{
SqlConnection con = new SqlConnection(Ab);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "update kc set Num=@nu where WzName=@na";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@nu",num);
cmd.Parameters.AddWithValue("@na",name);
cmd.ExecuteNonQuery();
}
catch
{
Console.WriteLine("文件错误!--改动");
}
finally
{ con.Close(); }
}
public static void Chafafang(string na,int num,string unit,decimal price,string ren)
{
int count = 0;
DateTime date = DateTime.Now;
SqlConnection con = new SqlConnection(Ab);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "insert into fafang values (@na,@nu,@un,@pr,@da,@re)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@na",na);
cmd.Parameters.AddWithValue("@nu", num);
cmd.Parameters.AddWithValue("@un", unit);
cmd.Parameters.AddWithValue("@pr", price);
cmd.Parameters.AddWithValue("@da", date);
cmd.Parameters.AddWithValue("@re", ren);
cmd.ExecuteNonQuery();
cmd.CommandText = "select count(*) from fafang ";
count = (int)cmd.ExecuteScalar();
Console.WriteLine("已经有"+count+"人领取了物资!");
}
catch
{
Console.WriteLine("文件错误!--插入");
}
finally
{
con.Close();
}
}
}
}