. Net SQL Server transaction usage

A transaction is a unit of concurrency control and a sequence of user-defined operations. These operations are either done or not done. They are an inseparable unit of work.
Through transactions, SQL Server can bind a set of logical operations to ensure data integrity on the server.
In the SQL SERVER +. NET development environment, there are two ways to complete transaction operations to maintain the data integrity of the database;
One is SQL stored procedures, and the other is a simple transaction processing in ADO. net;
Now we use a typical bank transfer example to illustrate the usage of these two examples.
Let's take a look at how SQL stored procedures perform transaction operations:
First, create a table:
Create Database AAAA -- create a table that contains the user's account and money
Go
Use AAAA
Create Table bb
(
Id int not null primary key, -- account
Moneys money -- transfer amount
)
Insert into BB values ('1', '20140901') -- insert two pieces of data
Insert into BB values ('2', '123 ')
Use this table to create a stored procedure:
Create procedure Mon -- create a stored procedure and define SEVERAL VARIABLES
@ Toid int, -- the account that receives the transfer
@ Fromid int, -- transfer out your account
@ Momeys money -- transfer amount
As
Begin tran -- start transaction execution

Update BB set moneys = moneys-@ momeys where id = @ fromid-the first operation to be executed. The money is transferred, minus the amount transferred.
Update BB set moneys = moneys + @ momeys where id = @ toid -- perform the second operation, accept the transfer amount, and increase

If @ error <> 0 -- determines if either of the two statements has an error
Begin rollback tran-start to execute the transaction rollback, before the recovery transfer starts
Return 0
End
Go

Else -- how to successfully execute both
Begin commit tran
Return 1
End
Go

Next let's take a look at how C #. Net calls this stored procedure:
Protected void button#click (Object sender, eventargs E)
{
Sqlconnection con = new sqlconnection (@ "Data Source =. \ sqlexpress; database = AAAA; uid = sa; Pwd = jcx"); // connection string
Sqlcommand cmd = new sqlcommand ("mon", con); // call the Stored Procedure
Cmd. commandtype = commandtype. storedprocedure;
Con. open ();
Sqlparameter prar = new sqlparameter (); // pass the Parameter
Cmd. Parameters. addwithvalue ("@ fromid", 1 );
Cmd. Parameters. addwithvalue ("@ toid", 2 );
Cmd. Parameters. addwithvalue ("@ momeys", convert. toint32 (textbox1.text ));
Cmd. Parameters. Add ("@ return", ""). Direction = parameterdirection. returnvalue; // obtain the return value of the stored procedure
Cmd. executenonquery ();
String value = cmd. Parameters ["@ return"]. value. tostring (); // value the returned value to the value.
If (value = "1 ")
{
Label1.text = "added successfully ";
}
Else
{
Label1.text = "failed to add ";
}
}

 

This is to add a transaction to the stored procedure. Let's take a look at how ADO. Net handles the transaction instead of writing the SQL stored procedure to the database:
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 (); // first instance the sqltransaction class. This transaction uses the con connection and the begintransaction method to start executing this transaction.
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = con;
Cmd. Transaction = Tran;
Try
{
// Execute the sqlcommand in the try {} block,
Cmd. commandtext = "Update BB set moneys = moneys-'" + convert. toint32 (textbox1.text) + "'where id = '1 '";
Cmd. executenonquery ();
Cmd. commandtext = "Update BB set moneys = moneys + 'A' where id = '2 '";
Cmd. executenonquery ();
Tran. Commit (); // if both SQL commands are successfully executed, execute the commit method to perform these operations.

Label1.text = "added successfully ";
}
Catch
{
Label1.text = "failed to add ";
Tran. rollback (); // if an exception occurs, execute the rollback method and roll back to the beginning of the transaction operation;
}

}
This is a simple example of different transaction usage, ADO. the net transaction processing method looks simple, but he wants to use the same connection to execute these operations. If he wants to use several databases to execute these operations with one transaction at the same time, it will be complicated, however, if you use SQL stored procedures, this is relatively simple. In short, the two methods have their own advantages.