The ODBC.NET Data Provider in ADO.NET

This article has been excerpted from the book "A Programmer's Guide to ADO.NET in C#".
 
The ODBC.NET data provider installation adds 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:
  1. using Microsoft.Data.Odbc; 
You've seen how to use the SQL and OleDb data providers in previous articles. 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 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 as you've used the SQL and OleDb classes. Table 11-1 defines the ODBC.NET data provider classes.
 
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
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 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, fill data from the connection to a data adapter or a data reader, and then 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 the 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 the following:
  1. Driver={Microsoft ODBC for Oracle}; Server=Oracle8i7;UID=odbcuser;PWD=odbc$5xr 
The connection string for a Microsoft Access Database looks like the following:
  1. Driver={Microsoft Access Driver (*.mdb)};  
  2. DBQ=c:\Northwind.mdb 
The connection string for an Excel database looks like the following:
  1. Driver={Microsoft Excel Driver (*.xls)};  
  2. DBQ=c:\bin\book1.xls 
The connection string for a Text database looks like the following:
  1. Driver={Microsoft Text Driver (*.txt; *.csv)};  
  2. DBQ=c:\ 
You can use any data source name (DSN) by using the following connection string:
  1. DSN=dsnname 
The connection string for a SQL Server database looks like the following:
  1. "DRIVER={SQL Server};  
  2. SERVER=MyServer;UID=sa;PWD=Qvr&77xk;DATABASE= northwind;"; 
Listing 11-1 reads data from the 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 Microsoft.Data.Odbc namespace. After that, I created an OdbcConnection object with the Microsoft Access the 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 and the results on the console.
 
Listing 11-1: Reading data from Northwind using the ODBC data provider
  1. using System;  
  2. using Microsoft.Data.Odbc;  
  3. namespace FirstODBCSamp  
  4. {  
  5.      class Class1  
  6.     {  
  7.         static void Main(string[] args)  
  8.         {  
  9.              // Build a connection and SQL strings  
  10.             string connectionString = @"Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\Northwind.mdb";  
  11.             string SQL = "SELECT * FROM Orders";  
  12.              // Create connection object  
  13.             OdbcConnection conn = new OdbcConnection(connectionString);  
  14.              // Create command object  
  15.             OdbcCommand cmd = new OdbcCommand(SQL);  
  16.             cmd.Connection = conn;  
  17.              // Open Connection  
  18.             conn.Open();  
  19.              // Call command's ExecuteReader  
  20.             OdbcDataReader reader = cmd.ExecuteReader();  
  21.              // Read the reader and display results on the console  
  22.             while (reader.Read())  
  23.             {  
  24.                 Console.Write("OrderID:" + reader.GetInt32(0).ToString());  
  25.                 Console.Write(" ,");  
  26.                 Console.WriteLine("Customer:" + reader.GetString(1).ToString());  
  27.             }  
  28.              // Close reader and connection   
  29.             reader.Close();  
  30.             conn.Close();  
  31.         }  
  32.     }  
The output of Listing 11-1 looks like figure 11-7.
 
figure-11.7.gif
 
Figure 11-7: The output of Listing 11-1
 

Conclusion

 
Hope this article would have helped you in understanding the ODBC.NET Data Provider in ADO.NET. See my other articles on the website on ADO.NET.
 
adobook.jpg
 
This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.