Ado. NET in the Connection object detailed

Connection string

Several common parameters for connection strings:

Provider: This property is used to set or return the name of the connection provider, only for the OleDbConnection object.

Connection Timeout or connect Timeout: The length of the connection time (in seconds) to wait to connect to the server before aborting the attempt and generating an exception. The default is 15 seconds.

Initail Catalog: Name of the database.

Data Source: The name of the SQL Server to use when the connection is opened, or the file name of the Microsoft Access database.

The login password for the PASSWORD:SQL server account.

User id:sql Server login account.

Integrated security or Trusted Connection: This parameter determines whether it is a secure connection. The possible values are true, FALSE, and SSPI (SSPI is a synonym for true).

Persist Security Info: When set to false, if the connection is open or has been turned on, safe-sensitive information, such as a password, is not returned as part of the connection. Setting a property value of true can be a security risk. False is the default value.

1. Writing a

"Data source= server name; Initial catalog= database; User ID = Username; password= password; Charset=utf8; “

2. Writing Two

"Server= server name; database= database; uid= user name; password= password; Charser=utf8″

ps:integrated security = True;

What if you forget the connection string? (though it's humiliating)

You can use SqlConnectionStringBuilder to generate strings, if you forget how to write them.

Mysqlconnectionstringbuilder can be used to point out the attributes you want.

Connection needs to be released.

Connection is an object that implements the IDisposable interface. Use connection to release resources.

Suggested using using (Connection object)

{

Auto Close (); automatic Dispose ();

}

StateChange Events

This event listens for database connection status. This action is triggered when the database connection state changes.

We can do some action.

The database connection state is an enumeration, ConnectionState.

So far, ConnectionState has a total of three values, respectively.

The Closed connection is in a closed state.

The connecting Connection object is connecting to the data source.

The open connection is turned on.

Connection pool

1. The experiment

First, set pooling = False in the connection string;

And then through

Stopwatch watch =new stopwatch ();

Watch. Start ();

Perform database operations.

Watch. Stop ();

Output watch. Elapsed;

We were pleasantly surprised to return, after setting pooling = False in the connection string, the original performance decreased by 20~30 times.

The Connection object provides 4 ways to connect

1, OLEDB:System.Data.Oledb.OledbConnection for non-SQL Server and non-Oracle database use

2. SQL:System.Data.SqlCilent.SqlConnection for connecting to SQL Server database

3, ODBC:System.Data.Odbc.OdbcConnection If the database does not have the built-in OLE DB, you can make the Odbc.net data provider object.

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

Why, then?

I went on to see the next experiment. Remove pooling = False first.

Loop-Control the Connection object's closing and opening. Do it 2000 times.

Then we use SQL Server "tools" → "SQL Servers Profiler". Use this tool to observe database transactions, connection records.

We found 2000 times Open ()/close (), and only one database connection was seen in SQL Server Profiler.

And the Pooling=false is actually connected 2000 times.

The purpose of these two experiments is to elicit a knowledge point, namely the connection pool.

So, the phenomenon has, we knot to look at the principle.

2. Principle

Schematic diagram

Connection object, when the first connection is established, close is not closed directly, but is saved in the connection pool.

At the next connection, if the connection string is the same, the connection in the connection pool is invoked. Rather than re-establish it.

This will improve the efficiency of certain. This has been verified in experiment one.

1. The Conn object was destroyed, and the connection in the connection pool was not destroyed.

2. The connection pool is provided by the ado.net, not the database. In the local cache.

3. First time running slowly?

. NET program why most of the first time running slower?

Just-in-time compilation is certainly a very important reason.

When the database operation, the ADO connection pool is empty, is also a reason!