ODBC

Info

  • ADO.NET Data Provider for Data Sources that have ODBC driver support. It is recommended to use a managed provider other than ODBC to access a database. For example, it is better to use Npgsql to connect to PostgreSQL instead of using ODBC.

  • Exists in namespace System.Data.Odbc and assembly System.Data

  • Works on Windows, Linux, and Unix.

  • Works on Windows via the native Windows odbc32.dll

  • Works on Linux/Unix via:
  • List of unixODBC drivers

  • List of ODBC Vendors

  • ODBC can connect to various databases which has an ODBC driver installed:
  • Microsoft did not add System.Data.Odbc to the System.Data.dll assembly until Dot NET 1.1 (Visual Studio.net 2003). For Dot NET 1.0 (Visual Studio.net 2002), ODBC was available as Microsoft.Data.Odbc.dll as a separate download from MSDN

Current Status

  • OdbcCommandBuilder has been implemented.

  • Can connect on Windows via native Windows odbc32.dll

  • Can connect on Linux via unixODBC’s libodbc.so or iODBC’s libiodbc.so

  • Various databases have been tested using their ODBC drivers: MySQL, PostgreSQL, Oracle, IBM DB2, and Microsoft SQL Server. Please note, some drivers maybe good while others may not be. If a managed provider is available, it recommend to use that managed provider.

  • 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

Action plan

  • Need to create more test cases to find and fix bugs

  • Test more ODBC drivers

  • Test other setups

Missing Features

  • Odbc Output Parameters

  • Complex Parameter Types (Binary, Numeric, Guid)

Testing ODBC Providers

You can test a native odbc provider with ADO.NET Odbc classes. There is a possiblity of bugs in either Managed code or the native odbc providers. Before filing a bug, make sure your native provider works with other languages (C, perl, etc).

To test a odbc provider

  • Create a DSN entry at /etc/unixODBC/odbc.ini and /etc/unixODBC/odbcinst.ini.

  • Provide the connection string information in odbc.config file under mcs/class/System.Data/Test/ProviderTests. The app settings name should be “ConnString”.

  • Go to the source director mcs/class/System.Data/Test/ProviderTest.

  • In a shell, enter “make run-test DATABASE=odbc”.

The following topics are for running tests for individual providers as stand-alone tests.

Testing ODBC provider with IBM DB2 Universal Database

Testing ODBC provider with MySQL

Prerequisites 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

Connection String Format

  • 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 (also called DSN-less connection). Note the use of DRIVER and SERVER parameters:
"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 = new OdbcConnection(connectionString);
       dbcon.Open();
       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:

  • Save the example to a file, such as, TestExample.cs

  • To build on Mono:

mcs TestExample.cs -r:System.Data.dll
  • Running the Example:
     mono TestExample.exe