Use of connection object of ADO. net

This article from: http://www.builder.com.cn/2007/0924/521134.shtml
In the ADO. Net object model, the connection object represents the connection with the data source .. Net Framework has two connection objects: oledbconnection, used for most database connections, and sqlconnection, which is a connection specially developed by MS for SQL Server. Before creating a connection object, you must first reference the three namespaces system. Data. oledb, system. Data. sqlclient, and system. Data.

1. SQL Server database connection

You can use the properties of the connection object to specify the location of the data source and other parameters to connect to the database. For example, sqconnection con = new sqlconnection ("provider = sqloledb; Data Source = myserver; initial catalog = database; Use Id = yourid, password = yourpassword ;");

This is a connection to the local database. If you want to connect to the database on the network, you must use integrated security, while ignoring the user name and password. For example:

Sqconnection con = new sqlconnection ("provider = sqloledb; Data Source = myserver; initial catalog = database; Integrated Security = sspi ");

If you are using ODBC to connect to SQL Server, you can use the network database by using trusted_connection = yes.

2. Oracle database connection: (prerequisite: you must first install the appropriate version of the Oracle client utility and create a database alias. Then you can use the following connection string to connect)

Sqconnection con = new sqlconnection ("provider = msdaora; Data Source = dbalias; user id = yourid, password = youpwd ;);

3. Access database connection: (you can use the following connection string to connect)

Sqconnection con = new sqlconnection ("provider = Microsoft. Jet. oledb.4.0; Data Source = D: yourdb. mdb; user id = yourid, password = youpwd ;);

After connecting to the database, you can call the open () method of the connection object to open the connection to the database. Similarly, the close () method is used to close the connection to the database.

Connection Pool:

What is a connection pool? In a three-tier structure (or N-tier), when a client communicates with a middle-tier server, the server creates a connection to the database, and the business object that executes the operation (that is, the instance connected to the database). At the same time, a connection object will be created and put in a pool (actually a thread ). When the instance is released, the instance is closed. The data connection is not closed, but the connection object is marked as closed and stored in the pool. If a new service object is started, the existing connection is checked. If a connection is opened in the pool, use it. Otherwise, create a new connection.

You may find it strange that, in this case, there are not many objects in the pool, and it will not waste a lot of resources, the solution to this problem is that you can set a specific connection time (60 seconds by default) with the database. If this time is not used ,. net will close this connection.

How to open the connection pool? It is enabled by default.

How to close the connection pool? You can use oledbconnection. releaseconnectionpool () method to close the connection pool. Alternatively, you can add ole db services =-4 to the ole db connection string, and add pooling = false to the connector when using the sqlconnection object. When you call close (), the connection to the database is actually closed.

(Note 1: You can use the SQL event probe or performance monitor to check the number of connections to the database to identify whether the connections are actually closed or only in the pool .)

(Note 2: You can explicitly call the dispose () method to release resources before the Garbage Collector recycles them. However, if you only set the connection object to null, will not disconnect from the data source)

Use the connection object to create the command object: (ADO. net uses the command object to execute Data Query and update) Example:

Sqconnection con = new sqlconnection ("provider = sqloledb; Data Source = myserver; initial catalog = database; Integrated Security = sspi ");

Using (oledbcommand cmd = con. createcommadn ())

{

Cmd. commandtext = "select * from table ";

Cmd. executenonquery ();

}

(Note: the advantage of using here is that resources can be released after this operation .)

Use the connection object to create a transaction object: (the transaction object is the transaction management object in ADO. Net)

Example:

Sqconnection con = new sqlconnection ("provider = sqloledb; Data Source = myserver; initial catalog = database; Integrated Security = sspi ");

Con. open ();

Oledbtransaction TRAN = con. begintransaction (); (Note: calling this method will return a new opened transaction object for transaction management during connection)

(Note: A transaction is a group of statements run by a single entity. It can ensure data integrity and prevent data loss caused by system faults or other causes. The concept is very abstract)

Transactions have four attributes: acid (atomicity, consistency, isolation, and persistence ):

Atomicity means that a transaction is executed either successfully or not.

Consistency refers to the data consistency before and after the transaction, that is, if the transaction is successfully executed, the system returns the successful state, that is, all data changes are marked as completed, if the transaction is not completed, roll back and return to the previous valid state.

Isolation means that any change in a transaction is independent of other transactions (compared with two transactions)

Durability means that the transaction is continuous, that is, the change after the transaction is successfully completed is permanent.

(Note: There are two types of transactions: manual and automatic. The topic of this article is not here, and the transaction introduction will be involved in other chapters)

Obtain the database architecture information:

Sometimes you may find that you need to obtain the database architecture information to facilitate program operation. You can use the getoledbschematable () method of the oledbconnection object to obtain the information. It requires a parameter to be used as a filter for the returned schema information, that is, to obtain only the column or row information in the table, if this parameter is not set, information about all columns in the entire table is obtained.

Example:

Oledbconnectioncon = new oledbconnection ("provider = sqloledb; Data Source = myserver; initial catalog = database; Integrated Security = sspi ");

Con. open ();

Datatable dt = con. getoledbschematable (oledbschamaguid, null );

Foreach (datarow row in DT. Rows)

Console. writeline (row ["column_name"]. tostring ());

---------------------------------------------------------------