Bolt On Oracle Support to Existing SQLClient app?

Long shot here... I've built up a DAL for a utility app for our partners who use MSSQL with System.Data.SqlClient.... now I need to add Oracle compatibility. Is there some abstraction layer out there so I needn't add an Oracle version for each of my existing DAL methods?

No stored procs, just simple selects, updates and inserts. All inline CommandText.

Have you checked out Linked Servers in SQL Server? I just used that to execute queries against an Oracle server so I didn't have to install a client or use any different libraries.

There should be classes in the System.Data.Common namespace called DbConnection, DbCommand. The SQLClient classes inherit from them and the OracleClient ones should too.

1 hour ago,itsnotabug wrote

Long shot here... I've built up a DAL for a utility app for our partners who use MSSQL with System.Data.SqlClient.... now I need to add Oracle compatibility. Is there some abstraction layer out there so I needn't add an Oracle version for each of my existing DAL methods?

No stored procs, just simple selects, updates and inserts. All inline CommandText.

one thing you can do is refactor your DAL a bit....

lookup IDBConnection and related interfaces

also lookup System.Data.Common and how it mirrors SqlCLient

you can use .Common and then supply the right connection string

OR you can use the interfaces and new up the Oracle client or the Sql client class as needed.

SqlClient inherits from COmmon.DBCOnnection and that impliments IDBConnection

the Oracle bits should follow the same pattern.

awesome. this is why i love this site!

so i take it, by implementing IDBConnection, i won't have to use the oracle data provider or install drivers?

1 hour ago,itsnotabug wrote

awesome. this is why i love this site!

so i take it, by implementing IDBConnection, i won't have to use the oracle data provider or install drivers?

no,  you will refactor your DAL to use the "generic" DBCommand / connection and related classes.

also do not use sql strings directly ... use command builder and named parameters.

this puts your code at the more general level.

then the connection string is where you connect to a specific database type.

thats one way of doing it.

yes the clients will need to have the driver for oracle if they want to use it.

but now your code will work with both of them.

this is proving easier than i thought! the only thing i'm having trouble with is using parameters. the sqlcommand had a method for paramters I could use like:

cmd.Parameters.AddWithValue("@Key", Key);

but the dbcommand doesn't have it.. i only see

cmd.Parameters.Add(Key)... how does the command know which parameter the value is associated with? some of my queries have dozens of parameters.

internet high-five to anyone with an answer!

24 minutes ago,itsnotabug wrote

this is proving easier than i thought! the only thing i'm having trouble with is using parameters. the sqlcommand had a method for paramters I could use like:

cmd.Parameters.AddWithValue("@Key", Key);

but the dbcommand doesn't have it.. i only see

cmd.Parameters.Add(Key)... how does the command know which parameter the value is associated with? some of my queries have dozens of parameters.

internet high-five to anyone with an answer!

You have to use a bit of sugary syntax. I don't know why the ADO.NET guys designed it like that, I guess they were trying to be "hip".

Anyway, if you look at the .Add() method, you'll see it actually returns an IDbParameter object, so you do this:

cmd.Parameters.Add(key).Value = myValue;

Silly, I know, but they made that design choice over 10 years ago, now we have to live with it.

...or better yet: why not define an extension method? (I heard they removed the Oracle client library in .NET4).

nevermind. the Add() takes type DBParameter, not just the value. looks like i have to use the factory to create a new parameter.

additionally, if anyone else ever wants to do this, you have to use DBProviderFactory to create the parameter and in my case, at the very least specify:

p.DbType = DbType.AnsiString
p.Direction = ParameterDirection.Input
p.ParameterName = "Key"

//Note that the parameter symbol is missing here... i guess that makes sense since this is provider agnostic, but your commandText needs to be aware and preface the parameter inline with whichever @ or : is appropriate

p.Value = "whatever"

Remember that System.Data.OracleClient is removed in .NET 4, so you'll want to download Oracle's ODP.NET. The namespace becomes Oracle.DataAccess.Client. (it also performs a lot better than the MS one)