The Connection object is the way ADO.NET's built-in data providers connect to a data source. There are two Connection objects that ship with the ADO.NET: OleDbConnection and SqlConnection . As you might have guessed, the OleDbConnection object is used to create a connection to any valid OLE DB data source and the SqlConnection object is used to connect to Microsoft SQL Server versions 7.0 and up.
Just as in ADO, ADO.NET uses connection strings to connect to various data sources. The next section discusses connection string syntax and gives you several examples enabling you to connect to several types of databases.
A connection string is a semicolon-delimited set of name -value pairs that define the various properties of a database connection. A connection string can have several properties or as little as one property, depending on the requirements of the data source.
The connection properties are specified through a string rather than through a set of individual connection object properties so that the properties can remain highly configurable. Each OLE DB data provider may require setting special connection string properties. It would be difficult to encompass all these properties by specifically enumerating them as a set of built-in properties of the Connection object. Therefore, the connection string is used because additional name-value pairs can easily be added.
Connection strings specify a wide variety of information, such as the type of OLE DB provider you are using for your connection to the database (if you are using the OleDbConnection object), database user information, and security information. Here's a sample connection string enabling you to connect to a Microsoft Access database using the standard admin password and the Microsoft Jet drivers:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePath;Jet OLEDB: User Id=admin;Password=;"
As previously stated, when using the OleDbConnection object and the OleDb namespace, you are actually connecting to an OLE DB data provider. Therefore, with a few minor exceptions, connection strings in ADO.NET are the same as the ones used in ADO. When creating an OLE DB source connection string in ADO.NET, you must use the Provider keyword. The Provider keyword specifies the OLE DB provider you are using to connect to your data.
When you're specifying user information for OLE DB providers, the username is normally specified by the User Id and the password by the Password key. However, you can also specify your user name with UID and password with PWD.
The Connection object has several methods and properties. Most of the properties of the Connection object are read-only, set when the connection string is specified. These include properties such as Database , which contains the name of the database you're working with, and Provider , which contains the name of the OLE DB provider you're using to connect to your data. You already know the ConnectionString property of the Connection object. The following sections discuss the commonly used methods.
The Open() method of the Connection object opens up a connection to your data source. Because database connections are a very expensive resource memory-wise, you should only call the Open() method just before you're ready to retrieve the data. This ensures that the connection is not open any longer than it needs to be.
Immediately after you are done retrieving data, you should call the Close() method of the Connection object. This closes the connection to the database. However, opening and closing a connection to the database is time-consuming . When performing a number of calls to the database in quick succession, it's normally best to leave the connection to the database open.
Make sure to always call the Close() method. When the Connection object is recycled by the framework garbage collector, connections to the database are not automatically closed. You can use the State property of the Connection object to test whether the Connection object is open or closed.