Figure 11.38: The Agents table connects directly with the Assignments table, but uses the Agents_Specialty table as a bridge to the Specialty table.

An intermediate table, such as Agents_Specialty, is frequently used to implement many−to−many relationships.

Working with Other Databases

ADO.NET is designed to work with SQL Server, and it also provides the very handy ability to connect to a number of other database formats, including XML. This is important if you already have a database that you want to build a C# program around, or if you want to use XML to exchange data with another program. The DataConnection and DataAdapter classes you have seen throughout this chapter have been designed to work explicitly with Microsoft SQL Server. However, the .NET interface also has another set of classes which can be used to attach to many other kinds of database. To illustrate, I will create a connection to an Access database and save the data as an XML file.

I began by building a simple database in Access. I decided to create a standard address book like the one you saw in Chapter 10, “Basic XML: The Quiz Maker,” showing names, addresses, and phone numbers. The database has one table called contacts.

Creating a New Connection

You still use the server explorer to connect to an existing database. Right−click on the Data Connections item at the top of the server explorer window, and choose the Add Connection menu. This produces a dialog box like the one featured in Figure 11.39.

355

Figure 11.39: The Data Link Properties Dialog lets you choose from a number of different data sources.

Trap Unlike most dialog boxes, the Data Link Properties Dialog generally pops up with the second (connection) tab already selected. Be sure to select the provider tab first, because the connection changes based on which type of provider you use.

Under the providers tag, choose Microsoft Jet 4.0 OLE DB Provider to connect to an Access database, then press the Next button.

Hint You also can choose a provider specific to Oracle or a number of other common data sources. If you don’t see the database system you want, ODBC is a good starting point.

If you are connecting to an Access database, the dialog changes to look like Figure 11.40.

356

Figure 11.40: You can now select an access database from your file system.

Be sure to test the connection with the provided button. It’s much easier to test the connection here than in your application, where many other things could go wrong.

For an access connection, you can simply press the OK button after choosing the data file. For other types of connections you may need to provide more, such as server account information and security information.

When you look at the server explorer, you now see the connection to the Access database. You can create a data adapter object by choosing the OleDataAdapter from the Data tab of the toolbar, as illustrated in Figure 11.41.

357

Figure 11.41: The toolbox (where you normally choose components such as textboxes and labels) has a data tab that provides access to several data components.

Drag an OleDataAdapter to the form, and the dialog shown in figures 11.42 and 11.43 appear.

Figure 11.42: All the connections established on the current machine are available from the

358