asp.net and SQL affairs processing

ASP tutorials. NET and SQL processing
1: Write directly to SQL
Write SQL statements directly, the SQL statement itself can have transactions, have transactions, in the stored procedure using the BEGIN TRAN,COMMIT Tran rollback TRAN. Advantages: Independent of the application, with the best performance to run a transaction (write transaction processing in stored procedures is the best performance). Restrictions: The transaction context exists only in the database tutorial call (transaction processing can only be reflected in SQL statements, if it is not accessible outside of the SQL statement, the database code is related to the database system (because the transaction is handled in the SQL statement, the different database writes the same method);
2: Through the Ado.net implementation
We say that in. NET, transactions can be implemented through ado.net, and connection and Transacion objects are used in ado.net to control transactions, and to perform transactions: Invoke the Connrction method of the BeginTransaction object to mark a The start of the transaction (BeginTransaction returns a transaction object), Assigns the transaction object to the transaction property of the command to be executed (assigning this Tranaction object to the Command object's Transaction property), Call the transaction object's commit method to complete the transaction, or call rollback to cancel the transaction; Benefits: Simplicity, almost as fast as database transactions, independent of the database. Disadvantage: Cannot span multiple databases, transactions are performed on the database connection layer, so a database connection needs to be maintained in a transaction.
3:com+ transactions (distributed transactions)
In some specific cases, what we want to use; General database transaction control requires that the operations in the transaction must be in the same database (must operate on the same database), and there is a problem that in distributed applications, we tend to operate multiple databases simultaneously, SQL Server, Oracle,mysql tutorial, you may have to operate two databases or even multiple databases, and you want these operations to be either successful or fail at the same time, that is, they have to do a transaction, in which case the COM + distributed transactions are processed;
The core of transaction processing mentioned above is to keep the data consistent and logical after the database is restarted to prevent the system from crashing.
But in fact there is a point in our practical application of a very important point, such as: There is a invoicing system, when the procurement success, the data table order

Syntax for transaction operations:
BEGIN TRANSACTION
Begin DISTRIBUTED Transaction
Commit TRANSACTION
Commit work
Rollback work
Save Transaction
BEGIN TRANSACTION
BEGIN TRANSACTION
Marks the starting point for an explicit local transaction.

The BEGIN TRANSACTION The @ @trancount plus 1.

The BEGIN transaction represents a point where the data referenced by the connection is logically and physically consistent at that point. In the event of an error, all data changes after the BEGIN transaction can be rolled back to return the data to a known consistent state. Each transaction continues to execute until it is done without errors and a commit transaction to make permanent changes to the database, or encounters an error and erases all changes with the ROLLBACK TRANSACTION statement

Grammar
BEGIN Tran [saction] [transaction_name | @tran_name_variable [with Mark [' description ']]]

Example:
BEGIN Tran T1
Update table1 ...
--nest transaction m2
Begin Tran M2 with Mark
Update Table2 ...
SELECT * FROM table1
Commit Tran m2
Update Table3 ...
Commit Tran T1

Begin DISTRIBUTED Transaction
Specifies the start of a Transact-SQL Distributed transaction managed by the Microsoft Distributed Transaction Coordinator (MS DTC).

Grammar
Begin distributed Tran [Saction]
[transaction_name | @tran_name_variable]

Parameters
Transaction_name
is a user-defined transaction name used to track distributed transactions in the MS DTC utility. Transaction_name must conform to the rules for identifiers, but use only the first 32 characters

@tran_name_variable
is a user-defined variable name that contains a transaction name that is used to track distributed transactions in the MS DTC utility. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Comments
The server executing the BEGIN DISTRIBUTED TRANSACTION statement is the transaction creator and controls the completion of the transaction

When a subsequent commit TRANSACTION or ROLLBACK TRANSACTION statement is issued by the connection,
The master server requests MS DTC to manage the completion of distributed transactions between the servers involved.
There are two ways to register a remote SQL Server in a distributed transaction:

A registered connection in a distributed transaction executes a remote stored procedure call that references a remote server.
A registered connection in a distributed transaction executes a distributed query that references a remote server.

Example
This example updates the author's last name on the local and remote databases. The local and remote databases will either commit or roll back the transaction at the same time.

Description
MS DTC must be installed on the current SQL Server.

Use

pubs
Go
Begin DISTRIBUTED Transaction
Update authors
Set au_lname = ' McDonald ' where au_id = ' 409-56-7008 '
Execute link_server_t.pubs.dbo.changeauth_lname ' 409-56-7008 ', ' McDonald '
Commit Tran
Gonote:

If you need to connect to remote DB, be sure to fix the linkserver RPC option to True if you are connecting linkserver.

Set Xact_abort
Specifies that when a Transact-SQL statement produces a run-time error, Microsoft? SQL Server? Whether the current transaction is automatically rolled back.

Be sure to set the following parameters in distributed trans (Xact_abort)

Syntax set Xact_abort {on | off}

Note When set XACT_ABORT is on, if a Transact-SQL statement produces a run-time error, the entire transaction terminates and is rolled back. When off, only Transact-SQL statements that produce errors are rolled back, and transactions continue to be processed. Compilation errors, such as syntax errors, are not affected by set Xact_abort.

For most OLE DB providers, including SQL Server, data-modification statements in implicit or explicit transactions must have Xact_abort set to ON.

Set XACT_ABORT settings are set at execution or runtime, not at parse time.

The following example causes a foreign key error to occur in a transaction that contains other Transact-SQL statements. An error occurred in the first statement set, but the other statements were executed successfully and the transaction succeeded
Submit. In the second statement set, set Xact_abort to ON. This causes the statement error to terminate the batch process and rollback the transaction.

CREATE TABLE t1 (a int primary key)
CREATE TABLE t2 (a int references T1 (a))
Go
INSERT into T1 VALUES (1)
INSERT into T1 values (3)
INSERT into T1 values (4)
INSERT into T1 values (6)
Go
Set Xact_abort off
Go
BEGIN Tran
INSERT into T2 values (1)
INSERT into T2 values (2)/* FOREIGN KEY Error * *
INSERT into T2 values (3)
Commit Tran
Go

Set XACT_ABORT on
Go

BEGIN Tran
INSERT into T2 values (4)
INSERT into T2 VALUES (5)/* FOREIGN KEY Error * *
INSERT into T2 values (6)
Commit Tran
Go

Save Transaction

Set the save point within a transaction.

Syntax save Tran [saction] {savepoint_name | @savepoint_variable}
Parameter savepoint_name
is the name assigned to the save point. The savepoint name must conform to the rules for identifiers, but only the first 32 characters are used.
@savepoint_variable
is the name of a user-defined variable that contains a valid savepoint name.
The variable must be declared with a char, varchar, nchar, or nvarchar data type. Comments
A user can set a savepoint or tag within a transaction. A savepoint defines where a transaction can be returned if it is conditionally canceled as part of a transaction. If a transaction is rolled back to the savepoint, the transaction must be continued (if necessary, with more Transact-SQL statements and COMMIT TRANSACTION statements), or the transaction must be completely canceled (by rolling the transaction back to its starting point). To cancel the entire transaction, use the ROLLBACK TRANSACTION transaction_name format. This will undo all the statements and procedures for the transaction.

Note:1: Save transaction is not supported in distributed transactions that are explicitly started by the begin distributed transaction or upgraded from a local transaction.

2: When the transaction starts, it will always control the resources used in the transaction until the transaction completes (that is, the lock). When a part of a transaction is rolled back to the savepoint, the resource continues to be controlled until the transaction completes (or the entire transaction is rolled back).

Example: BEGIN TRANSACTION
Save transaction A
Insert into demo values (' BB ', ' B term ')
ROLLBACK TRANSACTION A
CREATE TABLE Demo2 (name varchar, age int)
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
Commit TRANSACTION