C # + SQL server transaction processing

A    transaction (Transaction) is a unit of concurrency control and is a user-defined sequence of operations. These operations are either done or not, and are an inseparable unit of work. 

through transactions, SQL Server The ability to bind a logically related set of operations so that the server maintains the integrity of the data. 

In  the development environment, there are two ways to complete the operation of the transaction, and maintain the data integrity of the database;

one is to use SQL stored procedures, and the other is the ADO a simple transaction processing;

Now use the example of a typical bank transfer to illustrate the usage of these two examples

let's look at it first . SQL How a stored procedure completes the operation of a transaction:

First create a table:

?

1 2 3 4 5 6 7 8 9 10

create database aaaa --创建一个表，包含用户的帐号和钱数 go use aaaa create table bb (  ID int not null primary key,  --帐号  moneys money    --转账金额 ） insert into bb values (‘1‘,‘2000‘) --插入两条数据 insert into bb values (‘2‘,‘3000‘)

Use this table to create a stored procedure:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

create procedure mon --创建存储过程，定义几个变量 @toID int,    --接收转账的账户 @fromID int ,  --转出自己的账户 @momeys money --转账的金额 as begin tran --开始执行事务 update bb set [email protected] where [email protected] -执行的第一个操作，转账出钱，减去转出的金额 update bb set [email protected] where [email protected] --执行第二个操作，接受转账的金额，增加 if @@error<>0 --判断如果两条语句有任何一条出现错误 begin rollback tran –开始执行事务的回滚，恢复的转账开始之前状态 return 0 end go else--如何两条都执行成功 begin commit tran 执行这个事务的操作 return 1 end go

pick it up and see How the C#.net calls this stored procedure:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

protected void Button1_Click(object sender, EventArgs e) {         SqlConnection con =new SqlConnection(@"Data Source=.\SQLEXPRESS;database=aaaa;uid=sa;pwd=jcx"); //连接字符串         SqlCommand cmd = new SqlCommand("mon",con); //调用存储过程         cmd.CommandType = CommandType.StoredProcedure;         con.Open();         SqlParameter prar = new SqlParameter();//传递参数         cmd.Parameters.AddWithValue("@fromID", 1);         cmd.Parameters.AddWithValue("@toID", 2);         cmd.arameters.AddWithValue("@momeys",Convert.ToInt32( TextBox1.Text) );         cmd.Parameters.Add("@return", "").Direction = ParameterDirection.ReturnValue;//获取存储过程的返回值         cmd.ExecuteNonQuery();         string value = cmd.Parameters["@return"].Value.ToString();//把返回值赋值给value         if (value == "1")         {             Label1.Text = "添加成功";         }         else         {             Label1.Text = "添加失败";        } }

This is to add a transaction in the stored procedure, and then to see not the database write SQL stored procedures, How ADO handles transactions:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

protected void Button2_Click(object sender, EventArgs e)    {         SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;database=aaaa;uid=sa;pwd=jcx");         con.Open();         SqlTransaction tran = con.BeginTransaction();//先实例SqlTransaction类，使用这个事务使用的是con 这个连接，使用BeginTransaction这个方法来开始执行这个事务         SqlCommand cmd = new SqlCommand();         cmd.Connection = con;         cmd.Transaction = tran;         try         {              //在try{} 块里执行sqlcommand命令，             cmd.CommandText = "update bb set moneys=moneys-‘" + Convert.ToInt32(TextBox1.Text) + "‘ where ID=‘1‘";             cmd.ExecuteNonQuery();             cmd.CommandText = "update bb set moneys=moneys+‘ aa ‘ where ID=‘2‘";             cmd.ExecuteNonQuery();             tran.Commit();//如果两个sql命令都执行成功，则执行commit这个方法，执行这些操作             Label1.Text = "添加成功";         }         catch         {             Label1.Text = "添加失败";             tran.Rollback();//如何执行不成功，发生异常，则执行rollback方法，回滚到事务操作开始之前；         }     }

This is a simple example of the different uses of two transactions, the ADO method looks simple, but he wants to use the same connection to perform these operations, it is cumbersome to use a few databases to execute with a single transaction, but it is relatively straightforward to use SQL stored procedures, In short, the two methods each have their advantages