no common super class for OleDbConnection and OdbcConnection

[filibuster]

Hi all,

I am playing around with database access from C#.

I got some working code for connecting to oracle (complete snippets included below) using two different strategies:
- ODBC access via OdbcConnection
- OLE access via OleDbConnection

The problem I am facing is that I do have to write the same code twice, because OleDbConnection and OdbcConnection do not share the same super class (just look at the two samples, both pieces of code are pretty much the same).
In my opinion this is a major drawback compared to JDBC where I only have to change the driver specification and that's it. (this is because database access in JDBC is defined through plain APIs).
This becomes an issue when changing the target db or the database configuration.

The class hierarchy is as follows:

System.Object
System.MarshalByRefObject
System.ComponentModel.Component
Microsoft.Data.Odbc.OdbcConnection


System.Object
System.MarshalByRefObject
System.ComponentModel.Component
System.Data.OleDb.OleDbConnection



Am I missing something ??




/** OracleOdbcDemo.cs */

using System;
using System.Data;

//using System.Data.Odbc;

using Microsoft.Data.Odbc;

/** demo for accessing oracle database within C# through the Odbc class.

This is similar to using the JDBC-ODBC bridge in Java/JDBC

To make the whole thing work, a datasource named "oracle_odbc" must
be available on your machine.

To run this sample, you have to install separately ODBC.NET from the following site:

http://download.microsoft.com/download/dasdk/Install/1.0.4030.0/W98NT42KMeXP/EN-US/odbc_net.msi

http://www.microsoft.com/downloads/release.asp?ReleaseID=35715

A new directory named "Odbc.Net" will be created in your Microsoft.NET folder, e.g:
C:\Program Files\Microsoft.NET\Odbc.Net

The directory contains some txt and help files, and most importantly, a file called "Microsoft.Data.Odbc.dll"
You will need this file to compile your C# program as follows:

csc /r:Microsoft.Data.Odbc.dll OracleOdbcDemo.cs
*/
class OracleOdbcDemo {
public static void Main() {
try {
Console.WriteLine("Oracle Connectivity through ODBC DB with C#"

// Create/Open Oracle Connection
String dataSourceName = "oracle_odbc";
String user = "system";
String password = "manager";

// e.g. "Provider=MSDAORA;DSN=zwu.world;UID=system;PWD=manager
String connectionString =
"Provider=MSDAORA" +
";DSN=" + dataSourceName +
";UID=" + user +
";PWD=" + password;

Console.WriteLine("Oracle Connection:"
Console.WriteLine(" dataSourceName=[" + dataSourceName + "]"
Console.WriteLine(" user=[" + password + "]"
Console.WriteLine(" password=[" + password + "]"
Console.WriteLine(" connectionString=[" + connectionString + "]"

Console.WriteLine("Creating connection"
OdbcConnection connection = new OdbcConnection(connectionString);

Console.WriteLine("Opening connection"
connection.Open();


// Create Oracle Command
Console.WriteLine("Creating command"
OdbcCommand command = connection.CreateCommand();

command.CommandText = "select table_name from user_tables";
Console.WriteLine("CommandText=[" + command.CommandText + "]"


// Execute Oracle Command
Console.WriteLine("Executing Command/Reader"
Console.WriteLine(""
OdbcDataReader reader = command.ExecuteReader();


// Display Results
Console.WriteLine("table_name"
while (reader.Read()) {
Console.WriteLine(" " + reader.GetString(0));
}
Console.WriteLine(""


// Cleanup
Console.WriteLine("Closing Reader"
reader.Close();

Console.WriteLine("Closing Connection"
connection.Close();

Console.WriteLine("Complete"
} catch(Exception ex) {
Console.WriteLine("Error: " + ex.Message);
}
}
}



/** OracleOleDbDemo.cs */

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

/** demo for accessing oracle database within C# through the OleDb class.

This is similar to using Oracle's "Thick" OCI Driver in Java/JDBC.

To make the whole think work, Oracle TNS must be set up properly on your
client machine (this is the program where the .exe file will be run).

To verify your TNS Setup, type in the following

tnsping oracle

(or whatever value the tnsName variable is given below)
*/
class OracleOleDbDemo {
public static void Main() {
try {
Console.WriteLine("Oracle Connectivity through OLE DB with C#"

// Create/Open Oracle Connection
String tnsName = "oracle";
String user = "system";
String password = "manager";
String connectionString =
"Provider=OraOLEDB.Oracle" +
";Data Source=" + tnsName +
";User ID=" + user +
";Password=" + password;

Console.WriteLine("Oracle Connection:"
Console.WriteLine(" tnsName=[" + tnsName + "]"
Console.WriteLine(" user=[" + password + "]"
Console.WriteLine(" password=[" + password + "]"
Console.WriteLine(" connectionString=[" + connectionString + "]"

Console.WriteLine("Creating connection"
OleDbConnection connection = new OleDbConnection(connectionString);

Console.WriteLine("Opening connection"
connection.Open();


// Create Oracle Command
Console.WriteLine("Creating command"
OleDbCommand command = connection.CreateCommand();

command.CommandText = "select table_name from user_tables";
Console.WriteLine("CommandText=[" + command.CommandText + "]"


// Execute Oracle Command
Console.WriteLine("Executing Command/Reader"
Console.WriteLine(""
OleDbDataReader reader = command.ExecuteReader();


// Display Results
Console.WriteLine("table_name"
while (reader.Read()) {
Console.WriteLine(" " + reader.GetString(0));
}
Console.WriteLine(""


// Cleanup
Console.WriteLine("Closing Reader"
reader.Close();

Console.WriteLine("Closing Connection"
connection.Close();

Console.WriteLine("Complete"
} catch(Exception ex) {
Console.WriteLine("Error: " + ex.Message);
}
}
}

 

[chiph]

I think it's this way because ODBC and OLEDB use two vastly different technologies for connecting to databases. Microsoft is trying to get people to stop using ODBC, but there's such a legacy of using it, that it will never go away totally.

What's wrong with using the OraOLEDB driver for both?

Chip H.

 

[filibuster]

there can be cases where odbc is necessary, e.g. if there is only an ODBC driver for a database. when building cross-platform dbs one should not be forced to use a certain driver implementation.

also there is some SQL*Server specific stuff in C#, which I think is unecessary. ok, it might be faster than ODBC, but with a sound architecture like JDBC you don't need to code your connectivity twice.

 

[chiph]

also there is some SQL*Server specific stuff in C#, which I think is unecessary.

Yeah, I know.
Think of it as the extend part of "Embrace & Extend".

Chip H.

 

[filibuster]

I do ;-). But one should be aware of this stuff. C# is probably more flexible than java, but when it comes to portability, one must be very careful in C#. In the end it all boils down to building own abstraction layers for os/db/whatever/.net dependant calls which can be replaced by other implementation when moving an app around.

the solution here is probably to build an own database connectivity similar to JDBC and let these classes create the connections etc, e.g.

public interface IInterface {
public IConnection connect();
}

public IConnection {
public IResultSet executeQuery(String query);
}


// odbc implementation of connection
public class XOdbcConnnection implements IConnection {
public OdbcConnection realConnection;

public IResultSet executeQuery(String query) {
return realConnection.xyz(); // run query and return results
}
}

// odbc implementation of driver
public class OdbcDriver {
public IConnection connect() {
XOdbcConnection con = new XOdbcConnection();
con.realConnection = new OdbcConnection();
return con;
}
}

// test
public static void main() {
IConnection con = OdbcDriver.connect(); // only this line needs to be changed for other dbs/drivers
IResultSet rs = con.exeucteQuery("select * from customers"
}

of course the code is not complete, but I think it shows what I mean - create some own JDBC-like interfaces which are implemented as wrappers (decorator pattern could be applied) to call the Ole / Odbc / SqlServer / whatever stuff.

Thanks for your input, chip !

 

[chiph]

There's a good article in this month's (next months?) issue of Visual Studio Magazine about how ADO and OLEDB works in .NET.

To summarize:

The SQL Server managed provider that comes with .NET talks directly to the SQL Server client communications layer. The OLEDB provider goes through COM Interop (aka penalty layer) and talks to the MDAC that we all know & love. The recently released ODBC does the same thing.

The other thing that happens is the managed providers call SQL Server through the sp_executeSQL stored procedure, which puts the compiled SQL into cache. The OLEDB doesn't, and so subsequent calls are no faster. Calling sp_executeSQL yourself is better, but requires coding changed.

The good news for you is that Microsoft and Oracle are working together to develop a managed provider for Oracle databases. I would expect it to be for Oracle 9i, although it might work with 8i too. You might be able to get a beta through OTN.

Hope this helps.
Chip H.