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.
Figure 11.39. The Data Link Properties Dialog lets you choose from a number of different data sources.
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.
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.
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.
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 drop-down list.
Figure 11.43. You are prompted for a SELECT statement to initialize the data adapter.
The editor adds an adapter and a connection object to your form. Although these new objects are technically different objects than the SqlConnnection and SqlDataAdapter you’ve used, the OLE versions have the same properties, methods, and events. Encapsulation again saves you from worrying about how the internals of an SQL data object are different from an OLE data object.
Once you have an adapter and a connection, you can build a data set from the adapter, and attach the data set to a data grid just like you did with the SQL Server databases.
Converting a Data Set to XML
If you’ve been paying close attention, you might have noticed that every time you create a new data set Visual Studio adds a new .XSD file to your project. You might recall from Chapter 10, “Basic XML: The Quiz Maker,” that an XSD file is an XML schema. The .NET framework maintains a very close relationship between databases and XML. It’s very easy to convert between typical data and an XML file. To illustrate, I’ve added a button to the Access-XML form that displays the contacts database as an XML file. Figure 11.44 illustrates this function in action:
Figure 11.44. The text box displays an XML version of the Access database.
The code in the View as XML button illustrates how this is done:
txtXML.Text = myDS.GetXml();
The Dataset class has a GetXml() method which extracts XML data from a data set. The resulting string can be stored as a file or be manipulated like any other XML data.
Reading from XML to a Data Source
You also can easily read an XML file and use it as a data source. If you press the Load Quiz Data button, the program loads up the quiz XML from Chapter 10 and binds it to the data grid, as shown in Figure 11.45.
Figure 11.45. The XML quiz file from the last chapter can be viewed as a data set.
