Working With The ODBC .NET Data Provider in VB.NET
In this article, I’ll show you how to work with different data sources (or database) using the ODBC .NET data provider. You’ll see how you can use the ODBC.NET data provider to work with MySQL, Oracle, Excel, and even text data files.
The ODBC.NET data provider provides access to ODBC data provider with the help of native ODBC drivers in the same way that the OleDb.NET data provider accesses native OLE DB providers. One of the best things about working with ADO.NET data providers is that all data provider define the similar class hierarchy. The only things you need to change are the classes and the connection string.
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 SoftwareDevelopment Kit (SDK) and Visual Studio .NET. You can find the odbc.net data provider on the Microsoft site (http://www.microsoft.com/data). Alternatively, you can find the latest links of the ODBC.NET SDK on C# corner's Downloads section (http://www.csharpcorner.com/downloads.asp).
After installing ODBC.NET, you need to customize the toolbox to add the ODBC data components to it. You can customize the toolbox by right-click on the toolbox's Data tab and selecting Customize Toolbox (see Figure 11-1).
Figure 11-1: The Customize ToolBox option
Note: Makes sure the data tab is selected when you select Customize ToolBox. Otherwise new components will be added to the active tab.
Next, you click on the .NET framework Components tab (not the default COM components) and look for ODBC components. Check the appropriate boxes, and click the OK button (see figure 11-2).
Figure 11-2: Adding ODBC components to the project
Next, you need to add a reference to the Microsoft.Data.Odbc.dll assembly using the Project > Add Reference. You can use the Browser button to browse the directory. The Microsoft.Data.Odbc.dll resides in the \Program Files\Microsoft.NET\Odbc.NET directory (see Figure 11-3).
Figure 11-3: Browsing the Odbc.Net folder
Select the component and then click to add the reference to the Microsoft.Data.Odbc namespace. The add reference dialog box will appear and you'll see Microsoft.Odbc.dll listed in the selected components list (see figure 11-4).
Figure 11-4: Adding reference to microsoft.data.odbc.dll
Click OK, Using the Solution Explorer, click your project and then expand the Reference node, you'll see that the Microsoft.Data.Odbc reference has been added to your project (see Figure 11-5).
Figure 11-5: The Microsoft.Data.Odbc namespace
To make sure the ODBC data provider is installed and added to your project, you can also look at the toolbox (see figure 11-6). If the toolbox has the ODBC data components listed, that means the ODBC data provider is installed on your system and the reference to the data provider has been to your project.
Figure 11-6: ODBC data provider components
Note: The toolbox's data tab is not available for console applications.
Hope this article would have helped you in understanding working with the ODBC .NET Data Provider. See my other articles on the website on ADO.NET.