Connecting to Databases Other Than SQL Server

As mentioned earlier, ADO.NET connects to databases through direct or disconnected data access with the assistance of data providers. Data providers are essentially ADO.NET adapters that optimize data storage and retrieval for a specific database platform. Most of the examples in this chapter used the Microsoft SQL Server database and therefore used the SQL Server data provider. Until database vendors such as Oracle, Sybase, and Informix produce data providers that optimize access to their products, application developers need to connect via the Open Database Connectivity (ODBC) managed provider.

Connecting to MySQL

One of the most popular databases servers connected to the Internet today is MySQL. MySQL is an open-source database implementation that is publicly available from the Downloads section of the MySQL Web site ( http://www.mysql.com/downloads ).

ODBC is an interface that describes connection points between databases and the host operating system. ODBC drivers allow different database systems to communicate in a uniform way to different applications running on different operating systems. To facilitate this, database vendors offer ODBC drivers that bridge their product to different operating systems. In the case of the MySQL database, you can download an ODBC driver for the Microsoft Windows operating system from http://www.mysql.com/downloads/api-myodbc-2.50.html .

After downloading and installing the driver, create a system ODBC data source pointing to the online MySQL database. From the Windows Desktop, click Start ˜ Control Panel ˜ Administrative Tools ˜ Data Sources (ODBC). The ODBC Administrator will display, as shown in Figure 2-12.

Figure 2-12: The ODBC Data Source Administrator

Next, select the System DSN tab and click the Add button. A list of installed ODBC drivers displays. Scroll to the end of the list and select the MySQL driver. The new MySQL data source form will display, as shown in Figure 2-13. Enter a name for your data source and the connection information.

Figure 2-13: The new MySQL data source interface

In addition to the ODBC managed provider, you need to download the Microsoft Data Access Components (MDAC) package version 2.6 and higher. The MDAC package contains a collection of updated ODBC drivers and is available for download from Microsoft at http://www.microsoft.com/data/download_270RTM.htm .

After restarting Visual Studio, the ODBC managed provider is available for use. Set the reference to its namespace with the following statement:

 using System.Data.Odbc; 

You can use the ODBC managed provider objects to connect to and interact with the database just as you would with the SqlClient objects:

 public void TestConnection() {     string strConnection;     OdbcConnection connection;     try     {         strConnection = "DSN=IssueTracker;UID=sys;PWD=syst3m";         connection = new OdbcConnection( strConnection );         connection.Open();         //perform data access functions     }     catch( OdbcException exception )     {         EventLog systemLog = new EventLog();         systemLog.Source = "IssueTracker";         systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 );     }     finally     {         connection.Close();     }     return; } 

Connecting to Microsoft Access

Microsoft Access has proven itself well as a small and reliable application database. Although it does not offer the performance or reliability that is needed by an enterprise application, stand-alone desktop applications can make good use of Windows' built-in driver support.

Connecting to a Microsoft Access database is similar to connecting to a MySQL database because both rely on the ODBC interface and ADO.NET's OLE data provider. The namespace reference looks more like this, though:

 using System.Data.OleDb; 

The only real difference is the connection string passed to the OleDbConnection object's constructor:

 public void TestConnection() {     string strConnection;     OleDbConnection connection;     try     {         strConnection = "Provider=Microsoft.JET.OLEDB.4.0;" +             @"Data Source=C:\Program Files\IssueTracker\Data\  IssueTracker.mdb  "         connection = new OleDbConnection( strConnection );         connection.Open();         //perform data access functions     }     catch( OleDbException exception )     {         EventLog systemLog = new EventLog();         systemLog.Source = "IssueTracker";         systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 );     }     finally     {         connection.Close();     }     return; } 

Once you have established a connection with the database, you can store and retrieve data just as with the MySQL and SQL Server databases.