Explanation of Connection in ADO. NET, ado. netconnection

Explanation of Connection in ADO. NET, ado. netconnection
Connection string

1.Method 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)

AvailableSqlConnectionStringBuilderGenerate 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.Lab

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.

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 is inLab 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!