Introduction to practical examples of ADO.NET

In order to give full play to the advantages of ADO.NET, it is not only necessary to have a comprehensive and in-depth understanding of the ADO.NET programming model, but it is also very important to summarize experience and skills in a timely manner. ADO has many years of practical experience, and ADO.NET provides richer and more powerful tools based on this. However, the design goal of ADO.NET is not to provide a plug-and-play tool after all, and it will not integrate all The programming work is simplified to the extent that it can be completed with just mouse clicks.
ADO.NET contains a large number of objects representing various logical entities in the data access model, among which the two objects of connection and transaction are the most important. The function of a connection is to establish a communication channel with the back-end database. Creating a connection object must be based on a specific .NET data provider. Transaction objects 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 uncertain factors surrounding connections and transactions, and they have a crucial impact on the overall stability and efficiency of the application.

　How to save the connection string and protect sensitive information (such as passwords) that may be contained in the connection string? How to design a complete data access policy that takes into account security (i.e. authentication, authorization) without having too much impact on performance and scalability? If transactions are needed, how to implement and control transactions efficiently? Use automatic transactions or manual transactions? When using ADO.NET, these issues must be carefully considered.

1. Connection string, connection pool

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

Pay attention to safety when saving connection strings.
The connection should be opened late and the connection should be closed early.
The connection string is the key to access the database. In addition to describing the data to be accessed, the connection string also contains proof of identity of why the user can access that data. User authentication is the most important factor in determining data access rights when performing database operations.

　1.1 Saving connection strings

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 the application must be recompiled once the connection string changes.

Saving the connection string externally increases flexibility, at the expense of additional overhead for accessing the external string. But in the vast majority of cases, the resulting performance overhead is negligible, and what you really need to worry about is security. For example, an attacker could modify or steal the connection string. Common ways to save the connection string to the external environment are: Configuration file, UDL file, Windows registry.

.NET framework configuration files are deployed in the form of plain text files and are easy to access. If the connection string contains a password, the text format will be the biggest drawback, because the password will be saved in clear text. You can consider introducing a dedicated encryption/decryption engine, but this part of the work needs to be done by the developers themselves.

UDL files are text files used by OLE DB providers, that is, SQL Server hosting providers do not support UDL files. UDL files also have the same security issues as the previous configuration files, and overall there are not many advantages.

Finally, the Windows registry can be used as a naturally secure storage place. The registry is a system knowledge base that stores key information. If combined with encryption technology, higher security can be achieved. The main disadvantages of using a registry are the hassle of deployment, the requirement to create registry keys (and possibly perform encryption) and to read data from the registry. Although the .NET Framework provides a set of encapsulated classes that call the underlying Win32 API, none of these classes provide encryption functions. 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 will encrypt 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, avoiding the need to create a new object every time a connection object is used. After using the connection pool, only 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 attempts to match the specified connection string with the connection pool's string. If the match 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 dismantled unless the process ends. Some people think that this processing method will affect performance. In fact, it does not cost much to maintain an inactive or empty connection pool.

After the connection pool is created, the system will create some connection objects and add them to the connection pool until the rated minimum number of connection objects is reached. In the future, the system will create and add connection objects as needed until the maximum number of connection objects is reached. If there is no free connection object 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 placed in the queue, and once a connection is released back to the buffer pool, it is immediately taken out for use.

Avoid constructing connection strings programmatically. If the connection string is constructed by merging multiple input data, it is easy for injection attacks to take advantage of it. If user-entered data must be used, strict validation must be performed.

　1.3 Closing the connection

When closing a connection, the connection object is returned to the connection pool for reuse, but the actual database connection is not dismantled at this time. If connection pooling is disabled, the actual database connection is also closed. One point that must be emphasized here is that the connection object should be explicitly closed and returned to the connection pool after use. Do not rely on the garbage collector to release the connection. In fact, when the reference of the connection object goes out of the valid range, the connection is not necessarily closed - the function of the garbage collector is to tear down the .NET encapsulated object representing the physical connection, but this does not mean that the underlying The connection is also closed.

Calling the Close or Dispose method can 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 Pooling and Security

If all data access operations of an application use the same connection string, the advantages of the connection pool will be maximized. However, this is an idealized situation and may conflict with other requirements of the application. For example, it would be difficult to enforce security controls at the database level if only a single connection string was used.

On the other hand, if each user is allowed to use his own connection string (that is, a separate database account is set for each user), there will inevitably be a large number of small connection pools, and many connections will not be reused at all. . Conventionally, the best solution to this type of problem 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 representing the user ID. The stored procedure performs different operations based on the incoming user ID.

2. Transaction Mode

Distributed enterprise applications are inseparable from transactions. There are two main ways to add transaction management functions to data access code: manual and automatic.

In the manual method, the programmer is responsible for writing all code that configures and uses the transaction mechanism. Automatic (or COM+) transactions add declarative attributes to .NET classes to specify the transactional characteristics of runtime objects. The automated approach facilitates configuring multiple components to run within 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 transactions are a very expensive operation, so you must think twice before deciding to use transactions. If you really need to use transactions, you should try to reduce the granularity of the transactions and reduce the locking time and locking scope of the database. For example, for SQL Server, a single SQL statement does not need to explicitly declare a transaction, SQL Server will automatically run each statement as an independent transaction. Manual local transactions are always much faster than other transactions because it does 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, give priority to manual transactions. 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 given priority. Regardless, mixing the two transaction modes should be avoided as much as possible. If performance is not particularly important, consider using automatic transactions even for only one database operation, making the code cleaner (but slightly slower).

In short, to improve the quality of database access code, you must have an in-depth understanding of the ADO.NET object model and flexibly use various techniques according to the actual situation. ADO.NET is a public API. Various applications, whether it is Windows Forms applications, ASP pages or Web services, can access the database through ADO.NET; however, ADO.NET does not accept input and spit out results at the same time. A black box, but a toolbox made up of many tools.

The above is the detailed content of Introduction to practical examples of ADO.NET. For more information, please follow other related articles on the PHP Chinese website!