Ado. NET practical Skills Two

In order to give full play to the advantages of ado.net, we need not only a comprehensive and in-depth understanding of ado.net programming model, but also experience and skills in time. ADO has many years of practical experience, ADO. NET on this basis, provides a richer and more powerful tools; however, ADO. NET design goal is not to provide a Plug and Play tool, it does not simplify all programming work to only click on the mouse to complete the extent.

Ado. NET contains a large number of objects representing the various logical entities in the data access model, in which the two objects, especially the connection, transaction, are most important. The role of a connection is to establish a channel to communicate with the backend database, and the connection object must be created with a specific. NET Data provider. A transaction object can be created on an existing connection object, or by explicitly executing a BEGIN TRAN SQL statement. Although the theory is simple, in fact, there are many uncertainties surrounding connectivity and transactions, and they have a crucial impact on the stability and efficiency of the application as a whole.

How do I save a connection string to protect sensitive information (such as a password) that may be contained in a connection string? How do you design a comprehensive data access strategy that takes into account security (authentication, authorization) without too much impact on performance and scalability? If you need to use a transaction, So how to implement and control transactions efficiently? Do you use automatic or manual transactions? These issues must be carefully considered when using ado.net.

　　A, connection string, connection pool

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

Saving the connection string should be safe.

It should be late to open the connection and the connection should be closed earlier.

The connection string is the key to accessing the database. In addition to the data to be accessed, the connection string contains the identification of why users can access those data. User identification is the most important factor in determining data access permissions when performing database operations.

1.1 Save Connection string

Currently, hard-coded connection strings have the best performance because they are compiled directly into the application code. 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 to the outside increases flexibility, at the cost of additional overhead in accessing the external strings. In most cases, however, the resulting performance overhead can be negligible, and the real concern is security issues. 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 in plain text files and are easy to 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, that is, the SQL Server managed provider does not support UDL files. UDL files also have the same security issues as the previous configuration files, which generally seem to have little advantage.

Finally, the Windows registry can be a natural and safe place to store. The registry is a system repository of key information, and if combined with encryption technology, it can achieve higher security. The main disadvantage of using the registry is deployment trouble, requiring the creation of a registry key (which may also require encryption) and reading data from the registry. Although the. NET Framework provides a set of encapsulated classes that invoke the underlying Win32 APIs, none of these classes provide cryptographic functionality. The Aspnet_setreg.exe tool can be used to create registry keys under HKEY_LOCAL_MACHINE to save user names and passwords, for example: Aspnet_setreg.exe-k "Softwaremydata"-u:userid-p: Password This command encrypts the specified user ID and password.

1.2 Connection Pool principle

Connection pooling allows us to reuse existing connection objects through a buffer pool, avoiding the need to create a new object each time the connection object is used. With a connection pool, a small number of connection objects can meet the needs of many 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 into the connection pool. After the connection pool is created, it will not be removed unless the process is finished. Some people think that this approach will affect performance, but in fact, it does not cost much to maintain an inactive or empty connection pool.

After connection pooling is created, some connection objects are created and added to the connection pool until the minimum number of connected objects is reached. Later, the system creates and joins the connection objects as needed until the maximum number of connection objects is reached. If the program requests a connection object with no idle connection objects available, and the number of objects in the connection pool has reached an upper limit, the request is queued and immediately removed when a connection is released back to the buffer pool.

Avoid constructing connection strings programmatically. If you construct a connection string by merging multiple input data, it is easy to exploit the injection attack. If you have to use 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 used, it should be explicitly closed and returned to the connection pool, and do not rely on the garbage collector to release the connection. In fact, when a reference to a connection object exceeds a valid range, the connection is not necessarily closed--the garbage collector's function is to remove the physical connection. NET encapsulates the object, but that does not mean that the underlying connection is also closed.

Call the close or Dispose method to free 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 Pool and security

If all data access operations for an application use the same connection string, the advantage of the connection pool will be limited. However, this is only an idealized condition 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, each user set the database account separately), there will be a large number of small connection pool, many connections are not reused at all. By convention, the best solution to this type of problem is to look for 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's identity, and the stored procedure performs different operations based on the incoming user identity.

　　Second, the business model

The application of distributed enterprise is inseparable from transaction. There are two main ways to add transaction management functionality to your data access code: manual, Automatic.

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

It is important to note that a transaction is a very expensive operation, so it is important to think twice before you decide to use the transaction. If you really need to use transactions, it is necessary to minimize the granularity of transactions, reduce the database lock time, 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. Manual local transactions are always much faster than other transactions because they do not need to involve 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, consider manual transactions first. 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), an automatic transaction is considered a priority. In any case, the mixed use of the two transaction modes should be avoided vigorously. If performance is not particularly important, you can consider using automatic transactions even for one database operation, making your code simpler (but slightly slower).

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