ODBC is not dead

ODBC (Open DataBase Connectivity) is a uniform interface standard that you may use to access database systems. Learn about ODBC classes and how to use the ODBC .NET Data Provider within the Microsoft .NET Framework.

One of the .NET Framework features often highlighted is its new data access model, ADO.NET. Some of its improvements over its predecessors include scalability, speed, and its disconnected nature features. A .NET data provider is used to access a database system; a good example is the Oracle Data Provider for .NET. One problem is some systems don't have a data provider available, so, thankfully, you can easily add support for the older ODBC (Open DataBase Connectivity) technology. ODBC is an established industry standard with ODBC drivers available for most systems in use.

Working with ODBC
ODBC is a uniform interface standard that you may use to access database systems. It's a database access library that enables applications to work with data contained in a database. One aspect of ODBC is that you may use it to access almost any type of database, albeit Oracle, Access, Sybase, mySQL, spreadsheets, text files, and so forth. It's a mature technology, so locating an ODBC driver for a particular database system is usually not a problem.

ODBC .NET Data Provider
The ODBC .NET Data Provider is an add-on component to the Microsoft .NET Framework Software Development Kit (SDK). It provides access to native ODBC drivers the same way that the OLE DB .NET Data Provider provides access to native OLE DB Providers. The ODBC .NET Data Provider is intended to work with all compliant ODBC drivers, but the Microsoft site states that it has only been tested with the Microsoft SQL ODBC Driver, Microsoft ODBC driver for Oracle, and the Microsoft Jet ODBC driver.

ODBC setup
ODBC consists of a driver and driver manager. The driver is specific to a database vendor's product. For instance, Oracle provides a driver for working with an Oracle system. The driver manager is used to install the necessary driver files and configure data sources (that take advantage of the driver) to be used in applications. On Windows-based systems, the ODBC Data Source Administrator is used to create and maintain ODBC connections. You may utilize an ODBC driver in a .NET application once it is property installed and set up.

ODBC classes
Once you install the ODBC .NET Data Provider, you can utilise it in an application. If you're using Visual Studio .NET, you may add a reference to its dll file, Microsoft.Data.Odbc.dll. If you're developing from the command line, you can add a reference during compilation or copy the dll file into the application's bin directory.

The ODBC classes are contained in the Microsoft.Data.Odbc namespace. It includes the following classes:

• OdbcConnection: Used to connect to an ODBC data source. The name assigned to the ODBC data source, during its setup, is used to access it.
  
• OdbcCommand: Used to execute a command against a connection.
  
• OdbcDataReader: Allows you to loop through the results of a query against a data source.
  
• OdbcParameter: Used to bind a parameter to a command.
  
• OdbcDataAdapter: Used to fill a DataSet object from an ODBC data source.
  
• OdbcCommandBuilder: Creates default Insert, Update, and Delete statements for an ODBC data adapter.
  

The next example takes advantage of a few of these classes. It utilizes a previously established ODBC data source (aptly called Test). The DSN name is used in the connection string, along with the user id and password to access the database. A basic SQL statement is used to retrieve all rows from the Customers table with the column values displayed. Finally, the connection is closed and all other objects are disposed. The C# code follows:

using System; using Microsoft.Data.Odbc; namespace BuilderODBC { class TestClass { static void Main(string[] args) { string connectionString = "DSN=Test;UID=Chester;Pwd=Tester;"; string sql = "SELECT CustomerID, ContactName, ContactTitle FROM Customers"; OdbcConnection conn= new OdbcConnection(connectionString); conn.Open(); OdbcCommand comm = new OdbcCommand(sql, conn); OdbcDataReader dr = comm.ExecuteReader(); while (dr.Read()) { Console.WriteLine(dr.GetValue(0).ToString()); Console.WriteLine(dr.GetValue(1).ToString()); Console.WriteLine(dr.GetValue(2).ToString()); } conn.Close(); dr.Close(); comm.Dispose(); conn.Dispose(); } } }

The equivalent VB .NET code follows:

Imports Microsoft.Data.Odbc Module Module1 Sub Main() Dim conn As OdbcConnection Dim comm As OdbcCommand Dim dr As OdbcDataReader Dim connectionString As String Dim sql As String connectionString = "DSN=PracticalLotusScriptTest;UID=Chester;Pwd=Tester;" sql = "SELECT CustomerID, ContactName, ContactTitle FROM Customers" conn = New OdbcConnection(connectionString) conn.Open() comm = New OdbcCommand(sql, conn) dr = comm.ExecuteReader() While (dr.Read()) Console.WriteLine(dr.GetValue(0).ToString()) Console.WriteLine(dr.GetValue(1).ToString()) Console.WriteLine(dr.GetValue(2).ToString()) End While conn.Close() dr.Close() comm.Dispose() conn.Dispose() End Sub End Module

Dealing with ODBC errors
The Microsoft.Data.Odbc namespace includes the OdbcException class for handling any errors that may occur when interacting with ODBC data sources. We can alter our example to utilize this class to handle any runtime exceptions that may occur. The altered C# code follows:

using System; using Microsoft.Data.Odbc; namespace BuilderODBC { class TestClass { static void Main(string[] args) { string connectionString = "DSN=PracticalLotusScriptTest;UID=Chester;Pwd=Tester;"; string sql = "SELECT CustomerID, ContactName, ContactTitle FROM Customers"; OdbcConnection conn = null; OdbcCommand comm = null; OdbcDataReader dr = null; try { conn= new OdbcConnection(connectionString); conn.Open(); comm = new OdbcCommand(sql, conn); dr = comm.ExecuteReader(); while (dr.Read()) { Console.WriteLine(dr.GetValue(0).ToString()); Console.WriteLine(dr.GetValue(1).ToString()); Console.WriteLine(dr.GetValue(2).ToString()); } dr.Close(); } catch (OdbcException oe){ Console.WriteLine("An ODBC exception occurred: " + oe.Message.ToString()); } catch (Exception e) { Console.WriteLine("An exception occurred: " + e.Message.ToString()); } finally { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } comm.Dispose(); conn.Dispose(); } } } }

You'll notice the ODBC objects are declared outside the try/catch block, so they may be utilized in the finally block. ODBC exceptions are handled separately from generic errors. The connection is checked to see if it is open before closing it.

Every data source available
ODBC has been around for numerous years. Consequently, ODBC drivers exist for almost any data source imaginable. The ODBC .NET Data Provider providers ODBC access within .NET applications through the Microsoft.Data.Odbc namespace. Use this data provider when you cannot find a native data provider for the data system used in your application.