Two practical skills for ADO. NET

To give full play to the advantages of ADO. NET, it is important not only to have a comprehensive and in-depth understanding of the ADO. NET programming model, but also to sum up experience and skills in a timely manner. ADO has years of practical experience.. NET. after all, the design goal of NET is not to provide an plug-and-play tool. It will not simplify all programming work to the extent that it can be done by simply clicking the mouse.
ADO. NET contains a large number of objects that represent various logical entities in the data access model, especially the connection and transaction objects. The connection function is to establish a channel for communication with the backend database. The connection object must be created based on a specific. NET data provider. You can create a transaction object on an existing connection object or execute a begin tran SQL statement explicitly. Although the theory is very simple, in fact, there are many uncertainties around the connection and transaction, and they have a crucial impact on the overall stability and efficiency of the application.

How to save the connection string and protect the sensitive information (such as password) that may be contained in the connection string )? How can we design a complete data access policy that takes security (authentication and authorization) into account, but does not significantly affect performance and scalability? If transactions are needed, how can we efficiently implement and control transactions? Are automatic or manual transactions used? These issues must be carefully considered when using ADO. NET.

1. connection string and Connection Pool

Database Connection is an important, limited, and expensive resource. Therefore, using a good connection object is the most basic requirement of any application. The key points of database connection are summarized as follows:

Pay attention to security when saving the connection string.
It should be too late to open the connection. It should be too early to close the connection.
The connection string is the key to access the database. In addition to specifying the data to be accessed, the connection string also contains the proof of why the user can access the data. User identification is the most important factor in determining data access permissions when performing database operations.

1.1 Save the connection string

Currently, hard-coded connection strings have the best performance because they are directly compiled into the application code. However, hardcoded strings affect Program Flexibility. Once the connection string changes, the application must be re-compiled.

Saving the connection string to the outside improves flexibility at the cost of additional overhead for accessing the external string. However, in most cases, the resulting performance overhead is negligible, and the real concern is the security issue. For example, attackers may modify or steal connection strings. Common ways to save a connection string to an external environment are: configuration file, UDL file, and Windows registry.

The. NET Framework configuration file is deployed in plain text files for easy access. If the connection string contains a password, the text format is the biggest defect because the password will be saved in plaintext. You can consider introducing a dedicated encryption/Decryption engine, but you need to complete this part by yourself.

A udl file is a text file for the ole db Provider. That is to say, the SQL Server hosting provider does not support UDL files. The UDL file also has the same security issues as the preceding configuration file, but it does not seem to have many advantages.

Finally, the Windows registry can be used as a natural and secure storage place. Registry is a system knowledge base that stores key information. If you use encryption technology in combination, you can achieve high security. The main disadvantage of using the registry is that it is difficult to deploy. You must create a registry key (which may require encryption) and read data from the registry. Although. NET Framework provides a set of encapsulation classes that call the underlying Win32 API, these classes do not provide encryption. The aspnet_setreg.exe tool can be used to create a registration key under HKEY_LOCAL_MACHINE to save the user name and password, for example, aspnet_setreg.exe-k "Software \ MyData"-u: userID-p: password. This command encrypts the specified user ID and password.

1.2 connection pool Principle

The connection pool allows us to reuse existing connection objects through a buffer pool to avoid creating an object each time we use a connection object. After using the connection pool, a small number of connection objects can meet the needs of a large number of clients.

Each connection pool is associated with an independent connection string and Its transaction context. Each time a new connection is opened, the data provider tries to match the specified connection string with the connection pool string. If the matching fails, the data provider creates a new connection and adds it to the connection pool. After the connection pool is created, it will not be removed unless the process ends. Some people think that this processing method will affect the performance. In fact, it is not necessary to maintain an inactive or empty connection pool.

After the connection pool is created, the system creates some connection objects and adds them to the connection pool until the minimum number of connection objects reaches the rated value. Later, the system will create and add connection objects as needed until the maximum number of connected objects reaches. If no idle connection object is available when the program requests a connection object and the number of objects in the connection pool has reached the upper limit, the request is put into the queue, once a connection is released back to the buffer pool, it is immediately removed and used.

Avoid using programming to construct a connection string. If a connection string is constructed by combining multiple input data, injection attacks are easily accessible. If user-input data is required, strict verification is required.

1.3 close connection

When a connection is closed, the connection object is returned to the connection pool for reuse. However, the actual database connection is not removed. If the connection pool is disabled, the actual database connection is also disabled. Note that after the connection object is used, it should be explicitly closed and returned to the connection pool. Do not release the connection by using the garbage collector. In fact, when the reference of the connection object exceeds the valid range, the connection may not be closed-the Garbage Collector function is removed to represent a physical connection. NET, but this does not mean that the underlying connection is closed.

Call the Close or Dispose method to release the connection back to the connection pool. The connection object will be deleted from the connection pool only when the lifetime ends or a serious error occurs.

1.4 connection pool and security

If all data access operations of an application use the same connection string, the advantage of the connection pool will reach its limit. However, this is just an idealized situation and may conflict with other requirements of the application. For example, if only one connection string is used, it is very difficult to execute security control at the database level.

On the other hand, if each user is allowed to use his/her own connection string (that is, setting a database account for each user separately), there will inevitably be a large number of small connection pools, and many connections will not be reused at all. By convention, the best solution to such problems is to find an appropriate compromise between two extremes. You can set a group of representative public accounts and modify the stored procedure to accept a parameter indicating the user identity. The stored procedure performs different operations based on the input user identity.

Ii. transaction mode

Distributed enterprise applications cannot be separated from transactions. There are two main ways to add the transaction management function to the data access code: manual and automatic.

In the manual mode, the programmer is responsible for writing all the code that configures and uses the transaction mechanism. Automatic (or COM +) transactions are added with explicit properties in the. NET class to specify the transaction characteristics of the runtime object. The automatic mode facilitates the preparation of multiple components to run in the same transaction. Both transaction methods support local or distributed transactions, but the automatic transaction method greatly simplifies distributed transaction processing.

It must be noted that a transaction is a very costly operation, so you must think twice before deciding to use the transaction. If you really need to use transactions, we should try to narrow down the transaction granularity and reduce the lock time and scope for the database. For example, for SQL Server, a single SQL statement does not need to explicitly declare transactions. SQL Server automatically runs each statement as an independent transaction. Manual local transactions are always much faster than other transactions because they do not need to involve DTC (Distributed Transaction Coordinator ).

Manual transactions and automatic transactions should be regarded as two different and mutually exclusive technologies. If you want to perform transactional operations on a single database, manual transactions are preferred. When a single transaction spans multiple remote databases, or a single transaction involves multiple resource managers (for example, one database and one MSMQ Resource Manager), automatic transactions are prioritized. In any case, we should try our best to avoid mixing the two transaction modes. If performance is not particularly important, you can consider using automatic transactions even if you only operate on one database to make the code simpler (but slower ).

All in all, to improve the quality of the database access code, you must have a deep understanding of the ADO. NET object model and use various techniques flexibly according to the actual situation. ADO. NET is a public API, various applications-whether it is a Windows form application, ASP page or Web service, you can use ADO.. NET to access the database. However, ADO. NET is not a black box that accepts input and spouts results, but a toolbox composed of many tools.