SQL Server database transaction introduction (2)-how to use SQL statements, sqltransaction, and transactionscope

This section describes SQL statements, sqltransaction, and transactionscope.

All examples in this section run in SQL Server 2008 and vs 2008 environments. If SQL Server 2005 is not available, the same applies to SQL Server, however, SQL se rver 2000 cannot be run because some SQL statements are not supported in SQL 2000. Please pay attention to this.

Run the following script to create a test database in the database instance of the Local Machine to facilitate the running of the example.

-- Database creation <br/> If exists (Select name from sys. databases where name = n' transtestdb') <br/> drop database [transtestdb] </P> <p> Create Database [transtestdb]; </P> <p> -- create a table <br/> use [transtestdb] <br/> go <br/> If exists (select * From sys. objects where object_id = object_id (n' [DBO]. [transtesttable] ') and type in (n'u') <br/> drop table [transtesttable] </P> <p> Create Table [DBO]. [transtesttable] (ID int, [name] varchar (16 )); </P> <p> -- Initial value <br/> use [transtestdb] <br/> go <br/> insert into [transtesttable] <br/> select 1, 'A' Union <br/> select 2, 'B' Union <br/> select 3, 'C ';

 

First, we will introduce how to use transactions using SQL statements. SQL server2005/2008 provides begin Tran, commit Tran, and rollback Tran statements to display transactions. Begin Tran indicates that the transaction starts, commit Tran indicates that the transaction is committed, and rollback Tran indicates that the transaction is rolled back. The Code is as follows:

Begin try <br/> begin Tran <br/> insert into DBO. transtesttable values (66, '66'); <br/> Update DBO. transtesttable set [name] = '77 'Where [ID] = 66; <br/> -- raiserror ('error raised in try block. ', 16,1); <br/> commit Tran <br/> end try <br/> begin catch <br/> rollback Tran <br/> end catch

The begin try and begin catch statements in the Code are used to catch exceptions. They are only supported in SQL server2005/2008 and are not supported in SQL Server 2000. If an exception occurs during code execution between begin try and end try, the program will jump to begin catch and end catch to perform related rollback Tran rollback operations. It is a transaction between Tran in TRAN and commit Tran. The insert and update operations must succeed at the same time; otherwise, the Operation will fail at the same time. The raiserror statement throws an exception and is only supported by SQL Server 2008 and not supported by SQL Server 2000.

Execute the above code and we will find that both insertion and update are successful. Remove the comment of raiserror and execute it again. We will find that insertion and update are rolled back. After raiserror throws an exception, it does not execute commit Tran, but directly executes the rollback Tran rollback statement in begin catch.

 

The following describes how to use sqltransaction. Sqltransaction is a transaction class in the namespace of system. Data. sqlclient. The main methods include commit () and rollback (). For more methods and attributes, see msdn. The Code is as follows:

Static void main (string [] ARGs) <br/>{</P> <p> sqlconnection sqlconn = new sqlconnection (<br/> configurationmanager. connectionstrings ["connstr"]. connectionstring); <br/> sqltransaction sqltrans = NULL; <br/> try <br/> {<br/> sqlconn. open (); <br/> sqltrans = sqlconn. begintransaction (); // transaction start <br/> sqlcommand sqlcomm = new sqlcommand ("", sqlconn, sqltrans); <br/> sqlcomm. commandtimeout = 120; <br/> Sqlcomm. commandtype = system. data. commandtype. text; </P> <p> string insertsql = "insert into DBO. transtesttable values (66, '66'); "; <br/> string updatesql =" Update DBO. transtesttable set [name] = '77 'Where [ID] = 66; "; </P> <p> sqlcomm. commandtext = insertsql; <br/> sqlcomm. executenonquery (); // execute insert </P> <p> sqlcomm. commandtext = updatesql; <br/> sqlcomm. executenonquery (); // execute update <br/> // throw New exception ("test exception. the transaction must rollback "); </P> <p> sqltrans. commit (); // transaction commit <br/>}< br/> catch (exception ex) <br/>{< br/> sqltrans. rollback (); // transaction rollback <br/> console. writeline (ex. message); <br/>}< br/> finally <br/>{< br/> If (sqlconn. state! = System. data. connectionstate. closed) <br/> sqlconn. close (); <br/>}</P> <p> console. readline (); <br/>}

The above code shows the basic usage of the sqltransaction class. Sqlconn. begintransaction () indicates the start of the transaction. After some basic operations (the Code executes an insert and an update), sqltrans is executed. commit (); indicates that the transaction is committed. At this time, the data inserted and updated can only be used in the database. If throw new exception ("test exception. the transaction must rollback "); after removing the comments of this sentence, we will find that the program did not execute the commit, but directly executed the rollback () in catch and rolled back. Then the insert and update statements are rolled back together.

 

Finally, let's take a look at the basic usage of transactionscope. Transactionscope inherits the idisposable interface, so it is generally used in using. The Code is as follows:

Static void main (string [] ARGs) <br/>{< br/> using (transactionscope scope = new transactionscope ()) <br/>{< br/> sqlconnection sqlconn = new sqlconnection (<br/> configurationmanager. connectionstrings ["connstr"]. connectionstring); <br/> sqlconn. open (); </P> <p> string insertsql = "insert into [transtesttable] values (11, '11 ')"; <br/> string updatesql = "Update [transtesttable] Set [name] = '000000' where [ID] = 11 "; </P> <p> sqlcommand sqlcomm = new sqlcommand (insertsql, sqlconn); <br/> sqlcomm. commandtype = system. data. commandtype. text; <br/> sqlcomm. executenonquery (); </P> <p> sqlcomm = new sqlcommand (updatesql, sqlconn); <br/> sqlcomm. commandtype = system. data. commandtype. text; <br/> sqlcomm. executenonquery (); </P> <p> sqlconn. close (); </P> <p> scope. complete (); <br/>}</P> <p> console. readline (); <br/>}

Defining a transactionscope in using is equivalent to defining a transaction scope, that is, the transaction scope is within using. The program executes two actions, one insert, one update, and finally scope. Complete (); which is equivalent to committing a transaction. If you set scope. after the complete (); is commented out, we will find that both insert and update are rolled back, because in the using scope, if no command is submitted, then when scope is destroyed, all operations are automatically rolled back.

 

The above are the basic usage methods of the three transactions. On this basis, more problems can be raised, such as nested transactions and mixed use of the three methods. I will not list them here.