Introduction to Web. config connection words

Document directory

• 1. Configure in <deleetting>
• 2. Configure in <connectionstrings>

1. Connection word configuration method

There are two methods to configure the connection word in Web. config: <etetting> in configuration, <connectionstrings> in configuration.

1. Configure in <deleetting>

<configuration>   <appSettings>      <add key="connstr1" value="Data Source=.;Initial Catalog=DBName;Integrated Security=true"/>   </appSettings>  </configuration>

① This method is used in ASP. net1.1. This method is also supported in later versions of Asp.net.

② The form of key-value pairs stored in the deleetting is key and value. You can not only store connection strings, but also some configuration items.

③ Providername = "system. data ....... "(if you want to use it, you only need to write it in the value, and pass it as a value)

④ Obtain the database connection word value in the background program code in the following way:

string conn=System.Configuration.ConfigurationManager.AppSettings["connstr";]

 

2. Configure in <connectionstrings>

<configuration>      <connectionStrings>      <add name="connstr3" connectionString="........" />      <add name="connstr4" connectionString="......" providerName="System.Data.Sqlclient"   </connectionStrings></configuration>

① It is added in ASP. net2.0, and later versions also support this method

② The key-value pairs are also stored in the format of name and connectionstring. Generally, the connection string is saved.

③ In connectionstrings, you can use providername.

④ In the background code, the value method is as follows:

string conn=System.Configuration.ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

 

Since connectionstrings comes out of connectionstrings 2.0, it is certainly better than appsettings:

① You can encrypt the connection string by using an encryption tool of Ms.

② Data source controls that can be directly attached without having to write code to read them and then assign them to the controls.
③ You can easily change the database platform. For example, if you change to an Oracle database, you only need to modify the providername.

 

II. Providername

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

Possible values of providername include:

① Providername = "system. data. sqlclient "---- indicates that MSSQLServer database ② providername =" system. data. sqllite "---- indicates that sqllite database ③ providername =" system. data. oracleclient "---- indicates that Oracle database or providername =" system. data. oracle. dataaccess. client "---- same as ④ providername =" system. data. oledb "---- indicates that the ACCESS database is used.

Setting the value of the providername parameter in the connection Word tells CLR to connect to the database using a set of program code specified by the providername value. In fact, it does not matter if you do not write it. CLR will determine the database type for connection. After the connection, what type of database is used, we need to use the corresponding database provider to operate the database, that is, the SQL Server database uses the sqlcommand set, and the Access database uses the oledbcommand set.

III. Connection Pool What is

Sometimes you may see values such as Min pool size and Max pool size in the connection word. This value sets the connection pool size.

Every time the program needs to read and write the database. Connection. open () will connect to the database using connectionstring, the database will establish a connection for the program and stay open, then the program can use T-SQL statements to query/update the database. When connection. Close () is executed, the database closes the connection before the execution. Good. Everything looks so methodical. However, if my program needs to open and close the connection from time to time, such as ASP. net or Web Service). For example, when an HTTP request is sent to the server, we need to open the connection and use select * from table to return a datatable/dataset to the client/browser, then close the current connection. So frequent open/close connection operations will undoubtedly become a waste for the entire system. Ado. Net team provides a better solution. Save the previous connection. The previous connection is handed over to the next connection when you need to open the connection. This is the connection pool.

  Connection Pool How does it work?  First, when a program executes connection. when open () is enabled, ado.net needs to determine whether the connection pool is supported (pooling is set to true by default). If it is set to false, ado.net creates a connection with the database (to avoid confusion, all connections in the database use the "connection" Description) and then returns it to the program.

If it is set to true, ado.net will create a connection pool based on connectstring, and then fill in the connection pool with the connection (all connections in. net programs use the "connection" description ). The number of connections filled is determined by the min pool size (0 by default) attribute. For example, if it is set to 5, ado.net opens five connections with the SQL database at a time, then stores four connections in the connection pool, and one connection is returned to the program.

When the program runs connection. Close. If pooling is true, ado.net places the current connection in the connection pool and maintains the connection with the database.

At the same time, the connection lifetime (default value: 0) attribute will be determined. 0 represents an infinite number. If the connection exists for a longer time than the connection lifetime, ado.net will close the connection and disconnect the database, instead of saving it to the connection pool again.

(This setting is mainly used in the Cluster's SQL database to achieve load balancing ). If pooling is set to false, the connection to the database is directly disconnected.

Then, when connection. open () is executed, ADO. NET will judge whether the new connectionstring is consistent with the connectionstring of the connection stored in the connection pool.

Then, ado.net needs to determine whether there is a usable connection in the current connection pool (not occupied by other programs). If not, ado.net needs to judge the Max pool size (100 by default) set by connectionstring. If all connections in the connection pool do not reach the Max pool size, ado.net will connect to the database again and create a connection, then return the connection to the program.

If maxpoolsize is reached, ado.net will not create any new connection again, but will wait for the connection occupied by other programs in the connection pool to be released. The waiting time is affected by sqlconnection. connectiontimeout (the default value is 15 seconds). That is to say, if the time exceeds 15 seconds, sqlconnection will throw a timeout error (sometimes if sqlconnection is used. the open () method throws a timeout error. One possible cause is that the previous connnection is not closed in time, and the number of connection pools reaches maxpoolsize .)

If there is an available connection, the connection retrieved from the connection pool is not directly returned to the program. ado.net also needs to check the connectionreset attribute of connectionstring (true by default) whether to reset the connection at most. This is because the connection returned from the program may have been modified, for example, sqlconnection. changedatabase method modifies the current connection. The returned connection may not be connected to the initial Catalog Database specified by the current connection string. Therefore, you need to reset the current connection once. However, all additional checks increase the overhead of the ado.net connection pool on the system.

 

References:

1. [ASP. NET] -- connection string in Web. config

2. the timeout time has reached. Error and Max pool size settings