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 utilize 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 provides 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.
TechRepublic’s free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!
