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 .Net data provider accesses native OLE DB providers. One of the best things about working with ADO.NET data providers is all data providers 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 Software Development Kit (SDK) and Visual Studio .NET. You can find the ODBC .NET data provider on the Microsoft site (http://www.microsoft.com/data). If you don't find the ODBC .NET SDK on Microsoft site, you can find latest links of the ODBC .NET SDK on C# Corner's Downloads section:
(http://www.c-sharpcorner.com/UsefulResources/Downloads.aspx?Type=0)
After installing ODBC .NET you need to customize the toolbox to add ODBC data components to it. You can customize the toolbox by right-clicking on the toolbox's Data tab and selecting Customize Toolbox (see Figure 11-1).
Figure 11-1. The Customize Toolbox option
Note: Make 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 from 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 Browse 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 Opento add the reference to the Microsoft.Data.Odbc namespace. The Add Reference dialog box will appear and now 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 here. Now you can see the Microsoft.Data.Odbc references is added to your project using Solution Explorer and click on Project and then expand References node. (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, that means the ODBC data provider is installed on your system and the reference of data provider has been added to your project.
Figure 11-6. ODBC data provider components
Note: The toolbox's Data tab is not available for console applications.
Now you can use ODBC components in a similar way that you've used the Sql and OleDb data provider components.
The ODBC .NET data provider installation adds the Microsoft.Data.Odbc namespace to the namespace, which defines the classes for ODBC data providers. To use the ODBC .NET data provider, you must add a using statement for the Microsoft.Data.Odbc namespace to your application:
using Microsoft.Data.Odbc;
Understanding the ODBC .Net Data Provider
You've seen how to use the SQL and OleDb data providers in previous chapters. Working with the ODBC data provider is no different than 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 a reference to this namespace before you start using the ODBC data provider classes.
The ODBC data provider defines similar classes and a class hierarchy as the Sql and OleDb data providers. Further, you can use the ODBC classes similarly to how you've used SQL and OleDb classes. Table 11-1 defines the ODBC .NET data provider classes (discussed in more detail in Chapter 5).
Table 11-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 data adapter. |
| OdbcDataReader |
Represents a data reader. |
| OdbcError |
Represents errors and warnings. |
| OdbcErrorCollection |
Represents collection of errors and warnings. |
| OdbcException |
ODBC exception class. |
| OdbcParameter |
Represents an ODBC parameter. |
| OdbcParameterCollection |
Represents a parameter collection. |
| OdbcTransaction |
Represents a transaction. |
As you can see from Table 11-1, the ODBC data provider has connection, command, data adapter, parameter, exception and errors, command builder, data reader, transaction, and other classes similar to the Sql and OleDb data providers. To use the ODBC data provider classes, you create a connection object, then fill data from the connection to a data adapter or a data reader, and they display the data.
Now I'll show you an example of how to access data from a data source using the ODBC data provider. In this example, I'll use Access 2000 Northwind database as the data source.
Before creating a connection, the first thing you need to understand is the connection string. The connection string for OdbcConnection contains a data source driver and the data source path with an optional user ID and password. Optionally, you can also use an ODBC Data Source Name (DSN) as a connection string. You create a DSN from the ODBC Administration.
The connection string for an Oracle database looks like following:
Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;UID=odbcuser;PWD=odbc$5xr
The connection string for a Microsoft Access database looks like following:
Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\Northwind.mdb
The connection string for an Excel database looks like following:
Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls
The connection string for a text database looks like following:
Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\
You can use any data source name (DSN) by using the following connection string:
DSN=dsnname
The connection string for a SQL Server database looks like following:
"DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=Qvr&77xk;DATABASE=northwind;";
Listing 11-1 reads data from Northwind database and shows the results on the console. In this sample, I created a console application to test the code. As you can see from Listing 11-1, first I included the Microsoft.Data.Odbc namespace. After that I created an OdbcConnection object with Microsoft Access ODBC driver and the Northwind database. The next step was to create an OdbcCommand object and call the ExecuteReader method, which returns OdbcDataReader. After that I read data from the data reader and displayed the results on the console.
Listing 11-1. Reading data from Northwind using the ODBC data provider
using System;
using Microsoft.Data.Odbc;
namespace FirstODBCSamp
{
class Class1
{
static void Main(string[] args)
{
// Build a connection and SQL strings
string connectionString = @"Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\Northwind.mdb";
string SQL = "SELECT * FROM Orders";
// Create connection object
OdbcConnection conn = new OdbcConnection(connectionString);
// Create command object
OdbcCommand cmd = new OdbcCommand(SQL);
cmd.Connection = conn;
// Open connection
conn.Open();
// Call command's ExecuteReader
OdbcDataReader reader = cmd.ExecuteReader();
// Read the reader and display results on the console
while (reader.Read())
{
Console.Write("OrderID:"+reader.GetInt32(0).ToString() );
Console.Write(" ,");
Console.WriteLine("Customer:" + reader.GetString(1).ToString() );
}
// close reader and connection
reader.Close();
conn.Close();
}
}
}
The output of Listing 11-1 looks like Figure 11-7.
Figure 11-7. The output of Listing 11-7
Note:
In the next part of this chapter, you will learn how to access and manipulate MySQL server databases using ODBC .NET data provider.
Pradeep YadavPosted Sep 24, 2018, 7:19 AM
Thanks for sharing the same, howeverplease provide some details for connecting SAP Hana by using ODBC driver
Lloyd FoldenPosted Aug 1, 2018, 6:57 PM
When they change the site, can't they keep the bloody links updated. I can see external links going and no one bothering to make sure they stay good, but internal links going bad just shows a lazy, sloppy approach
Randy RaymondPosted Mar 17, 2009, 6:04 PM
First article that I have come across that actually has working code. I changed the connection string for my setup, and voila', it worked. Is there a command that gets a list of the tables in a connected dataset, and a list of columns in a specific table? Thank you
Venkat RaoPosted May 10, 2007, 4:03 AM
Hi Sir, I am venkat.working on C#.net.I have one problem with ado.net. I am not fimiliar with C#.net.I have two froms form1 contains datagrid with data .and form2 contains textboxes and comboboxes.Here what i need is i can able to edit the datagrid in form1. if i try to edit i can able to open form2 with databse of form1(datagrid data).once i click save button in form2 data need to be updated.please can you help me .I am waiting for your great response. thanking you sir venkat [email protected]