Ado. Net BASICS (transactions, General Data factories)

ArticleDirectory

• Transactions
• Data Provider-independent code

Transactions

A transaction is a group of operations that must succeed or fail. The goal of a transaction is to ensure that the data is always in a valid and consistent state. For example, the transfer operation.

 

Transactions have four features called acid attributes. acid is short for the following concepts:

• Atomic (Atomicity): all steps in the transaction must succeed or fail at the same time.
• Consist (consistency): the transaction converts the underlying database to a stable State.
• Isolated (isolation): Every transaction is an independent entity, and a transaction should not affect other tasks that run at the same time.
• Durable.

The ideal features of these transactions may not always be achieved. When executing a transaction, RDBMS needs to lock the data so that other users cannot access it. The more locks there are, the larger the granularity is. When a transaction is executed, other users are more likely to complete some tasks. That is to say, we need to make a balance between user concurrency and isolation..

 

 

Transactions and ASP. NET ApplicationsProgram

ASP. NET supports three basic types of transactions:

• Stored Procedure transactions: These transactions are completely processed in the database,Stored Procedure transactions provide the best performance because only one round trip to the database is required. The disadvantage is that you need to use SQL statements to write transaction processing logic..
• Client-initiated (ADO. Net) Transactions:These transactionsCodeControl by programming. They use the same commands as stored procedure transactions, and the Code uses ADO. Net objects that encapsulate these details. The disadvantage is that an additional round-trip to and from the database is required when the transaction starts and is committed..
• COM + transaction: When COM + uses the two-step commit protocol, additional overhead will always occur. COM + transactions are used only when transactions need to span multiple resource managers. A com + can span the interaction between SQL Server and Oracle databases.

 

Although ADO. NET provides good support for transactions, it should not be used at will. Each use will impose additional burden on the system. In addition, transactions will lock some rows in the table, and unnecessary transactions will damage your application performance.

Transactions should be used in accordance with these practical principles for optimal results:

