The Connection classes do just as you would think. They contain the information needed to open a database connection. In addition, they are used to manage transaction objects. The five main members of this class are ConnectionString, Open, Close, and BeginTransaction.
The ConnectionString Property
The ConnectionString property is a concatenation of various elements that tell ADO .NET about the database and server you wish to connect to. A semicolon (;) separates the elements. This is not much different than ADO or ODBC. The connection string definitely traces its heritage back to the ODBC DSN string. Many of the elements are the same. The five main elements of the connection string are:
OLEDB Provider The name of the OLEDB provider. This is only needed for the OleDbClient classes. If provided with the SqlClient classes an error will be thrown.
Server The domain name or Windows machine name of the server. You can also use an Internet Protocol (IP) address. If the name is registered on a Domain Name System (DNS) server you can use that because it resolves to an IP address. Otherwise you can use the Windows machine name if it is in the same workgroup or Windows domain. For SQL Server you can also map an alias to an IP address or named pipe using the client configuration utility. With Oracle you can use the global database name. Oracle clients need to be set up using the SQLNet configuration utility before you can access them using ADO .NET. As this implies, you need an Oracle client installation and license.
Database This is the name of the SQL Server database or the Oracle instance name.
UserName or UID This is the login user name.
Password or PWD The password for the user.
A typical connection string for SQL Server where the server name is MyServer and the database name is MyDB would look like this for the OleDbClient:
For the SqlClient:
For an Oracle database located on server UNIX1 with instance name MYOraDB would look like this for the OleDbClient:
You cannot use the SqlClient classes to connect to an Oracle database.
The Open and Close Methods
These do exactly what they sound like. The Open method opens a connection to the database specified in the connection string. The Close method closes it. You do not have to call the Open and Close methods explicitly. If you call a DataAdapter's Fill method, it will automatically open the connection, execute the SQL or stored procedure, and close the connection. If you open the connection before calling the Fill method, the DataAdapter will not close the connection when it is finished.
The BeginTransaction Method
ADO .NET handles transactions differently than all previous Microsoft data access technologies. In the past, transactions were not named and they were started on the connection object. You could nest transactions if the underlying database allowed it but the nesting was in the order the transactions where started. This made tracking the transaction scope very difficult, especially if the transactions were started in various procedures throughout the program. The programmer had to make sure the right transactions were in the correct scope. Now, when you begin a transaction, it gets assigned to a transaction object which represents a nesting level. You assign a transaction object to a database operation, such as a command object, and the object will operate at that level. The Commit and Rollback methods are now in the transaction class.