Detailed description of Connection objects in ADO. NET

Detailed description of Connection objects in ADO. NET

Connection string

Several common parameters used to connect strings:

Provider: this property is used to set or return the name of the connection Provider. It is only used for OleDbConnection objects.

Connection Timeout or Connect Timeout: Wait for the Connection time (in seconds) to Connect to the server before the attempt is aborted and an exception occurs ). The default value is 15 seconds.

Initail Catalog: name of the database.

Data Source: the name of the SQL server used when the connection is opened, or the file name of the Microsoft Access database.

Password: the logon Password of the SQL Server account.

User ID: The Logon account of SQL Server.

Integrated Security or Trusted Connection: this parameter determines whether the Connection is a secure Connection. Possible values include True, False, and SSPI (SSPI is synonymous with True ).

Persist Security Info: when it is set to False, if the connection is opened or has been opened, the Security sensitive information (such as the password) will not be returned as part of the connection. Setting the attribute value to True may pose security risks. False is the default value.

1. Write 1

"Data Source = server name; Initial Catalog = database; User ID = username; Password = Password; Charset = UTF8;"

2. Statement 2

"Server = Server name; Database = Database; uid = user name; Password = Password; Charser = UTF8 ″

PS: Integrated Security = True;

What if I forget to write the connection string? (Although quite shameful)

You can use SqlConnectionStringBuilder to generate a string. If you forget how to write it.

MySqlConnectionStringBuilder can be used to point out the desired attributes.

Connection needs to be released

Connection is the object that implements the IDisposable interface. To use Connection, you need to release resources.

Using (Connection object) is recommended)

{

// Automatic Close (); automatic Dispose ();

}

StateChange event

This event monitors the database connection status. This operation is triggered when the database connection status changes.

We can perform some operations.

The database connection status is an enumeration, ConnectionState.

Currently, ConnectionState has three upper values:

The Closed connection is Closed.

The Connecting connection object is Connecting to the data source.

The Open connection is Open.

Connection Pool

1. Experiment

First, set pooling to false in the connection string;

Then

StopWatch watch = new StopWatch ();

Watch. Start ();

// Perform database operations.

Watch. Stop ();

Output watch. Elapsed;

We are pleasantly surprised to return the result. After setting pooling = false in the connection string, the original performance is reduced by 20 ~ 30 times.

The Connection object provides four Connection methods.

1. OLEDB: System. Data. Oledb. OledbConnection for non-SQL Server and non-Oracle databases

2. SQL: System. Data. SqlCilent. SqlConnection is used to connect to the SQL Server database.

3. ODBC: System. Data. Odbc. OdbcConnection if the database does not have a built-in OLEDB, you can set the ODBC. NET Data provider object.

4. Oracle: System. Data. OracleClient. OracleConnection is used to connect to the Oracle database.

Why?

I will continue to see the next experiment. Delete pooling = false first.

Use loop control to close and open a Connection object. 2000 times.

Then we use SqlServer [Tool] → [SQL Server Profiler ]. Use this tool to observe database transactions and connection records.

We found that only one database connection was found in SQL Server Profiler for 2000 Open ()/Close () times.

Pooling = false is actually connected for 2000 times.

These two experiments aim to bring out a knowledge point, that is, the connection pool.

Now, let's take a look at the principles.

2. Principles

Schematic diagram

Connection object. After the first Connection is established, Close is not directly closed, but saved in the Connection pool.

If the connection string is the same during the next connection, the connection in the connection pool is called. Instead of re-establishing.

This will increase efficiency. This has been verified in Lab 1.

// 1. the Conn object is destroyed, and the connection in the connection pool is not destroyed.

// 2. The connection pool is provided by ADO. NET, not a database. In the local cache.

3. Is the first running slow?

Why are most. NET programs running slowly for the first time?

Instant compilation is an important reason.

The ADO connection pool is empty during database operations!