• The transaction should be as short as possible
• Do not use select to query returned data in the transaction.
• If the transaction does need to obtain records, it should only obtain the records that are actually needed, which can reduce the number of locked Resources
• If possible, use transactions in the stored procedure instead of using transactions in ADO. net. Such transactions can be started and compiled faster, because the database server does not need to interact with the client (Web application.
• Avoid using transactions with multiple independent batch processing tasks, and use each batch processing task as a single transaction
• Avoid affecting the update of large batches of records as much as possible

 

1. Stored Procedure transactions

As long as possible, the best place to store transactions is the stored procedure code. It is most likely to achieve the best performance, and all activities are performed on the data source without any network communication. In short, the shorter the transaction span, the better the database concurrency, and the fewer database requests to be serialized.

The following pseudocode demonstrates how to transfer funds between two accounts. It is a simplified version that allows the number of accounts in the village to be negative:

 
Create ProcedureTransferamount
 

 
(
 

 
@ Amount money,
 

@ Id_aInt,
 

 
@ Id_ BInt
 

 
)
 

 
As
 

 
Begin Transaction
 

 
UpdateAccountsSetBalance = balance + @ amountWhereAccountid = @ id_a
 

 
If(@ Error> 0)
 

 
GotoProblem
 

 
UpdateAccountsSetBalance = balance-@ amountWhereAccountid = @ id_ B
 

 
If(@ Error> 0)
 

 
GotoProblem
 

 
-- No error
 

 
Commit
 

 
Return
 

-- An error occurred.
 

 
Problem:
 

 
Rollback
 

 
Raiserror('Could not update .', 16,1)
 

When @ error is used in transact-SQL, the value must be checked immediately after each step is completed !!! Because @ error is automatically reset to 0 after an SQL statement is successfully executed..

 

If you use SQL server2005 or an updated version, there is a more modern try/catch structure to achieve the above example. (After all, the GOTO statement has a lot of criticism)

 
Create ProcedureTransferamount
 

 
(
 

 
@ Amount money,
 

 
@ Id_aInt,
 

 
@ Id_ BInt
 

 
)
 

 
As
 

 
BeginTry
 

 
Begin Transaction
 

 
UpdateAccountsSetBalance = balance + @ amountWhereAccountid = @ id_a
 

 
UpdateAccountsSetBalance = balance-@ amountWhereAccountid = @ id_ B
 

 
-- When an error occurs, the catch block is entered. You can submit the Catch Block directly here.
 

 
Commit
 

 
EndTry
 

 
BeginCatch
 

 
If(@ Trancount> 0)
 

Rollback
 

 
-- Record exception information
 

 
Declare@ Errmsg nvarchar (4000), @ errseverityInt
 

 
Select@ Errmsg = error_message (), @ errseverity = error_severity ()
 

 
Raiserror(@ Errmsg, @ errseverity, 1)
 

 
EndCatch
 

This example checks @ trancount to determine whether a transaction is in progress. (Variable @ trancount calculates the number of transactions in the current connection. The begin transaction statement adds 1, while the rollback and commit values minus 1 .)

The raiserror statement is used to prevent errors from being swallowed up by the Catch Block. Ado. Net encapsulates the message as a sqlexception object, which needs to be captured in. net.

 

2. Ado. net transaction caused by the client

Most ADO. NET data providers support database transactions.

The transaction class has two key methods:

• Commit ()
• Rollback ()

The following example shows how to insert two records to the Employees table:

 
Protected VoidPage_load (ObjectSender, eventargs E)
 

 
{
 

StringConnstr = webconfigurationmanager. connectionstrings ["Northwind"]. Connectionstring;
 

 
Sqlconnection conn =NewSqlconnection (connstr );
 

 
Sqlcommand cmd1 =NewSqlcommand ("Insert into employees (lastname, firstname) values ('job', 'tester ')", Conn );
 

 
Sqlcommand cmd2 =NewSqlcommand ("Insert into employees (lastname, firstname) values ('Harry ', 'sulivan ')", Conn );
 

Sqltransaction TRAN =Null;
 

 
Try
 

 
{
 

 
// Open the connection and create the transaction
 

 
Conn. open ();
 

 
TRAN = conn. begintransaction ();
 

 
// Enlist two commands in the transaction
 

 
Statement 1.transaction = Tran;
 

Export 2.transaction = Tran;
 

 
// Execute both commands
 

 
Statement 1.executenonquery ();
 

 
Listen 2.executenonquery ();
 

 
// Commit the transaction
 

 
Tran. Commit ();
 

 
}
 

 
Catch
 

 
{
 

// In the case of error, Rool back the transaction
 

 
Tran. rollback ();
 

 
}
 

 
Finally
 

 
{
 

 
Conn. Close ();
 

 
}
 

 
}
 

It is not enough to create and submit a transaction. You must set the command. the transaction attribute is a transaction object, so that the command object is explicitly included in the transaction. If a command not in the current transaction is executed in the transaction, an error is returned.

 

Transaction isolation level

The isolation level determines the sensitivity of transactions to data affected by other transactions. By default, when two transactions run independently, the data inserted by the first transaction is invisible to other transactions before the end of the first transaction.

The concept of isolation level is closely related to the concept of lock, because determining the isolation level of transactions is to determine the type of the required lock.

• Shared lock: A lock generated when a transaction reads data from the database. When a table, row, or a range of shared locks exist, other transactions cannot modify the corresponding data. However, multiple users can use the shared locks to concurrently read data.
• Exclusive lock: prohibit multiple transactions from modifying data at the same time. When a transaction updates data and no other transaction has locked the data, an exclusive lock is generated. When an exclusive lock exists, other users cannot read or update data.

In the SQL Server Stored Procedure, use the SET transaction isolation level command to set the isolation level. In ADO. net, you can input the isolationlevel enumeration value to the connection. begintransaction () method, as shown in the following table:

Readuncommitted	No shared lock or exclusive lock. Will causeDirty Data ReadingBut it can improve the performance.
Readcommitted	When the data is read by the transaction, a shared lock is generated to avoid dirty data reading. However, the data may have been modified before the transaction ends.Non-repeated readOrUnreal line. (SQL Server default isolation level)
Snapshot	Store a copy of the data that the transaction is accessing, so a transaction will not see any modifications made by other transactions. This isolation level reduces congestion because other transactions can read data from data copies when they are reading data locked by the Snapshot isolation transaction lock. This option is only supported by SQL Server 2005 and must be enabled at the database level.
Repeatableread	All data involved in the query is added with a shared lock. This prevents others from modifying the data, avoiding repeated reads, and may still cause phantom rows.
Serializable	A series of locks on the data used prohibit other users from updating or inserting rows within this range. It is the only isolation level that can delete phantom rows, but it has a very negative impact on concurrent access, which is rarely used in multi-user scenarios.

Related database terms:

• Dirty read:Reads data from other uncommitted transactions, but the transaction may be rolled back..
• Unrepeatable read: If repeated read is allowed, different data may be obtained when multiple queries are performed in the same transaction. This is because the transaction only reads data during the process and cannot prevent other users from modifying the data. To prevent repeated reads, the database server needs to lock the row read by the transaction.
• Phantom row: indicates the row that does not appear in the initial read but is subsequently read in the same transaction. When another user inserts a record during the transaction, phantom rows may occur. To prevent phantom rows, a Range lock should be used according to the WHERE clause when the transaction queries the database.

Whether these phenomena are harmless small defects or potential errors depends on your specific needs. In most cases,Unrepeatable reading and phantom rows are only a small problem. It is too costly to use locks to prevent them from being concurrent. Readcommitted applies to most transactions. Let's take a look at the comparison of different isolation levels:

Isolation level	Dirty read	Non-repeated read	Illusory data	Concurrency
Read uncommitted)	Yes	Yes	Yes	Best
Read committed)	No	Yes	Yes	Good
Snapshot)	No	No	No	Good
Repeatable read)	No	No	Yes	Average
Serializable)	No	No	No	Worst

 

 

