This article has been
excerpted from book "A Programmer's Guide to ADO.NET in C#".
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;
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 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 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:
Driver={Microsoft ODBC for Oracle}; Server=Oracle8i7;UID=odbcuser;PWD=odbc$5xr
The connection string for a Microsoft Access Database looks like the following:
Driver={Microsoft Access Driver (*.mdb)};
DBQ=c:\Northwind.mdb
The connection string for an Excel database looks like the following:
Driver={Microsoft Excel Driver (*.xls)};
DBQ=c:\bin\book1.xls
The connection string for a Text database looks like the 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 the 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 the 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 and
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-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.
|
This essential guide
to Microsoft's ADO.NET overviews C#, then leads you toward deeper
understanding of ADO.NET. |