Chapter 10: Working with the ODBC .NET Data Provider

In previous chapters, you saw how to use the Sql data provider to work with SQL Server databases. You also learned that there aren't great differences between any of the data providers. With a few minor exceptions, it's just a matter of changing class names and the connection string. You don't believe us? To prove this theory, you'll see how to use the ODBC data provider in this chapter.

The ODBC data provider uses ODBC drivers to connect to different data sources; it's useful when you're not sure what database your clients will be using in their applications. The user shouldn't have any trouble connecting as long as they have an ODBC driver for the database installed on their machine. In this chapter, you'll see how you can use the ODBC .NET data provider to work with MySQL, Oracle, Excel, and even text data files.

Using the ODBC .NET Data Provider

You read a brief discussion about the ODBC .NET data provider in Chapter 4. The ODBC .NET data provider provides access to ODBC data sources with the help of native ODBC drivers in the same way that the OleDb data provider accesses native OLE DB providers. One of the best things about working with ADO.NET data providers is that all the data providers define a similar class hierarchy. The only things you need to change are the classes and the connection string.

Before you start using the ODBC .NET data provider, let's quickly look at some installation issues.

Note

If you're using Visual Studio .NET (VS .NET) code name Everett, you don't need to worry about installation because the ODBC .NET data provider is already installed.You can skip the following section.

Installing the ODBC .NET Data Provider

Unlike the Sql and OleDb data providers, the ODBC data provider is an add-on component to the .NET Framework. If the ODBC .NET provider isn't installed on your system, you can download it from the .NET Software Development Kit (SDK) and VS .NET on the Microsoft site (www.microsoft.com/data).

Note

Microsoft VS .NET code name Everett installs the ODBC .NET data provider automatically.

After installing the ODBC data provider, you need to customize the Toolbox to add the ODBC data components to it. You can customize the Toolbox by right-clicking the Data tab and selecting Customize Toolbox.

Note

Make sure the Data tab is selected when you choose Customize Toolbox. Otherwise, new components will be added to the active tab.

Next, click the .NET Framework Components tab (not the default COM components) and look for the ODBC components. Check the appropriate boxes, and then click the OK button. These check boxes are OdbcCommand, OdbcCommandBuilder, OdbcConnection, and OdbcDataAdapter.

Next, you need to add a reference to the Microsoft.Data.Odbc.dll assembly using Project Add Reference. You can use the Browse button to browse the directory. The Microsoft.Data.Odbc.dll assembly resides in the \Program Files\Microsoft.NET\Odbc.NET directory. On the Add Reference dialog box, select Microsoft.Data.Odbc.dll and then click the OK button, which adds the Microsoft.Data.Odbc namespace reference to the project. You can see the namespace reference by expanding the References node of your project in the Server Explorer.

To make sure the ODBC data provider is installed and added to your project, you can also look at the Toolbox. If the ODBC data provider is installed on your machine, the Toolbox will display the ODBC data components.

Note

The Toolbox's Data tab isn't available for console applications. However, you can use ADO.NET in console-based applications by writing the code manually.

You saw how to use the SQL and OleDb data providers in previous chapters. Working with the ODBC data provider is no different from working with the Sql and OleDb data providers. Unlike the Sql and OleDb data providers, however, the ODBC data provider is defined in the Microsoft.Data.Odbc namespace. You must add the following reference to this namespace before you can start using the ODBC data provider classes:

 Imports Microsoft.Data.Odbc 

Note

If you're using Microsoft VS .NET code name Everett, the ODBC data provider is defined in the System.Data.Odbc namespace. Make sure you import the correct namespace.

Understanding the ODBC .NET Data Provider

The ODBC data provider defines similar classes and a similar class hierarchy as the Sql and OleDb data providers. Further, you can use the ODBC data provider classes the same way as you've used the Sql and OleDb data provider classes. Table 10-1 defines the ODBC data provider classes.

Table 10-1: The ODBC .NET Data Provider Classes

CLASS	DESCRIPTION
OdbcCommand	Similar to OleDbCommand and SqlCommand, this class represents an SQL statement or stored procedure to execute against a data source.
OdbcCommandBuilder	Similar to OleDbCommandBuilder and SqlCommandBuilder, this class automatically generates SELECT, INSERT, UPDATE, and DELETE SQL commands.
OdbcConnection	Represents a connection.
OdbcDataAdapter	Represents a DataAdapter.
OdbcDataReader	Represents a DataReader.
OdbcError	Represents errors and warnings.
OdbcErrorCollection	Represents a collection of errors and warnings.
OdbcException	Represents an ODBC Exception class.
OdbcParameter	Represents an ODBC parameter.
OdbcParameterCollection	Represents a parameter collection.
OdbcTransaction	Represents a transaction.

As you can see from Table 10-1, the ODBC data provider has many classes that are similar to the Sql and OleDb data providers. To use the ODBC data provider classes, you create a Connection object and then fill data from the Connection object to a DataAdapter or a DataReader, and they display the data.