Introduction to the connection word for Web. config

I. How to configure a connection word

In Web. config, there are two ways to configure the configuration,<connectionstrings> in the connection word:<appsetting>.

Configuration in 1.<appsetting>

<configuration>   <appSettings>      <add key= "connstr1" value= "Data source=.;i Nitial catalog=dbname;integrated security=true "/>   </appSettings>  </configuration>

① This approach is used in asp.net1.1, and this is supported by the subsequent version of ASP.

The ②appsetting is the equivalent of a key-value pair in the form, key and value. Not only can you save the connection string, but you can also store some configuration items.

③ in appsettings, you can not use Providername= "System.Data ..." (but if you want to use it, just write it in value and pass it as a value)

④ gets the value of the database connection word in the background program code by the following way:

String conn=system.configuration.configurationmanager.appsettings["ConnStr";]

Configuration in 2.<connectionstrings>

<configuration>      <connectionStrings>      <add name= "CONNSTR3" connectionstring= "..."/>      <add name= "CONNSTR4" connectionstring= "..." providername= "System.Data.Sqlclient" </   Connectionstrings></configuration>

① It is new in asp.net2.0, and subsequent versions support this way

The ② is also similar to the key-value pair, using name and ConnectionString, which generally saves the connection string.

③ can be used in connectionstrings, you can use ProviderName.

④ in the background code, the way to take the value:

String conn=system.configuration.configurationmanager.connectionstrings["ConnStr"]. ConnectionString;

Since connectionstrings is a version 2.0, it's certainly better than appsettings:

① can encrypt the connection string and use one of MS's encryption tools.

② can directly state the data source control without having to write code to read it and assign it to the control.
③ can easily replace the database platform, such as the Oracle database, just modify the ProviderName

Two. providername It's role

The ProviderName parameter specifies the data provider. A data provider is used to connect to a database, execute commands, and retrieve results.

Possible values for the parameters of ProviderName include:

①providername= "System.Data.SqlClient"  ----instructions for using the MSSQLServer database ②providername= "System.Data.SqlLite"  ---- Description is using the Sqllite database ③providername= "System.Data.OracleClient"  ----instructions for using an Oracle database or providername= " System.Data.Oracle.DataAccess.Client "  ----Ibid ④providername=" System.Data.OleDb "   ----instructions for using an Access database

Setting the value of the ProviderName parameter in the connection word tells the CLR to connect to the database using a set of program code specified by the ProviderName value. It doesn't matter if you don't write it, the CLR will determine its own database type to connect. After the connection, the database is what type, we have to use the corresponding database provider to operate the database, that is, SQL Server database using SqlCommand that one set, Access database using OleDbCommand that set.

Three. Connection Pool is what

Sometimes you might see a value such as min pool Size,max pool size in the connection word, which sets the connection pool.

Whenever the program needs to read and write to the database. Connection.Open () uses ConnectionString to connect to the database, the database establishes a connection to the program and remains open, and the program can then use T-SQL statements to query/update the database. When executed to Connection.close (), the database closes the current connection. Well, everything seems to be so methodical. But if my program needs to open and close the connection in an indefinite time, such as ASP. NET or Web Service, for example, when HTTP request is sent to the server, we need to open connection and then use select* from Table Returns a datatable/dataset to the client/browser and then closes the current connection. That every time open/close Connection so frequent operation for the entire system is undoubtedly a waste. The ADO team gives a better solution. Save the previous connection, and the next time you need to open the connection, the previous connection will be handed over to the other connection. This is connection Pool.

Connection Pool How does that work?  First, when a program executes Connection.Open (), ADO needs to determine whether this connection supports connection Pool (Pooling default is True), and if it is specified as false, ADO creates a connection to the database (in order to avoid confusion, all connections in the database are described using a "connection") and then returned to the program.

If specified as true,ado.net, a connection pool is created based on ConnectString and then populated with connection in the connection Pool (all connections in. NET programs, using " Connection "description). How many connection are populated is determined by the Min Pool Size (default = 0) property. For example, if you specify 5, ADO opens 5 connections to the SQL database at a time, then 4 Connection are saved in Connection pool, and 1 Connection are returned to the program.

When the program executes to Connection.close (). If pooling is true,ado.net, the current connection is placed in the connection pool and the connection to the database is maintained.

The connection Lifetime (default 0) attribute is also judged, and 0 represents infinity if the connection exists longer than connection Lifetime, Connection closes the connection to the database at the same time, instead of re-saving it to the connection pool.

(This setting is primarily used in clustered SQL databases for load balancing purposes.) If pooling is specified as false, the connection to the database is broken directly.

Then the next time Connection.Open () executes, ADO. NET will determine whether the new connectionstring is consistent with the connectionstring of the connection previously saved in the connection pool.

Then ADO needs to determine if there are any available connection (not occupied by other programs) in the current connection Pool, and if not, ADO needs to determine the max pool of connectionstring settings Size (default is 100), if all connection in the connection pool do not reach Max Pool size,ado.net, the database is connected again, a connection is created, and connection is returned to the program.

If you have reached maxpoolsize,ado.net, you will not create any new connections again, but instead wait for the connection in the connection pool to be released by other programs. This wait time is limited by sqlconnection.connectiontimeout (the default is 15 seconds), which means that if the time exceeds 15 seconds, SqlConnection throws a timeout error (so sometimes if the Sqlconnection.open () method throws a timeout error, one possible reason is not to close the previous connnection in time, while connection The pool number reached maxpoolsize. ）

If a connection is available, the connection removed from the connection Pool is not returned directly to the program, and ADO needs to check the Connectionreset properties of the ConnectionString ( The default is True) if a reset is required for connection. This is because the connection that were previously returned from the program may have been modified, such as using Sqlconnection.changedatabase method to modify the current connection. The returned connection may not have been connected to the initial catalog database specified by the current connection string. So you need to reset the current connection. However, due to all the additional checks, the overhead of the system is increased by the ADO Connection Pool.

Reprint Source: http://www.cnblogs.com/bayes/archive/2012/09/05/2672306.html

Introduction to the connection word for Web. config