Ado. NET Practical Example Introduction

To give full play to the advantages of ADO, not only need a comprehensive, in-depth understanding of the ADO programming model, timely experience, skills are also very important. ADO has many years of practical experience with ADO. NET provides a richer, more powerful tool based on this, however, ADO. NET's design goal is not to provide a plug-and-play tool, it does not simplify all the programming work to the point that only the mouse clicks can be done.
Ado. NET contains a large number of objects representing the various logical entities in the data access model, especially the connection, transaction, and the most important objects. The purpose of the connection is to establish a channel to communicate with the backend database, and the creation of the connection object must be specific. NET Data provider-based. The transaction object can be created on an existing connection object, or it can be created by explicitly executing a BEGIN TRAN SQL statement. Although the theory is simple, in fact, there are many uncertainties around connections and transactions, and they have a crucial impact on the stability and efficiency of the application as a whole.

How do I save the connection string, protecting sensitive information (such as passwords) that might be contained in the connection string? How do you design a sophisticated data access strategy that takes into account security (that is, authentication, authorization) without having too much impact on performance and scalability? If you need to use transactions, how do you implement and control transactions efficiently? Do you use automatic or manual transactions? These problems must be carefully considered when using ADO.

One, connection string, connection pool

Database connectivity is an important, limited, and expensive resource, so using a good connection object is the most basic requirement for any application. The key points for using database connections can be summarized as follows:

The connection string should be saved with security in mind.
Open the connection should be late, close the connection should be early.
The connection string is the key to accessing the database. In addition to explaining the data to be accessed, the connection string also contains identification of why users can access those data. When performing database operations, user identification is the most important factor in determining data access rights.

1.1 Saving the connection string

Currently, hard-coded connection strings have the best performance because they are compiled directly into the code of your application. However, hard-coded strings affect the flexibility of the program, and once the connection string changes, the application must be recompiled.

Saving the connection string externally increases flexibility at the expense of additional overhead required to access external strings. But in the vast majority of cases, the resulting performance overhead can be negligible, and the real concern is the security issue. For example, an attacker could modify and steal a connection string. Common ways to save connection strings to an external environment are: Configuration files, UDL files, Windows registry.

. NET Framework configuration files are deployed as plain text files for easy access. If the connection string contains a password, the text format will be the biggest flaw, because the password will be saved in clear text. Consider introducing a dedicated encryption/decryption engine, but this part of the work needs to be done by the developer themselves.

UDL files are text files for use by OLE DB providers, which means that the SQL server hosting provider does not support UDL files. UDL files also have the same security issues as the previous configuration files, which in general appear to be of little advantage.

Finally, the Windows registry can be used as a natural and secure storage location. The registry is a system knowledge base that holds critical information, which can achieve high security if combined with encryption technology. The main disadvantage of using the registry is the hassle of deployment, requiring the creation of a registration key (which may also be encrypted) and reading data from the registry. Although the. NET Framework provides a set of encapsulation classes that invoke the underlying Win32 API, none of these classes provide encryption. Aspnet_setreg.exe tool can be used to create HKEY_LOCAL_MACHINE under the registration key to save the user name and password, for example: Aspnet_setreg.exe-k "Software\mydata"-u:userid-p: Password The command encrypts the specified user ID and password.

1.2 Connection Pooling principle

Connection pooling allows us to reuse existing connection objects through a buffer pool, avoiding the need to create a new object each time a connection object is used. With a connection pool, a small number of connection objects can meet the needs of a wide range of clients.

Each connection pool is associated with a separate connection string and its transaction context. Each time a new connection is opened, the data provider attempts to match the specified connection string to the string of the connection pool. If the match fails, the data provider creates a new connection and joins it to the connection pool. After the connection pool is created, it will not be removed unless the process is finished. Some people think that this kind of processing can affect performance, but in fact, it does not require much overhead to maintain an inactive or empty connection pool.

After the connection pool is created, some connection objects are created and joined to the connection pool until the minimum number of connected objects is reached. Later, the system will create and join the connection object as needed until the maximum number of connection objects is reached. If the program requests a connection object when no idle connection objects are available, and the number of objects in the connection pool has reached the upper limit, the request is placed in the queue and is immediately removed once the connection is released back to the buffer pool.

Avoid constructing connection strings in a programmatic way. If the connection string is constructed by merging multiple input data, it is easy to inject an exploit. If you must use the data entered by the user, be sure to verify it rigorously.

1.3 Closing the connection

When a connection is closed, the connection object is returned to the connection pool for reuse, but the actual database connection is not removed. If connection pooling is disabled, the actual database connection is also closed. It must be emphasized here that when the connection object is finished, it should be explicitly closed and returned to the connection pool, not relying on the garbage collector to release the connection. In fact, when a reference to a Connection object goes beyond the valid range, the connection is not necessarily closed-the function of the garbage collector is to dismantle the physical connection. NET wrapper object, but this does not mean that the underlying connection is also closed.

Call the close or Dispose method to release the connection back to the connection pool. The connection object is removed from the connection pool only if the lifetime ends or a critical error occurs.

1.4 Connection pooling and security

If all of the data access operations for an application use the same connection string, the benefits of the connection pool will reach its limit. However, this is an idealized situation and is likely to conflict with other requirements of the application. For example, if you use only one connection string, it is difficult to perform security control at the database level.

On the other hand, if you let each user use their own connection string (that is, set the database account for each user), there is a large number of small connection pools, many connections will not be reused at all. By convention, the best solution to such problems is to find an appropriate compromise between the two extremes. We can set up a representative set of public accounts and modify the stored procedure to accept a parameter that represents the user identity, and the stored procedure performs different actions based on the incoming user identity.

Second, the transaction mode

Distributed enterprise applications cannot be separated from transactions. There are two main ways to include transaction management functionality in data access code: manual, Automatic.

In manual mode, the programmer is responsible for writing all the code that configures, uses the transaction mechanism. Automatic (or COM +) transactions are in the. NET class to specify the transaction attributes of the run-time object. Automatic mode facilitates the configuration of multiple components to run within the same transaction. Both types of transactions support local or distributed transactions, but the automatic transaction mode greatly simplifies distributed transaction processing.

It is important to note that transactions are a costly operation, so it is important to think twice before deciding to use a transaction. If you do need to use transactions, it is necessary to minimize the granularity of transactions, reduce the lock time on the database, lock range. For example, for SQL Server, a single SQL statement does not need to explicitly declare a transaction, and SQL Server automatically runs each statement as a separate transaction. A manual local transaction is always much faster than other transactions because it does not need to involve the DTC (distributed Transaction Coordinator).

Manual transactions, automatic transactions, should be treated as two different, mutually exclusive technologies. If you want to perform transactional operations on a single database, take precedence over manual transactions. When a single transaction spans multiple remote databases, or a single transaction involves multiple resource managers (for example, a database and an MSMQ resource manager), automatic transactions are preferred. In any case, it is important to avoid mixed use of the two transaction patterns. If performance is not particularly important, you might consider using automatic transactions for only one database operation, making the code more concise (but slightly slower).

　　In a word, to improve the quality of the database access code, we must understand the ADO model and apply various techniques flexibly according to the actual situation. Ado. NET is a common API, a variety of applications-whether Windows Forms applications, ASP pages, or Web services, can access the database through ADO, but ADO is not a black box that accepts input and spits out results, but rather a toolbox of many tools.