Using transactions to protect the integrity of data in Ado.net (3)

ado| Data transactions in Ado.net

Ado.net supports two transaction models, which are in the. NET Framework documentation is defined as a guide. The Transaction Guide manual describes the database transactions that should normally be known, and it affects the operation of a single target database. A class uses transaction classes and methods to provide transaction guidance through the transaction boundary described by ado.net display. In the next chapters, I will focus on the business Guide.

Automatic transactions are also possible to coordinate changes across multiple data sources in order to participate in transactions by using classes.　In this scenario, the transaction itself is handled in the outer layer, such as through COM + and DTC. To learn more about automatic transactions, you can refer to related automatic transactions.

Note: When you do not specify transactional behavior in your code, you should be aware of the database transaction default behavior. For example, SQL Server default auto mode, each transaction-SQL statement or success or failure. To understand which default behavior you want to get from the data source in your work, it can help you decide whether or not to show the definition of the transaction, but also to help you understand your system behavior.

Transaction-related class assignments

Ado.net Data provider objects provide transactional functionality through connection, Command, and transaction classes. A typical transaction process is similar to the following:

1. Open transaction through Connetion.begintransaction ()

2. Add a statement or stored procedure to a transaction by setting the command's Command.transaction property.

3. Depending on the provider, choose to use Transaction.save () or Transaction.begin () to create a savepoint or inline transaction to enable local rollback.

4. Commit or ROLLBACK TRANSACTION by using Transaction.commit () or Transaction.rollback ()

All data providers provide the same basic structure to handle transactions, start connected transactions, add instructions, and use transaction objects to commit or rollback.

SQL Server Transactions

The SQL Server data Provider provides basic functionality for transactions see the following classes and members:

Class
Member
Function

SqlConnection
BeginTransaction
Open a new transaction.

SqlCommand
Transaction
Enlist the command associated with the "object in" an open transaction.

SqlTransaction
Save
Create a savepoint to enable a partial rollback.

SqlTransaction
Rollback
Roll back a transaction.

SqlTransaction
Commit
Commit a Transaction



SQL Server data provider (also, for SQL Server, OLE DB provider) does not provide a way to create inline transactions in code, so inline transactions are not really supported in SQL Server. So there's only one way to start a transaction in SqlClient-using sqlconnection.begintransaction (). You cannot open another transaction in a unified connection until the previous transaction is committed or rolled back, so there is only one open transaction for each connection at any time. To use a local rollback, use Sqltransaction.save ().

OLE DB Transactions

OLE DB data providers provide basic functionality for transactions see the following classes and members:

Class
Member
Function

OleDbConnection
BeginTransaction
Open a new transaction.

OleDbCommand
Transaction
Enlist the command associated with the "object in" an open transaction.

OleDbTransaction
Begin
Create a nested transaction, to enable a partial rollback.

OleDbTransaction
Rollback
Roll back a transaction.

OleDbTransaction
Commit
Commit a Transaction



The OLE DB data provider does not provide any method to set the savepoints, but instead there is a begin () method on the OleDbTransaction object. This allows you to create an inline transaction (based on your database) to provide similar functionality. In this case, you can have multiple transactions in a single connection. But what must be remembered is the display of call submission and rollback.

Remember, the embedded transactions of the. NET OLE DB data provider depend on your database and the specified OLE DB provider. In order to successfully implement an embedded transaction, it must be supported by both your database and OLE DB providers.

Oracle transactions

Oracle data providers provide basic functionality for transactions see the following classes and members:

Class
Member
Function

OracleConnection
BeginTransaction
Open a new transaction.

OracleCommand
Transaction
Enlist the command associated with the "object in" an open transaction.

OracleTransaction
Rollback
Roll back a transaction.

OracleTransaction
Commit
Commit a Transaction



Oracle data providers do not allow you to specify savepoints or inline transactions. Savepoints is supported by Oracle, but you cannot implement them for the current Oracle data provider version. The local rollback is not possible if the transaction is either rolled back or committed by Oracle.