"Turn" two usages of SQL Server transactions ()

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 ()