13th Lecture: go deep into ADO. NET development-Advanced Data Access Technology

2005.4.8 Fu Zhongkai

Basic Preview

Familiar with Microsoft ADO. NET

Understand basic Web Services Development

Understand. NET file set operations

 

Topics

Concurrency Problems

Connection Pool

Transactions

Data access layer that can be extracted

 

Concurrency Problems

Concurrency issues must be addressed in a disconnected Architecture

When two (or more) Users obtain and modify the same record and try to maintain their own modifications at the same time, there will be a conflict: Dirty read u, non-repeated read, phantom read

When multiple updates are submitted, some updates cannot be completed correctly.

Pessimistic and optimistic (constructed by ADO. NET) concurrency lock

Pessimistic lock: locks both read and write

Optimistic lock: only write locks and read locks (ADO. NET)

 

Handling of concurrency issues in ADO. NET

DataSet objects connected to DataAdapter objects use optimistic locks to handle record content conflicts

The DataAdapter Configuration Wizard can modify the Update and Delete statements to check whether concurrency issues have occurred.

When ContinueUpdateOnError is false (default value), DBConcurrencyException is thrown in the case of the first conflict.

Whether it is true or false, the update operation will be canceled for data rows with concurrency problems. If the difference between them is true, other rows will be updated. If it is false, an exception will be thrown immediately.

Capture the exception and notify the user, or handle the exception or prompt the user what to do next

 

Demonstration 1

Handling concurrency Problems

Open two forms at the same time, Load the same data, modify the rows 1 and 2 on the left to save the data, and modify the rows 1 and 2 on the right to throw a concurrency error exception, and 0 rows are changed, that is, the update is not completed.

It only prompts the dialog box for the first concurrency problem, but does not prompt an error for the second concurrency problem, because once it throws an exception, it will not operate on the subsequent data.

Another example is to set the attribute of ContinueUpdateOnError to True and then update it. The GetErrors () method can retrieve conflicting data rows.

Three rows of data are updated on the left, and three rows of data are updated on the right. Two rows of data have concurrent conflicts.

The result does not throw an exception dialog box, and any update without a concurrency conflict will be updated successfully.

 

Connection Pool

Connection Pool can greatly improve performance and scalability

Avoid high consumption caused by connection Creation

Adjust the connection pool by adding the following attributes to the connection string

Pooling = true; Max Pool Size = 5; Min Pool Size = 3;

The above string will open the connection pool, and make sure there are at least three connections in the connection pool, and a maximum of five connections

When the maximum connection is reached, requests that open a new connection will be queued for a configurable period of time.

The default wait time is 15 seconds. If a connection is not obtained after 15 seconds, a timeout exception is thrown.

When OleDbConnection is used, if you want to disable the connection pool, add "ole db Services =-4" to the connection string"

 

Note:

The connection is pooled by precisely matching the connection string. The pooling mechanism is sensitive to spaces between name-value pairs.

For example, the following two connection strings will generate a separate pool because the second string contains an additional null character.

"Integrated Security = SSPI; Database = Northwind"

"Integrated Security = SSPI; Database = Northwind"

Close method puts the connection back into the connection pool

The Dispose method directly destroys the connection, instead of releasing it back to the connection pool.

 

Demonstration 2

Connection Pool

The simple connection pool is not closed to demonstrate how to view the connections in the connection pool. Open the SQL Server Manager and you will see three links in the connection pool waiting for activation.

Advanced connection pool: creates multiple threads to call the Go method, starts the thread, and waits for the thread.

A timeout exception is thrown when the queue exceeds 15 seconds.

You can modify the connection string to increase the timeout time or the maximum number of connections.

The 50 threads continuously obtain the SQL version number. Each thread executes the SQL 100 times. After all the threads run the SQL 5000 times, the SQL version number is obtained.

When using a connection, we have no choice but to open the connection. After opening the connection, we should also close it in time.

 

Transactions

ACID principles: atomicity, consistency, isolation, and Durability

The transaction action either completes all the actions or does not do any action.

Submit or roll back all modifications at the same time

Not all SQL statements can appear in transactions.

For example, database creation and table modification operations are not allowed to appear in transactions.

ADO. NET transactionOnly applicable to a single connection

The SqlTransaction object is returned from the SqlConnection. BeginTransaction () method.

 

SqlTransaction class

SqlTransaction trans = conn. BeginTransaction ();

Instantiate transaction object

