ASP. NET 3.5 core programming learning notes (11): sqlconnection and Connection Pool

Composition of. NET data providers

. NET data providers have two types of functions: Non-connection data support and connection data support.

The following table lists the main components of the. NET data provider:

 

The component implementation in the above table is based on the methods and attributes defined by a group of interfaces, as shown in the following table:

 

Provider factory Model

Since version 2.0, the architecture of the ADO. net provider has been improved and the factory class has been introduced. Each. NET data provider contains a factory class inherited from the base class dbproviderfactory. The Factory Code specifies the public portal of the provider. The following table lists the main methods of the factory class:

 

What if I get the factory of a certain provider? We can use a newly introduced class dbproviderfactoryies, which has several static methods. Sample Code:

　　DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SqlClient");

The getfactory method accepts a string that represents a constant name of the provider. The name is hardcoded in the configuration file that registers each provider. Getfactory will enumerate all registered providers and return the Assembly and class name information that matches the constant name. This method reflects the factory class and obtains the value of the static attribute instance (that is, obtains the instance of the factory class in single-piece mode ).

The getfactoryclasses method of dataproviderfactories can return the currently installed data provider table through the datatable object. Sample Code:

　　DataTable providers = DbProviderFactories.GetFactoryClasses();

Sqlconnection

This represents a physical connection to SQL Server, which is located in the namespace of system. Data. sqlclient and implements the idbconnection interface.

The attributes of this class are shown in the following table:

Except connectionstring, other attributes are read-only.

The methods for this class are shown in the following table:

Note: connections are not automatically closed even if they are out of scope. The garbage collector cannot identify the special functions of objects and cannot properly process them. Therefore, the connection will not be closed. Therefore, you must explicitly call close or dispose when the object is used.

The sqlconnection class provides a static method called changepassword, allowing developers to change the SQL server password specified by the user in the connection string. Note that this method is only applicable to SQL Server 2005 or later versions.

In ADO. NET 2.0 and later versions, all managed providers implement the getschema method to obtain database metadata information (such as tables, indexes, views, and data types ).

Connection property Configuration

The connectionstring attribute of the connection class can be set only when the connection is closed. Many connection string values have corresponding read-only attributes in the connection class. The attribute names in the connection string are case-insensitive. If the connection string appears for multiple times, use the final settings.

SQL Server connection string keywords are listed in the following table:

If you want to perform large operations involving large objects, increasing the data packet size may be helpful, because this will reduce the number of reads/writes.

Connection string Generator

Using the sqlconnectionstringbuilder class can greatly reduce the risk of injection attacks and improve security.

Storage and acquisition of connection strings

In. NET Framework 2.0 and later versions, the configuration file defines a new section, which is designed to store connection strings. The Section is <connectionstrings>.

All connection strings defined in the web. config file are loaded into the configurationmanager. connectionstrings set.

Protection of connection strings

We can use the system tool aspnet_regiis.exe to encrypt the connection string. For example:

       aspnet_regiis –pe connectionStrings –app /core35

Note: Segment names are case sensitive.

If you want to decrypt it, use-PD instead of the-PE parameter of the command line. Example:

Aspnet_regiis-Pd connectionstrings-APP/core35

Pages Using Protected Area content do not have any problems when running on the local web server that comes with vs, but if the same page is in the standard IIS virtual folder, the RSA provider configuration error may occur. Why?

The "key container" is required to work based on the RSA provider (the default protection provider). The default key container netframeworkconfigurationkey is created during installation. The container not only exists, but also needs to be associated with the account that calls it. To run ASP. NET under the network service account, run the following command to add access permissions to the user:

Aspnet_regiis-PA "netframeworkconfigurationkey" "nt authority \ Network Service"

The full account name must be specified.

Note: The access permission of the key container must be authorized only when the RSA provider is used.

Connection Pool

The connection pool is the foundation of high-performance and scalable applications. Through the connection pool, the cost for applications to open and close database connections is reduced. By default, all standard. NET data providers enable the connection pool function.

Some settings in the connection string directly affect the pool mechanism. The following table lists the connection string parameters related to connection pool Configuration:

Except Connection Reset, all the keywords in the above table apply to the Oracle hosting provider.

To disable the connection pool, set the pooling parameter in the connection string to false.

Each connection pool is associated with a unique connection string and transaction context. When a new connection is enabled, if the connection string settings do not match the existing pool, a new pool is created. Once the connection pool is created, the connection pool will be destroyed after the process ends.

After a connection pool is created, to reach the minimum number of connections in the pool, multiple connection objects are created and added to the pool. Then, add connections as needed until the maximum number of connections in the pool reaches. When a connection object is requested, it is obtained as long as there is an available connection in the pool. Currently, the available connections must be idle, the transaction context must match or be empty, and a valid connection to the server must be available. If no connection is available, the connection pool program creates a new connection object. When the number of connections reaches the upper limit of the pool, the request must wait until an existing connection object is released to the connection pool. The queued requests are processed one by one.

After the close or dispose method is called, the connection is released. If the number of connections in the pool does not reach the upper limit, connections that are not explicitly closed will not be returned to the pool, and the connection is still valid.

If the connection object has expired or a serious error occurs, it will be removed from the pool. In this case, the connection is marked as invalid, and the connection pool program periodically cleans up each pool and permanently removes invalid objects.

Note: To make the connection work more effective, you should explicitly close the connection object and return it to the connection pool as soon as possible after use.

The connection lifetime keyword is used to indicate the validity period of the connection object. If the validity period is exceeded, the connection object should be released. Connection lifetime can be used only when it is in a cluster.

Connection Pool cleanup

If an exception indicates that the connection pool is damaged, ADO. Net automatically identifies and finds the connection pool and marks all connections as obsolete. In this way, the program must create a new connection when applying for a connection. When does an exception indicate that the connection pool is damaged? That is, a fatal exception from the network layer on the previously opened connection.

Sqlconnection and oracleconnection have two new static methods: clearpool and clearallpools, which can be programmed to clear the connection pool when the server is stopped or restarted. Ado. Net also uses these two methods internally to clean up the connection pool.