Using ADO.NET Databases
With the advent of the .NET Framework, Microsoft decided to update its model for
accessing databases, ActiveX Data Objects (ADO), and created ADO.NET. ADO.NET
contains several enhancements over the original ADO architecture, providing improved
interoperability and performance. If you are already familiar with ADO, you will notice
that the object model of ADO.NET is a little different. For one thing, the RecordSet type
no longer exists—Microsoft has created the TableAdapter and DataSet classes that
support disconnected data access and operations, allowing greater scalability because you
no longer have to be connected to the database all the time. (To be fair, ADO provided
disconnected RecordSets, but they were the exception rather than the rule when used by
programmers.) Therefore, your applications can consume fewer resources. With the
connection pooling mechanisms of ADO.NET, database connections can be reused by
different applications, thereby reducing the need to continually connect to and disconnect
from the database, which can be a time-consuming operation.
ADO.NET is designed to be easy to use, and Visual Studio 2005 provides several wizards
and other features that you can use to generate data access code.
The Northwind Traders Database
Northwind Traders is a fictitious company that sells edible goods with exotic names. The
Northwind database contains several tables with information about the goods that
Northwind Traders sells, the customers they sell to, orders placed by customers, suppliers
that Northwind Traders obtains goods from to re-sell, shippers that they can use to send
goods to customers, and employees who work for Northwind Traders. Figure 23-1 shows
all the tables in the Northwind Traders database and how they are related to each other.
The tables that you will be using in this chapter are Orders and Products.

Figure 23-1 The tables in the Northwind Traders database.
Creating the Database
Before proceeding further, you need to create the Northwind Traders database.
Create the database

1. On the Windows Start menu, click All Programs, click Accessories, and then click
Command Prompt to open a command prompt window. In the command prompt
window, go to the \Microsoft Press\Visual CSharp Step by Step\Chapter 23 folder
in your My Documents folder.
2. In the command prompt window, type the following command:
sqlcmd –S YourServer\SQLExpress –E –iinstnwnd.sql
Replace YourServer with the name of your computer.
TIP
You can find the name of your computer by running the hostname command in the
command prompt window, before running the sqlcmd command.
This command uses the sqlcmd utility to connect to your local instance of SQL
Server 2005 Express and run the instnwnd.sql script. This script contains the SQL
commands that create the Northwind Traders database and the tables in the
database, and fills them with some sample data.
TIP
Ensure SQL Server 2005 Express is running before you attempt to create the
Northwind Traders database. You can check the status of SQL Server 2005
Express, and start it running if necessary, by using the SQL Configuration
Manager available in the Configuration Tools folder of the Microsoft SQL Server
2005 CTP program group.
3. When the script finishes running, close the command prompt window.
Accessing the Database
In this set of exercises, you will write a program that connects to the database, retrieves
the contents of the Products and Suppliers tables, and displays their contents. In the
Northwind Traders database, each product is supplied by a single supplier, but an
individual supplier can supply more than one product.
In the first exercise, you will create a data source that connects to the Northwind Traders
database and retrieves the contents of these tables, by using the wizards provided with
Visual Studio 2005.
Create a data source

1. Using Visual Studio 2005, create a new project by using the Windows Application
template. Name the project DisplayProducts and save it in the \Microsoft
Press\Visual CSharp Step by Step\Chapter 23 folder in your My Documents
folder.
TIP
If you cannot remember how to create a new Windows Application, refer to the
first exercise, “Create the Middleshire Bell Ringers Association project,” in
Chapter 20, “Introducing Windows Forms.”
2. On the Data menu, click Add New Data Source.
The Data Source Configuration Wizard starts. You use this wizard to create a
connection to a data source. A data source can be a database, an object, or a Web
service. Using an object or a Web service as a data source is beyond the scope of
this book, but we will describe how to create and use Web services in Chapter 28,
“Creating and Using a Web Service.”

3. Ensure the Database icon is selected, and then click Next. The next page of the
wizard prompts you to provide information about the connection to the database
you want to use. You have not created any data connections yet, so click the New
Connection button.
The Choose Data Source dialog box appears, allowing you to select the data
source and data provider to use. The data source specifies the type of database you
want to use, and the data provider specifies how you will connect to the database.
Some data sources can be accessed by using more than one data provider. For
example, you can connect to SQL Server by using the .NET Framework Data
Provider for SQL Server, or the .NET Framework Data Provider for OLE DB. The
.NET Data Provider for SQL Server is optimized for connecting to SQL Server
databases, whereas the .NET Framework Data Provider for OLE DB is a more
generic provider that can be used to connect to a variety of data sources, not just
SQL Server.
4. For this application, click the Microsoft SQL Server data source, and make sure

the .NET Framework Data Provider for SQL Server is selected as the data
provider.

5. Click Continue to proceed to the next step.
The Add Connection dialog box appears next. You use this dialog box to specify
which SQL Server you want to connect to, the authentication mechanism to use,
and the database you want to access.
6. Type YourServer\SQLExpress in the Server name box, where YourServer is the
name of your computer. Select the Use Windows Authentication option to log on
to the server. This option uses your Windows account name to connect to the
database, and is the recommended way to log on to SQL Server. Select the
Northwind database, and then click OK.

You are returned to the Data Source Configuration Wizard. The new data
connection is given the name YourServer\SQLExpress.Northwind.dbo.
7. Click the + sign by the Connection String label.
You will see a string that contains the connection details you have just specified.
This information is held in a format that can be used by the SQL Server provider
to connect to the server.

8. Click Next.
The connection information you have specified can also be saved in an application
configuration file. This feature allows you to modify the connection string without
needing to rebuild the application; you simply edit the application configuration
file. It is useful if you envisage ever needing to use a different database from the
one you built the application with.
Save the connection information with the default name.

9. Click Next.
The next page of the wizard allows you to select the data you want to use. You can

retrieve data from tables or views in the database, or access the results of stored
procedures and functions in SQL Server.
10. Expand the Tables folder and select the Products and Suppliers tables.

The wizard generates a DataSet object called NorthwindDataSet that you can use
to manipulate the data returned. A DataSet object represents an in-memory copy
of tables and rows retrieved from a database.
11. Click Finish to complete the wizard.
Using an Application Configuration File
An application configuration file provides a very useful mechanism allowing the user to
modify some of the resources used by the application without actually needing to rebuild
the application itself. The connection string is an example of such a resource.
When you save the connection string generated by the Data Source Configuration
Wizard, a new file is added to your project—app.config. This is the source of the
application configuration file, and it appears in the Solution Explorer. You can examine
the contents of this file by double-clicking it. You will see that it is an XML file, as
shown below:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="userSettings" ... >
<section name="DisplayProducts.Properties.Settings" ... />
</sectionGroup>
<sectionGroup name="applicationSettings" ... >
<section name="DisplayProducts.Properties.Settings" ... />
</sectionGroup>
</configSections>
<connectionStrings>
<add name="DisplayProducts.Properties.Settings.NorthwindConnectionString"
connectionString="Data Source=LON-DEV-01\SQLExpress;Initial

Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<userSettings>
<DisplayProducts.Properties.Settings />
</userSettings>
<applicationSettings>
<DisplayProducts.Properties.Settings />
</applicationSettings>
</configuration>