- Introduction to ADO.NET
- Connecting to a Database
- Manipulating Data
- Using the Data Form Wizard
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Connecting to a Database
To access data in a database, you must first establish a connection using an ADO.NET connection object. Two connection objects are included in the .NET Framework: the OleDbConnection object, for working with the same OLE DB data providers you would access through traditional ADO, and the SqlConnection object, for optimized access to Microsoft SQL Server. Because these examples connect to the Microsoft Jet Database, you'll be using the OleDbConnection object. To create an object variable of type OleDbConnection and initialize the variable to a new connection, you could use a statement such as the following:
OleDbConnection cnADONetConnection = new OleDbConnection();
To use ADO.NET, the first step that you need to take is to add the proper Namespace to your project. Double-click the form now to access its events. Scroll to the top of the class and add the following using statement on the line below the other using statements:
using System.Data.OleDb;
You're going to create a module-level variable to hold the connection, so place the cursor below the left bracket ({) that follows the statement public class fclsMain : System.Windows.Forms.Form and press Enter to create a new line. Enter the following statement:
OleDbConnection m_cnADONetConnection = new OleDbConnection();Before using this connection, you must specify the data source to which you want to connect. This is done through the ConnectionString property of the ADO.NET connection object. The ConnectionString contains connection information such as the name of the provider, username, and password. The ConnectionString might contain many connection parameters; the set of parameters available varies depending on the source of data that you are connecting to. Some of the parameters used in the OLE DB ConnectionString are listed in Table 21.1. If you specify multiple parameters, separate them with a semicolon.
Table 21.1 Possible Parameters for ConnectionString
|
Parameter |
Description |
|
Provider= |
The name of the data provider (Jet, SQL, and so on) to use. |
|
Data Source= |
The name of the data source (database) to connect to. |
|
UID= |
A valid username to use when connecting to the data source. |
|
PWD= |
A password to use when connecting to the data source. |
|
DRIVER= |
The name of the database driver to use. This isn't required if a DSN is specified. |
|
SERVER= |
The network name of the data source server. |
The Provider= parameter is one of the most important at this point and is governed by the type of database you're accessing. For example, when accessing a SQL database, you specify the provider information for SQL; when accessing a Jet database, you specify the provider for Jet. In this example, you'll be accessing a Jet (Microsoft Access) database, so you'll use the provider information for Jet.
In addition to specifying the provider, you're also going to specify the database. I've provided a sample database at the Web site for this book. This code assumes that you've placed the database in a folder called C:\Temp. If you are using a different folder, you'll need to change the code accordingly.
To specify the ConnectionString property of your ADO.NET connection, place the following statement in the Load event of your form:
m_cnADONetConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\contacts.mdb";
After the connection string is defined, a connection to a data source is established by using the Open() method of the connection object. The Open() method has the following syntax:
objectvariable.Open();
NOTE
Refer to the online documentation for information on the connection strings for providers other than Jet.
When you attach to an unsecured Jet database, it's not necessary to provide a username and password. When attaching to a secured Jet database, however, you'll have to provide a username and a password. This is done by passing the username and password as parameters in the ConnectionString property. The sample database I've provided isn't secured, so it's not necessary to provide a username and password.
Closing a Connection to a Data Source
You should always explicitly close a connection to a data source. This means that you should not rely on a variable going out of scope to close a connection, but you should force an explicit disconnect via code. This is accomplished by calling the Close() method of the connection object.
You're now going to write code to explicitly close the connection when the form is closed. Start by clicking the Form1.cs tab to return to the form designer. Next, click the Events button on the Properties window (the lightening bolt) to access the list of events for the form. Double-click the Closed event to create a new event handler. Enter the following statement in the Closed event:
m_cnADONetConnection.Close();