Realize database transactions with Microsoft.Net (2)

Database transaction

Database transactions are the foundation of other transaction models. The transactions discussed in this article are implemented based on database transactions, and they are only hidden in complex or container-proprietary code for handling transactions. Different database systems have their own rules when a transaction is created. By default, SQL Server works in auto-submitted mode, and will submit immediately after each statement is executed, and the comparison is Oracle requires that you contain a submission statement. But when a statement is executed by OLEDB, it will be attached to the next submitted action. In order to make the following examples apply to Oracle, you need to remove the Begin Transion and Commit Transisting, because the two statements will be run as a separate transaction.

Advantage

l All transaction logic is included in a separate call

l Have the best performance that runs a transaction

l independently of the application

limit

l Transaction context exists only in the database call

l Database code is related to the database system

example:

CREATE Procedure Up_purchaseItem

@Customerid As Int,

@itemid Int,

@itemqty int)

AS

Declare @Orderid Int

Begin Transaction

- Update The Inventory Based on PURCHASE

Update Inventory

Set QtyInstock = QtyInstock - @itemqty

Where inventory.productId = @itemid;

IF @@ error! = 0 goto error_handler

- Insert The Order Into The Database

INSERT INTO Orders

VALUES (@customerid, @itemid, @itemqty, getdate ());

IF @@ error! = 0 goto error_handler

Set @Orderid = @@ identity

Commit transaction

Return @orderid

Error_Handler:

Rollback Transaction

Set nocount off

Return 0

Go

ADO.NET transaction

Creating an ADO.NET transaction is very simple, just a small extension of standard code. As long as you know how to use ADO.NET to access the database, then you will know. The difference is that you only need to put the code in a transaction context.

Or the original ADO.NET class library reference, introduce System.Data and System.Data.SqlClient class libraries in the class that implements transactions, in order to perform a transaction, you need to create a SQLTransation object, you can call your SqlConnection object BeginTransation () Method to create it, once you save the SQLTransation object as a local variable, you can give it a transaction property of your SQLCommand object, or use it as a parameter of the constructor to create SQLCommand. Before executing the SQLCommand action, you must call the begintransaction () method and assign the SQLCommand transaction property.

A single transaction begins, you can perform any number of SQLCommand action, as long as it belongs to the same transaction and connection. Finally, you can call SQLTransation's commit () method to submit a transaction.

The ADO.NET transaction is actually passed to the transaction context to the database layer, and if an error occurs in the transaction, the database will automatically roll back. In your error handling code, check whether the transaction object is a good habit each time you call the rollback () method. One example is when a deadlock occurs while the database is performing automatic rollback. Advantage:

l simply

l and database transactions are almost fast

l Transactions can accepted across multiple databases

l Independent in the database, the proprietary code of different databases is hidden

limit:

Transaction is on the database connection layer, so you need to manually maintain a connection during the transaction.

example:

Public Int PurchaseItem (int Customerid, Int itemid, int itemqty)

{

SqlConnection Con = NULL;

SQLTransaction TX = NULL;

Int ORDERID = 0;

Try

{

Con = New SqlConnection ("Data Source = localhost; user

ID = SA; password =; initial catalog = trans_db; ");

C.Open ();

Tx = con.begintransaction (isolationledLevel.Serializable);

String UpdateSqltext = "Update Inventory Set QtyInstock

= QtyInstock - " itemqty.totring ()

"Where inventory.productid =" itemid.tostring ();

Sqlcommand cmd = new SQLCOMMAND (UpdatesqlText, Con, TX);

cmd.executenonquery ();

// String is 2 SQL Statements: The first is the insert,

The Second Selects the Identity Column

String Insertsqltext = "Insert Into Orders Values

(" Customerid.toString () ", " itemid.toString ()

"," itemqty.toT7tring () ", getdate ()); select @@ identity";

Cmd.comMandText = INSERTSQLTEXT;

// Retrieve the Order id from the identity column

ORDERID = Convert.Toint32 (cmd.executescalar ());

cmd.dispose ();

TX.comMit ();

}

Catch (SQLEXCEPTION SQLEX)

{

// Specific catch for deadlock

IF (sqlex.number! = 1205)

{

TX.rollback ();

}

ORDERID = 0;

Throw (SQLEX);

}

Catch (Exception EX)

{

TX.rollback ();

ORDERID = 0;

Throw (ex);

}

Finally

{

TX.Dispose ();

CON.CLOSE ();

}

ASP.NET transaction

ASP.NET transactions can be said to be the easiest way in the transaction implementation of the .NET platform, you just need to add a line of code. Add an additional property in the page declaration of the ASPX, which is the transaction property, which can have the following values: Disabled, NotSupport, Support, Required, and RequiresNew, these settings, and COM , and enterprise-level services. Typically if you want to run a transaction in the page context, then set to Required. If the page contains a user control, these controls will also be included in the transaction, and the transactions exist at each place in the page.

Advantage:

l Simple implementation, no additional coding

limit:

l All code in the page is the same transaction, such transactions may be large, and maybe we need separate, small transactions

l Transaction is really web layer

example:

Aspx page

<% @ Page Transaction = "Required" Language = "C #" codebehind = "ASPNET_TRANSATION.ASPX.CS"

Inherits = "transtest.aspnet_transaction" autoeventwireup = "false"%>