To give full play to the advantage of ADO.NET, not only need to be comprehensive, in-depth understanding of the ADO.NET programming model, and timely summing up experience, and the skill is also very important. ADO has been experienced for many years, and ADO.NET provides a richer, powerful tool; The programming work is simplified to the extent that only by the mouse click.
The ADO.NET contains a lot of objects that represent various logical entities in the data access model, which is especially connected, and both objects are most important. The connection of the connection is to create a channel that communicates with the backend database, and create a connection object must be based on a specific .NET data provider. Transaction objects can be created on existing connection objects or created by explicitly executing a Begin TRAN SQL statement. Although the theory is simple, it is actually a lot of uncertain factors around the connection, and they have a critical impact on the overall stability and efficiency of the application.
How to save the connection string to protect sensitive information (such as passwords) that may contain in the connection string? How to design a perfect data access strategy, considering both security (ie authentication, authorization), but does not have much impact on performance and scalability? How can you implement and control your transaction if you need to use a transaction? Is automated transactions or manual transactions? These problems must be carefully considered when using ADO.NET.
First, join the string, connect pool
Database connections are an important, limited, expensive resource, so use a good connection object is the most basic requirement for any application. The main points of using database connections can be summarized as follows:
Save the connection string should pay attention to safety. Turn on the connection should be late and close the connection should be early. The connection string is the key to access the database. In addition to the data to be accessed, the connection string also contains why the user can access the identity certificate of those data. When performing a database operation, the user identification is the most important factor in determining data access.
1.1 Save the connection string
Currently, hard-coded connection strings have the best performance because they directly compile the application's code. However, hard-coded string affects the flexibility of the program, once the connection string changes, the application must recompile.
Save the connection string to the outside to improve flexibility, and the cost is an additional overhead of accessing external strings. However, in most cases, the resulting performance overhead can be ignored, and it is really worrying that security issues. For example, an attacker may modify and steal the connection string. Supreme channels for saving the connection string to an external environment include: profiles, UDL files, Windows registry.
The .NET frame configuration file is deployed in the form of a plain text file, which is convenient to access. If the connection string contains a password, the text format will be the biggest defect, because the password will be saved in a clear form. It can be considered to introduce a dedicated encryption / decryption engine, but this part of the work needs to be completed by developers.
The UDL file is a text file for the OLE DB provider, that is, the SQL Server managed provider does not support UDL files. The UDL file also exists as the same security problem as the previous configuration file, and the general seems to have not much advantage.
Finally, the Windows registry can be used as a naturally safe storage site. The registry is a system knowledge base that saves critical information. If combined with encryption technology, you can achieve high security. The main disadvantage to use the registry is to deploy trouble, requiring a registration key (possibly to perform encryption) and read data from the registry. Although .NET Framework provides a set of package classes that call the underlying Win32 API, these classes do not provide encryption. The ASPNET_SETREG.EXE tool can be used to create the 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 retrieve existing connection objects through a buffer pool, avoiding new objects every time you use the connection object. After connecting the pool, you can meet the needs of a large number of clients as long as a small amount of connection objects.
Each connection pool is associated with a separate connection string and its transaction context. Each time a new connection is opened, the data provider will try to match the specified connection string with the string of the connection pool. 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 removed unless the process ends. Some people think that this processing will affect performance, in fact, maintaining an inactive or empty connection pool does not need much overhead.
After connecting the pool, you will create some connection objects and add them to the connection pool until the number of rated minimum connection objects is reached. In the future, the system will reach the maximum number of connection objects until you need to build and join the connection object as needed. If the program requests a connection object, there is no idle connection object available, and the number of objects in the connection pool has reached the upper limit, then the request is placed in the queue, and once the connection is released back to the pool, it is immediately taken out.
Avoid construct a connection string in a programming manner. If you construct a connection string by combining multiple input data, it is easy to attack the injective attack. If you must use the data entered by the user, be sure to perform rigorous verification.
1.3 Close connection
When a connection is turned off, the connection object is returned to the connection pool for reuse, but the actual database connection is not removed. If the connection pool is disabled, the actual database connection is also closed. At a point where the connection object must be emphasized, the connection object should be explicitly closed and return it to the connection pool, and do not rely on the garbage collector to release the connection. In fact, when the reference to the connection object exceeds a valid range, the connection does not have to be closed - the function of the garbage collector is to remove the .NET package object that represents a physical connection, 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. Connection objects are only removed from the connection pool when the survival ends or a serious error occurs.
1.4 connection pool and safety
If all of the data access operations of an application use the same connection string, the advantage of the connection pool will be loaded. However, this is just an ideal condition, which is likely to have conflicts with other requirements of the application. For example, if only one connection string is used, it is very difficult to perform security control at this level of the database.
On the other hand, if each user uses its own connection string (that is, set a database account for each user, there is a large number of small connection pools, and many connects are not reused. In accordance with conventions, the best solution for such issues is to find an appropriate compromise between the two extremes. We can set a group of representative public accounts while modifying the stored procedure, so that it accepts a parameter representing the user ID, the stored procedure performs different operations in accordance with the incoming user ID.
Second, the transaction mode
Distributed enterprise applications inventory. There are two main ways to join the transaction management feature in the data access code: manual mode, automatic mode. In manual mode, the programmer is responsible for writing all configurations, using the transaction mechanism. Automatic (or COM ) transactions are added to the .NET class, specify the transaction characteristics of the runtime object. The automatic mode is convenient for multiple components to work within the same transaction. Both transactions support local or distributed transactions, but automated transactions greatly simplify distributed transaction processing.
It must be noted that the transaction is a big overhead, so it is necessary to use the transaction to be considered again. If you do need to use a transaction, you should try to reduce the particle size of the transaction, reduce the lock time of the database, lock the range. For example, for SQL Server, a single SQL statement does not need to explicitly declare transactions, and SQL Server automatically runs each statement as a separate transaction. Manual local transactions are always much faster than other matters because it does not need to involve DTC (Distributed Transaction Coordinator).
Manual business, automatic transactions should be considered two different, mutually exclusive technologies. If you want to perform a transactional operation on a single database, you can give priority to manual transactions. For multiple resource managers (eg, a database, and an MSMQ resource manager), a single transaction involves multiple resource managers (eg, a database and an MSMQ Explorer). In any case, it should be extremely avoided to avoid the two transaction modes. If performance is not particularly important, even if only one database operation can also consider using automatic transactions, making the code more simple (but the speed is slightly slow).
All in all, to improve the quality of the database access code, you must understand the ADO.NET object model, and use a variety of techniques based on the actual situation. ADO.NET is a public API, a variety of applications - whether it is a Windows Form Application, an ASP page or a web service, you can access the database via ADO.NET; however, ADO.NET is not an accepting input and spitting Black box, but a toolbox consisting of many tools.
