You must know ADO. Net (4) taste the connection object

ArticleDirectory

• Summary
• Directory

Summary

In the previous articles, I have not explained the core objects of data provider in detail, because I hope to give you a good understanding of some basic concepts before explaining these objects. In the previous article, you must know ADO. net (3) connection string. Have you underestimated it? I have explained the connection string in detail. I believe everyone is aware of its importance just like me. If the connection string is the key to open the data source door, what I want to explain today is how to use this key to open the data source door. As the first core object of data provider, connection objects shoulder the important task of connecting data sources. Let's get to know this heavyweight!

Directory

• Understanding the connection object
• Several methods that must be mastered
• Several attributes that must be mastered
• Connectionstate
• Instance: The sqlconnection object connecting to SQL Server
• Write elegant and safeCode

1. Understand the connection object

Connection object, as its name implies,Indicates the connection to a specific data source.. If the data source is compared to the door, the connection string is the key, and the connection object is the person who opens the door with the key. For ADO. net, different data sources correspond to different connection objects. The specific connection objects are as follows:

Name	Namespace	Description
Sqlconnection	System. Data. sqlclient	Indicates the connection object with SQL Server.
Oledbconnection	System. Data. oledb	Indicates the connection object to the oledb data source.
Odbcconnection	System. Data. ODBC	Indicates the connection object with the ODBC data source.
Oracleconnection	System. Data. oracleclient	Indicates the connection object with the Orale database.

Regardless of the connection object, it inherits from the dbconnection class. Let's take a look at the implementation structure of the dbconnection class:

 
Public Abstract ClassDbconnection: component,
Idbconnection, idisposable
 

Copy code

From the above, we can see that dbconnection is an abstract base class and inherits the compoent, idbconnection, and idisposable classes. Because the dbconnection class is an abstract base class, it cannot be instantiated. The dbconnection class encapsulates many important methods and attributes. The following describes several important methods and attributes in detail.

 

2. Several Methods that must be mastered

Open:UseConnectionstringThe specified settings enable the database connection.

dispose: component . "data-guid =" 91abfae124cc1fbaddc634f3166c968e "> release all resources used by component .

Close:Close the connection to the database. This method is the preferred method to close any opened connection.CloseMethod to roll back any pending transactions. Then, it releases the connection to the connection pool, or closes the connection when the connection pool is disabled.

 

3. Several attributes that must be mastered

Database:Obtain the name of the current database after the connection is opened, or obtain the database name specified in the connection string before the connection is opened.

Datasource:Obtain the name of the database server to be connected.

Connectiontimeout:Obtain the waiting time before the connection is terminated and an error is generated.

Connectionstring:Gets or sets the string used to open the connection.

State:Gets the string that describes the connection status.

 

4. connectionstate

As we know above,The state attribute describes the current status of the connection to the data source.Connectionstate is an enumeration type. It includes the following members:

Closed:The connection is closed.

Open:The connection is open.

Connecting:The connection object is connecting to the data source.

executing: the connection object is executing the command.

fetching: the connection object is retrieving data.

Broken:The connection to the data source is interrupted.

 

5. instance: The sqlconnection object connecting to SQL Server

So many theoretical knowledge is mentioned above. Let's talk about an instance connecting to SQL Server! The Code is as follows:

 1  Using System;
 2   Using System. Collections. Generic;
 3   Using System. LINQ;
 4   Using System. text;
 5   Using System. Data;
 6   Using System. Data. sqlclient;
 7  
8   Namespace Connection
 9 {
 10       Class Program
 11 {
 12           Static   Void Main ( String [] ARGs)
 13 {
 14               // Construct a connection string  
  15 Sqlconnectionstringbuilder connstr = New Sqlconnectionstringbuilder ();
 16 Connstr. datasource = @"  . \ Sqlexpress  " ;
 17 Connstr. initialcatalog = "  Master  " ;
18 Connstr. integratedsecurity = True ;
 19  
 20 Sqlconnection conn = New Sqlconnection (); //  Create a connection object  
  21 Conn. connectionstring = connstr. connectionstring; //  Set the connection string  
  22  
23 Conn. open (); //  Open connection  
  24  
 25               If (Conn. State = connectionstate. open)
 26 {
 27 Console. writeline ( "  Database is linked.  " );
 28 Console. writeline ( "  \ Ndatasource: {0}  " , Conn. datasource );
 29 Console. writeline ( "  Database: {0}  " , Conn. database );
 30 Console. writeline ( "  Connectiontimeout: {0}  " , Conn. connectiontimeout );
 31 }
 32  
 33 Conn. Close (); //  Close connection  
  34 Conn. Dispose (); //  Release resources  
  35  
 36               If (Conn. State = connectionstate. Closed)
 37 {
38 Console. writeline ( "  \ Ndatabase is closed.  " );
 39 }
 40  
 41 Console. Read ();
 42 }
 43 }
 44 }

Copy code

Result:

 

6. Write elegant and safe code (1) Add try... Catch Block

We know that exceptions may occur when connecting to the database, so we need to add exception handling. For C #, a typical exception handling method is to add a try... catch code block. Finially is optional. Finially refers to the code block that is executed no matter whether the code is abnormal or not. WhileIt is very valuable for database connection resources.. Therefore, we should ensure that after the connection is enabled, the connection should be closed and resources should be released no matter whether exceptions occur. Therefore, we must call the close method in the finially statement block to close the database connection.

 

1Sqlconnection conn =NewSqlconnection (connstr );
2 Try
3{
4Conn. open ();
5}
6 Catch(Exception ex)
7{
8;//Todo
9}
10Finially
11{
12Conn. Close ();
13}
 

Copy code

 

(2) Use the using statement

Another elegant method is to use the using statement. If you are not familiar with the using syntax, I will try again.The role of using statements is to ensure that resources are used and soon released.The Using statement helps reduce potential problems caused by unexpected running errors. It neatly encapsulates the use of resources. Specifically, it executes the following:

• Allocate resources.
• Put statement into the try block.
• Create the dispose method of the resource and put it into the Finally block.

Therefore, the preceding statement is equivalent:

 
1 Using(Sqlconnection conn =NewSqlconnection (connstr ))
2{
3;//Todo
4}
 

Copy code

 

from: http://www.cnblogs.com/liuhaorain/archive/2012/02/15/2349886.html