C # principles and practices of database transactions (3)

Another extreme Error
New users with confidence may be excited by some of their knowledge. Database The same is true for quasi-developers of Database Service Processing. They are eager to apply the transaction mechanism to their data processing. Program In each module. Indeed, the transaction mechanism looks so attractive-concise, wonderful, and practical, of course I want to use it to avoid all possible errors-I even want to use transactions to wrap up my data operations from start to end.
Let's take a look. Next I will start from creating a database:

Using system; Using system. Data; Using system. Data. sqlclient; Namespace aspcn { Public class dbtran { File: // execute Transaction Processing Public void dotran () { File: // create a connection and open it Sqlconnection myconn = getconn (); Myconn. open (); Sqlcommand mycomm = new sqlcommand (); Sqltransaction mytran; Mytran = myconn. begintransaction (); File: // bind the following connection and transaction object Mycomm. Connection = myconn; Mycomm. Transaction = mytran; File: // try to create a database testdb Mycomm. commandtext = "create database testdb "; Mycomm. executenonquery (); File: // submit the transaction Mytran. Commit (); } File: // get data connection Private sqlconnection getconn () { String strsql = "Data Source = localhost; Integrated Security = sspi; user id = sa; Password = "; Sqlconnection myconn = new sqlconnection (strsql ); Return myconn; } } Public class test { Public static void main () { Dbtran trantest = new dbtran (); Trantest. dotran (); Console. writeline ("transaction processing has been completed successfully. "); Console. Readline (); } } } //---------------

Unprocessed exception: system. Data. sqlclient. sqlexception: the create database statement is not allowed in Multi-statement transactions.

At system. Data. sqlclient. sqlcommand. executenonquery () At aspcn. dbtran. dotran () At aspcn. Test. Main ()

Note:SQLStatements cannot appear in transactions:

Alter Database	Modify Database
Backup log	BackupLogs
Create Database	Create a database
Disk init	Create a database or Transaction Log Device
DROP DATABASE	Delete Database
Dump transaction	Dumping transaction logs
Load Database	Load database backup copies
Load transaction	Load transaction log backup copies
Reconfigure	Update the current configuration (config_value column in The sp_configure result set) value of the configuration option changed using the sp_configure system stored procedure.
Restore database	Restore the Database Backup Using the BACKUP command
Restore log	Restore the log backup using the BACKUP command
Update statistics	Updates the key value distribution information of one or more statistical groups (sets) in the specified table or index view.

In addition to these statements, you can use any SQL statement in your database transactions.

Transaction rollback

One of the four features of a transaction is atomicity. It means that a transaction composed of a specific operation sequence is either completed or not done. If an unknown Unexpected error occurs during transaction processing, how can we ensure the atomicity of the transaction? When a transaction is aborted, you must perform a rollback operation to eliminate the impact of the executed operation on the database.
In general, it is better to use rollback for exception handling. Previously, we have obtained a database update program and verified its correctness. Modify it a little bit and we can get:

// Rollback. CS Using system; Using system. Data; Using system. Data. sqlclient; Namespace aspcn { Public class dbtran { File: // execute Transaction Processing Public void dotran () { File: // create a connection and open it Sqlconnection myconn = getconn (); Myconn. open (); Sqlcommand mycomm = new sqlcommand (); Sqltransaction mytran; File: // create a transaction Mytran = myconn. begintransaction (); File: // from then on, data operations based on the connection are considered as part of the transaction. File: // bind the following connection and transaction object Mycomm. Connection = myconn; Mycomm. Transaction = mytran; Try { File: // locate the pubs Database Mycomm. commandtext = "use pubs "; Mycomm. executenonquery (); Mycomm. commandtext = "Update roysched set royalty = royalty * 1.10 where title_id like 'pc % '"; Mycomm. executenonquery (); File: // The following statement is used to create a database. Manufacturing An error occurred. Mycomm. commandtext = "create database testdb "; Mycomm. executenonquery (); Mycomm. commandtext = "Update maid set maid = maid * 1.20 where title_id like 'ps % '"; Mycomm. executenonquery (); File: // submit the transaction Mytran. Commit (); } Catch (exception ERR) { Mytran. rollback (); Console. Write ("transaction operation error, rolled back. System Information: "+ err. Message ); } } File: // get data connection Private sqlconnection getconn () { String strsql = "Data Source = localhost; Integrated Security = sspi; user id = sa; Password = "; Sqlconnection myconn = new sqlconnection (strsql ); Return myconn; } } Public class test { Public static void main () { Dbtran trantest = new dbtran (); Trantest. dotran (); Console. writeline ("transaction processing has been completed successfully. "); Console. Readline (); } } }

First, we made a mistake in the middle-using the create database statement described earlier. Then, the Catch Block for exception handling contains the following statement:
Mytran. rollback ();
When an exception occurs, the program executes the stream to jump to the catch block. The first statement is this statement, which rolls back the current transaction. In this section, we can see that before the create database, there is already an operation to update the database -- the value of the royalty field of all books whose title_id field starts with "PC" in the royal sched table of the pubs database is increased by 0.1 times. However, the rollback caused by exceptions does not happen to the database. The rollback () method maintains the consistency of the data base and the atomicity of transactions.