Data Provider-independent code

Create a factory

The basic idea of the factory model is to use a single factory object to create all the objects related to the required provider. Then, you can use a group of common base classes to fully interact with objects related to these providers.

There is a standard class that dynamically discovers and creates the factory you need. This class is system. Data. Common. dbproviderfactories. It provides a getfactory () method, which returns the corresponding factory according to the provider name.

String factory = "system. Data. sqlclient ";
Dbproviderfactory provider = dbproviderfactories. getfactory (factory );

All factories are inherited from dbproviderfactory. If you only use members of dbproviderfactory, the code you write can be used together with other factories. The disadvantage of the previous Code lies in the string passed by the provider name, which should usually be configured in Web. config.

To make the dbproviderfactory class work normally, the provider must have a factory registered in machine. config or web. config.

 

Create an object with a factory

Again, you must assume that you do not know the provider you are using, so that you can only interact with the object created by the factory through the standard base class. A simple example can help you better understand how these fragmented concepts work together.

Configure the connection string, program name, and query statement for the example in the web. config file:

 
<Configuration>
 

 
<Connectionstrings>
 

 
<Add Name= "Northwind" Connectionstring= "Data Source = localhost; initial catalog = northwind; Integrated Security = sspi"/>
 

</Connectionstrings>
 

 
<Appsettings>
 

 
<Add Key= "Factory" Value= "System. Data. sqlclient" />
 

<Add Key= "Employeequery" Value= "Select * from employees" />
 

 
</Appsettings>
 

 
</Configuration>
 

Factory-based code:

 
Protected VoidEmployeesquery ()
 

{
 

 
// Get the factory
 

 
StringFactory = webconfigurationmanager. appsettings ["Factory"];
 

 
Dbproviderfactory provider = dbproviderfactories. getfactory (factory );
 

 
// Use this factory to create a connection
 

 
Dbconnection conn = provider. createconnection ();
 

 
Conn. connectionstring = webconfigurationmanager. connectionstrings ["Northwind"]. Connectionstring;
 

 
// Create the command
 

 
Dbcommand cmd = provider. createcommand ();
 

 
Cmd. Connection = conn;
 

 
Cmd. commandtext = webconfigurationmanager. receivettings ["Employeequery"];
 

 
// Open the connection and get the datareader
 

 
Conn. open ();
 

 
Dbdatareader reader = cmd. executereader ();
 

 
}
 

If the database is changed, you only need to modify the data provider name line of the configuration file..