* ODBC Data Provider
- ADO.NET Data Provider for Data Sources
that have ODBC support.
- Exists in namespace System.Data.Odbc and assembly System.Data
- Works on Windows and Linux. Should have no problems working on UNIX too.
- Works on Windows via the native Windows odbc32.dll
- Works on Linux via:
- List of unixODBC drivers
- List of ODBC Vendors
- ODBC can connect to various databases which has an ODBC driver installed:
- ODBC Provider created by Brian Ritchie.
- Does not support trusted connections
** Current Status
- Can Connect on:
- Windows via native Windows odbc32.dll
- Linux via:
- unixODBC's libodbc.so
- iODBC's libiodbc.so
- Various databases have been tested using their
ODBC drivers: MySQL, PostgreSQL, Oracle, IBM DB2, and Microsoft SQL Server
- Can execute non-query commands via ExecuteNonQuery of a OdbcCommand
- Can execute aggreates and retrieve a single row single column result via
ExecuteScalar of a OdbcCommand
- Can execute queries via ExecuteReader of a OdbcCommand and
retrieve results using an OdbcDataReader
- Can get a DataTable containing schema info via GetSchemaTable() in a OdbcDataReader
- Can Fill a DataTable in a DataSet via an OdbcDataAdapter
- Works in SQL#, but Column names don't show up correctly.
- Bugs with Mono or the data provider should be reported
in Mono's Bugzilla here. If you
do not have Bugzilla user account, it is free
and easy to
create one here.
** Action Plan
- Fixing bugs
- Testing with other setups
** Testing ODBC provider with IBM DB2 Universal Database
- You need a working mono and mcs
- For Unix, you will need unixODBC or iODBC installed
- Have acess to a IBM DB2 Universal Database or
you can download from IBM
- Read these web pages about Unix, ODBC, and IBM DB2
- The ODBC provider is similar to the IBM DB2 provider.
** Testing ODBC provider with MySQL
You can test Mono's ODBC provider System.Data.Odbc with the MySQL ODBC driver MyODBC
- Take a look at OdbcTest.cs in mcs/class/System.Data/Test
- Here is a ConnectionString format if you have a DSN setup:
"DSN=dataSetName;UID=myuserid;PWD=mypassword"
- Here is a ConnectionString format if you do not have a DSN (have not
gotten this to work though):
"DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;DATABASE=test;" +
"UID=myuserid;PASSWORD=mypassword;" +
"OPTION=3";
- C# Example:
using System;
using System.Data;
using System.Data.Odbc;
public class Test
{
public static void Main(string[] args)
{
// have an ODBC DSN setup named MYSQLDSN
// that accesses a MySQL database via
// MyODBC driver for ODBC with a
// hostname of localhost and database test
string connectionString =
"DSN=MYSQLDSN;" +
"UID=myuserid;" +
"PWD=mypassword";
IDbConnection dbcon;
dbcon.Open();
dbcon = new OdbcConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
// requires a table to be created named employee
// with columns firstname and lastname
// such as,
// CREATE TABLE employee (
// firstname varchar(32),
// lastname varchar(32));
string sql =
"SELECT firstname, lastname " +
"FROM employee";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read()) {
string FirstName = (string) reader["firstname"];
string LastName = (string) reader["lastname"];
Console.WriteLine("Name: " +
FirstName + " " + LastName);
}
// clean up
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;
}
}
- Building C# Example:
- Running the Example:
mono TestExample.exe