Introducing ADO.NET

ADO.NET is the .NET platform's new database technology, and it builds on ADO (Active Data Objects). ADO.NET provides DataSet and DataTable objects that are optimized for moving disconnected sets of data across the Internet and intranets, including through firewalls. At the same time, ADO.NET includes the traditional connection and command objects, as well as an object called a DataReader, (which resembles a forward-only, read-only ADO RecordSet, in case you're familiar with ADO). Together, these objects provide the best performance and throughput for retrieving data from a database.

In short, you'll learn about the following objects as you progress through this hour:

• OleDBConnection Used to establish a connection to an OLEDB data source.

• SqlConnection Used to establish a connection to a SQL Server data source.

• DataSet A memory-resident representation of data. There are many ways of working with a DataSet, such as through DataTables.

• DataTable Holds a resultset of data for manipulation and navigation.

• DataAdapter Used to populate a DataReader.

Connecting to a Database

To access data in a database, you must first establish a connection using an ADO.NET connection object. There are multiple connection objects included in the .NET Framework, such as 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 like this:

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 partial class frmMain : 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're connecting to. Table 21.1 lists some of the parameters used in the OLE DB ConnectionString. If you specify multiple parameters, separate them with a semicolon.

Table 21.1. Common 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. The type of data is determined by the provider.
User ID	A valid username to use when connecting to the data source.
Password	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 Server database, you specify the provider information for SQL Server, and 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're using a different folder, you'll need to change the code accordingly. Follow these steps:

1.	Specify the ConnectionString property of your ADO.NET connection now by placing 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";
2.	After the connection string is defined, a connection to a data source is established by using the Open() method of the connection object. Add the following statement to the Load event, right after the statement that sets the connection string: m_cnADONetConnection.Open();

By the Way

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 isn't necessary to provide a user-name and password. When attaching to a secured Jet database, however, you must 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 isn't 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. That means you shouldn't rely on a variable going out of scope to close a connection. Instead, 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. Follow these steps:

1.	Start by clicking the frmMain.cs [Design] tab to return to the form designer.
2.	Click the Events button on the Properties window (the lightning bolt) to access the list of events for the form.
3.	Double-click the FormClosing event to create a new event handler. Enter the following statements in the event: m_cnADONetConnection.Close(); m_cnADONetConnection.Dispose();