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.
With transactions, SQL Server can bind a logically related set of operations so that the server maintains the integrity of the data.
In the SQL server+. NET development environment, there are two methods to complete the operation of the transaction and maintain the data integrity of the database;
One is the use of SQL stored procedures, and the other is a simple transaction processing in ADO;
Now use the example of a typical bank transfer to illustrate the usage of these two examples
Let's take a look at how the SQL stored procedure is doing the operation of the transaction:
First create a table:
Create Database AAAA--Creates a table that contains the user's account number and the amount of money
Go
Use AAAA
CREATE TABLE BB
(
ID int NOT NULL PRIMARY key,--account number
Moneys money--transfer amount
)
Insert into BB values (' 1 ', ' 2000 ')--inserts two data
Insert into BB values (' 2 ', ' 3000 ')
Use this table to create a stored procedure:
CREATE PROCEDURE Mon-Creates a stored procedure that defines several variables
@toID int,--account to receive the transfer
@fromID int,--turn out your account
@momeys Money--The amount of the transfer
As
Begin Tran--start the execution of the transaction
Update BB Set [email protected] where [email protected]-the first action performed, transfer money, minus the amount transferred out
Update BB Set [email protected] where [email protected]-Perform a second operation, accept the amount of the transfer, increase
If @ @error <>0--judging if any of the two statements has an error
Begin rollback tran– Start the rollback of the transaction, before the recovery of the transfer starts the state
return 0
End
Go
Else--How to execute success on all two lines
Begin commit Tran Perform the operation of this transaction
Return 1
End
Go
Now let's see how c#.net calls this stored procedure:
protected void Button1_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 a stored procedure
Cmd.commandtype = CommandType.StoredProcedure;
Con. Open ();
SqlParameter Prar = new SqlParameter ();//Pass Parameters
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;//Gets the return value of the stored procedure
Cmd. ExecuteNonQuery ();
String value = cmd. parameters["@return"]. Value.tostring ();//assigns the return value to value
if (value = = "1")
{
Label1.Text = "Add Success";
}
Else
{
Label1.Text = "Add Failed";
}
}
This is to add the transaction in the stored procedure, and then to see not the database write SQL stored procedures, ADO. NET is how transactions are handled:
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 SqlTransaction class, use this transaction is con this connection, use BeginTransaction this method to start executing this transaction
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = con;
Cmd. Transaction = Tran;
Try
{
Execute the SqlCommand command 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+ ' AA ' where id= ' 2 '";
Cmd. ExecuteNonQuery ();
Tran.commit ();//If all two SQL commands perform successfully, execute the Commit method and perform these operations
Label1.Text = "Add Success";
}
Catch
{
Label1.Text = "Add Failed";
Tran. Rollback ();//How to perform unsuccessful, an exception occurs, then the Rollback method is executed and rolled back to the beginning of the transaction operation;
}
}
This is a simple example of the different uses of the two transactions, and 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 merits.
Original source: http://www.cnblogs.com/zhuifengnianshao/archive/2010/11/24/1886939.html
"Turn" two usages of SQL Server transactions ()