Isolation level enumeration (IsolationLevel): Chaos, ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Unspecified

Serializable is the highest level of isolation

The higher the isolation level, the better the concurrency problem. By default, ADO. NET uses ReadCommitted-level isolation.

Processing: Insert, Update, Delete

Either submit all the modifications: trans. Commit ();

Or, if an error occurs, withdraw all modifications: trans. RollBack ();

 

Isolation level

Serializable provides the highest isolation level, but only the lowest execution efficiency

Serializable is a serialization method, and is accessed by means of serial processing, so the efficiency is the lowest.

 

Demonstration 3

Local transaction operations

 

Advanced transactions

To execute cross-database transactions, You need to register a link to the Distributed Transaction Coordinator (DTC) through System. javasiseservices.

Distributed transactions can also be localized, but they have relatively high overhead because local transactions do not need to interact with the distributed coordinator.

Add the System. EnterpriseServices. Transaction attribute to the middle-Layer Web service.

TransactionOption enumeration: Disable, NotSupported, Required, RequiresNew, Supported

TransactionIsolationLevel enumeration: Chaos, ReadCommitted, ReadUnCommitted, RepeatableRead, Serializable

Serializable is the highest level of isolation

The Enlist entry in the connection string indicates whether the connection is involved in the transaction operation.

True indicates transaction operation.

When Enlist = false in the connection string, you can call SqlConnection. EnlistDistributedTransaction to execute transaction operations.

 

How to control distributed transactions

Annotate the method using the AutoComplete attribute. If the method is normal, the transaction is automatically committed, and if an exception occurs, the rollback operation is automatically performed.

Call the static methods SetComplete or SetAbort of the ContextUtil class to implement transaction commit and rollback operations.

 

Demonstration 4

Cross-database transactions

To use COM + transactions, you need to add the EnterpriseServices reference.

A label is added to the method, indicating the operation to execute the transaction. An exception will be thrown at the end of the method to allow the transaction to roll back automatically. Only operations with the Enlist in the middle being false and the EnlistDistributedTransaction method not called will not be rolled back, and other operations will be rolled back with the transaction.

 

Considerations for using transactions

In a group of operations, transactions are used only when ACID features need to be ensured.

Reduces the transaction granularity to minimize the time required to maintain the database lock.

Do not use transactions for a single SQL statement. SQL Server automatically processes and executes each statement as a single transaction.

 

Hierarchical application architecture

Multi-tier applications divide code into different logic code Layers

A common design scheme is a three-layer architecture design: presentation layer, business logic layer, and data access layer.

Reasonable hierarchical design brings many benefits: code reuse, scalability, encapsulation, loose coupling, and high aggregation.

We can design a data access layer that can be extracted to completely abstract data storage.

 

Hierarchical application architecture-Concept

The three layers are logically divided, and physical Division does not have to follow this strict division.

 

Data access layer-Concept

Code layer between application business logic and data storage

Contains all the data access code of the application; provides CRUD operation functions

The business logic layer is loosely coupled with the data access layer.

 

Data extraction access layer

The data extraction access layer provides good scalability for applications.

The design of the data extraction access layer can be easily modified, updated, and stored without re-compiling other layers

Loose coupling through interfaces

Extracts components through the interface-based factory Mode

Extract the data access layer from the business logic layer

 

Interface-based programming-Concepts

The interface is similar to the contract

Interface-based programming eliminates the coupling of methods implemented from actual code

Providing a polymorphism Mechanism

Implement the "removable" component

 

Extracted components-Concepts

. NET allows dynamic loading of file sets and classes through Assembly. Load () and Assembly. CreateInstance ().

Point to a specific data access layer class through Configuration

"New" or "Improved" (different) components can be "inserted" into existing applications

No need to re-compile the code that depends on the Interface

Very low coupling means great scalability

 

Demonstration 5

Data extraction access layer

GetDaL is the data access layer. It dynamically loads a file set based on input parameters to create a corresponding instance.

All three interfaces are components. We Abstract Three access methods of different colors into IDaL interfaces. the business logic layer does not need to care about which object or component to access. It only needs to call GetDaL to obtain the corresponding color.

 

Summary

Concurrency is a problem that must be addressed in the disconnected data model.

Connection Pool can improve performance and scalability

Transactions ensure the atomicity, continuity, isolation, and durability of data updates.

The data access layer that can be extracted is a powerful method to achieve scalability through loose coupling.

2010.10.22