In the ADO.NET object model, the Connection object represents a connection between the data source. There are two Connection objects in the .NET frame: one is OLEDBConnection, for most database connections, one is SQLConnection, which is MS development specifically for SQL Server connections. Before creating a Connection object, you must first reference system.data.oledb or SYSTEM.DATA.SQLCLIENT and SYSTEM.DATA.
1. Connection of SQL Server Database
You can use the properties of the Connection object to specify the location and other parameters of the data source to connect to the database. Such as: sqconnection con = New SqlConnection ("provider = sqloledb; data source = myserver; initial catalog = database; userpassword;");
This is connected to the local database. If you want to connect to a database, you will use integrated security while ignoring the username and password. Such as:
SQCONNECTION Con = New SqlConnection ("provider = sqloledb; data source = myserver; 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 the database alias, then you can connect with the following connection string)
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 invoke the open () method of the Connection object to open the connection to the database, which is used to close the connection with the database.
connection pool:
What is the connection pool? In a three-layer structure (or N layer), when a client communicates with the intermediate layer server, the server creates a database connection, and executes the operational object (which is to connect to the database), at the same time A Connection object is created, in a pool (actually a thread). When this instance is released, this example is turned off, and there is no real shutdown data connection, but the Connection object is labeled in the pool. If you are starting a new business object at this time, you will check the existing connection. If you have an open connection in the pool, you can create a new connection again if you use it.
Maybe you will feel very strange, if this, there is not a lot of objects in that pool, isn't it a lot of resources, this problem is to set the specific connection time (default 60 seconds) that you can set with the database, if Not used during this time, .NET provides closes this connection. How to open the connection pool? By default, it is open.
How to turn off the pool? You can use the OLEDBConnection.releaseConnectionPool () method to close the connection pool, or you can add OLE DB Services = - 4 in the OLE DB connection string; plus Pooling = false in the connector when using the SqlConnection object. At this time, you will really turn off the connection with the database when you call Close ().
(Note 1: You can use the SQL event probe or performance monitor to observe the number of connections to the database to identify if the connection is really turned off or is just put in the pool.)
(Note 2: You can explicitly call the Dispose () method to release the resource before the garbage collector is recycled, but if you just set the Connection object to null, it will not disconnect the connection with the data source.
Use the Connection object to create a Command object: (using a Command object in ADO.NET to perform data query, update) Case:
SQCONNECTION Con = New SqlConnection ("provider = sqloledb; data source = myserver; INTEGRATED Security = SSPI");
USING (OLEDBCommand cmd = con.createcommadn ())
{
cmd.commandtext = "Select * from table";
cmd.executenonquery ();
}
(Note: The benefit of using using here is to release resources after performing this operation.)
Use the Connection object to create a Transaction object: (Transaction object is the transaction management object in ADO.NET)
example:
SQCONNECTION Con = New SqlConnection ("provider = sqloledb; data source = myserver; INTEGRATED Security = SSPI");
C.Open ();
OLEDBTRANSACTION TRAN = Con.BegintransAction (); (Note: Calling this method will return a new open Transaction object when connecting to transaction management)
(Note: The transaction refers to the statement of a single entity that ensures the integrity of the data to prevent data loss caused by system failure or other reasons. The concept is very abstract, huh, huh, look down on it)
The transaction has four attributes of ACID (ie atomic, consistency, isolation, persistence):
Atomicity means that this process is either executed during the execution of the transaction, or not.
Consistency refers to the consistency of the data before the transaction and the transaction, that is, if the transaction is successfully executed, the system returns a successful state, that is, all data changes marked as completed, if not completed, the transaction, the return, and return To the previous legal state. Isolation refers to any changes in a transaction independently of other transactions (relative to two affairs)
The persistence of persistence is continuous, that is, the change after successful transaction is permanent.
(Note: There are two manual and automatic transactions. The theme of this article is not here, the introduction of the transaction is involved in other chapters)
The architecture information acquired by the database:
Sometimes you will find that you need to get the architecture information of the database to facilitate the operation of the program. You can get the getoledbschematable () method of the OLEDBConnection object, which requires a filter that is used to return schedule information, that is, get only columns or row information in the table, and do not write this parameter to get all lists of the entire table. .
example:
OLEDBCONNECON = New OLEDBCONNECTION ("provider = sqloledb; data source = myserver; initial catalog = data; integrated security = sspi");
C.Open ();
DataTable dt = con.getoledbschematable (OLEDBSChamaguid, NULL);
Foreach (Datarow Row In Dt.Rows)
Console.writeline (Row ["Column_Name"]. TOSTRING ());
-------------------------------------------------- -------------
There are also many useful methods regarding the connection objects of Ad.Net, specifically check the MSDN.
