Understanding and Application of transactions in ADO. net

A transaction refers to a unit of work. In this case, all operations on the database are either executed successfully or not, and "no" is used, cheng Ren is more appropriate.

(1) In ADO. in. net, use the begintransaction method of the connection object to initialize the transaction object, assign the object to a property transaction of the command object, and perform operations on the database. If yes, call the commit method of the transaction object to commit the transaction. If the transaction fails, call the rollback method of the transaction object to roll back the transaction. The specific operation code is as follows:

.....

Using system. Data. sqlclient;

......

String strsql = string. empty;

Using (sqlconnection con = new sqlconnection ("database connection string "))

{

Con. open ();

Sqltransaction trans = con. begintransaction ();

Sqlcommand cmd = NULL;

Cmd. Connection = con;

Cmd. Transaction = trans;

Try

{

Strsql = "insert into users values ('admin', 'admin ')";

Cmd. commandtext = strsql;

Cmd. executenonquery ();

Strsql = "Update users set Pwd = '" + admin888 + "'where uid = admin ";

Cmd. commandtext = strsql;

Cmd. executenonquery ();

Trans. Commit ();

}

Catch (sqlexception ex)

{

Response. Write (ex. Message );

Trans. rollback ();

}

}

 

(2) distributed transactions

During website development, we often encounter such a situation. Due to the large Website access volume and a large number of registered users, it is difficult for a server to store a large number of user information. What should we do?

Generally, two or more servers are used to store user login information on the SQL Server database server in the central data center, put the user's item consumption information in another server, so that each time a new user is added, the user information must be added to the database server of the central data center, at the same time, the user and related consumption information must be added to another server. Transaction problems are also involved here. If the increase succeeds, the user information should be added to both database servers, if an error occurs on one server and the user information cannot be saved, the data cannot be saved on the other server. This means that the overall commit of the transaction fails and is rolled back to the status before the operation.

In this case, when multiple database servers are involved, transactions are executed on two or more sqlconnection objects, which is distributed transaction processing.

Distributed Transaction Processing uses the transactionscope class in the namespace system. transaction. before using the class, add system. Transactions. dll

The specific operation code is as follows:

......

......

String strsql = string. empty;

Using (transactionscope scope = new transactionscope ())

{

Sqlconnection con1 = new sqlconnection ("database connection string 1 ");

Sqlconnection con2 = new sqlconnection ("database connection string 2 ");

Try

{

Con1.open ();

Strsql = "insert into users values ('admin', 'admin ')";

Sqlcommand cmd1 = new sqlcommand (strsql, con1 );

Statement 1.executenonquery ();

 

Con2.open ();

Strsql = "insert into consumption values ('20140901', 'admin ')";

Sqlcommand cmd2 = new sqlcommand (strsql, con2 );

Listen 2.executenonquery ();

}

Catch (sqlexception ex)

{

Response. Write (ex. Message );

}

Scope. Complete ();

Con1.close ();

Con2.close ();

}

.........

Distributed transactions are not handled by ADO. is managed by the MSDTC Service, which is based on the COM + technology. DTC is the abbreviation of Distributed Transaction Coordinator, which is not only the meaning of the distributed service coordinator, we can also see its role.

The distributed transaction management supported by the transactionscope class is not completed by the transactionscope class, but committed to the DTC service on the local machine. Therefore, when using distributed transactions, to enable the local DTC service, run the Net start MSDTC command.