Connection to Oracle

Rick Nash posted on Thursday, December 13, 2007

I have been asked to conevert our solution from SQL Server to Oracle 10g. I have converted the database but I now need to get CSLA to connect to it. The settings I have in the App.Config is:

<add key="Authentication" value="PrimeWorks" />
<add key="DB:PrimeWorks" value="data source=DevServer;initial catalog=PrimeWorks;user id=pwn_db;password=XYZ" />
<add key="DB:Security" value="data source=DevServer;initial catalog=PrimeWorks;user id=pwn_db;password=XYZ" />

Can anyone help?

PS I don't really know much about CSLA as the code was written by someone else.

ajj3085 replied on Thursday, December 13, 2007

You'll have to look in the DataPortal_xxx methods, and see what they are using to do data access.  If they're using SqlCommand, SqlDataReader, etc. you have a bit of work to do.

Mainly though, it will involve changing SqlCommand to IDataCommand and such.  Or OracleCommand if you're moving to Oracle and leaving MS Sql. 

Note that changing database's should affect any code outside the DataPortal_xxx methods and  your UI should work the same as well. 

Rick Nash replied on Thursday, December 13, 2007

Thanks for the prompt reply.

Having never connected to an Oracle database before, I am SQL Server kind of guy, this is new territory for me.

We have clients that use SQL Server and some who use Oracle, I was hoping that ADO would hide the actual database type and we could have only one C# Codebase. Is this achieavable?

Does the connection string:

<add key="Authentication" value="PrimeWorks" />

<add key="DB:PrimeWorks" value="data source=DevServer;initial catalog=PrimeWorks;user id=pwn_db;password=XYZ" />

<add key="DB:Security" value="data source=DevServer;initial catalog=PrimeWorks;user id=pwn_db;password=XYZ" />

in the App.Config remain the same?

 

mr_lasseter replied on Thursday, December 13, 2007

Rick Nash:

We have clients that use SQL Server and some who use Oracle, I was hoping that ADO would hide the actual database type and we could have only one C# Codebase. Is this achieavable?

It really depends on how it was written.  Is it using stored procedures or sql statements?  Was it using a ConnectionManager class of sorts to abstract the database?  Does it use SqlClient.SqlCommand or does it work with the IDbCommand interfaces?  I am guessing if this wasn't thought about when the application was originally written my guess would be no. 

In my applications, I have written a ConnectionManager dll that abstracts what type of database the application is talking to.  So it is possible.

Rick Nash replied on Thursday, December 13, 2007

Luckily its all stored procs but I guess some kind of connection manager is required, as per you suggestion. Its going to be a real pain and not very interesting.

JoeFallon1 replied on Thursday, December 13, 2007

Rick,

I supported both SQL Server and Oracle for many years.

I added a DBType to my config file so that I could tell which one I was supposed to use. Then I used the correct connection string:
<!-- <add key="DBType" value="Oracle" /> -->
<!-- <add key="ConnectionString" value="data source=myOracleDB;uid=myUser;password=myPwd" /> -->

<add key="DBType" value="SQL Server"/>
<add key="ConnectionString" value="data source=myServer;database=myDB;uid=myUser;password=myPwd;connection timeout=50" />

My ADO.Net code was abstracted to use the interfaces like IDbConnection and IDataReader.

When the app starts it reads the config file and then knows which DBType to use.

There is code to branch based on DBType so it creates the correct object.

e.g. It creates a SqlConnection when DBType is SQL Server and creates an OracleConnection when DDBType is Oracle. The method returns an IDbConnection which can hold either of the 2 concrete objects.

I dislike Oracle very much. I jumped for joy when told this year that we can stop supporting it.

You are fairly lucky that you are on a newer version becasue at least the SQL commands are 98% the same as SQL Server. Oracle did not support JOIN syntax until recently so you had to use older style operators.

Oracle Stored Procedures are nothing like SQL Server SPs. I took me whole lot of digging to figure out how to return a result set from an Oracle SP. Hint: you don't just call the SP.

There are other quirks that you will likely run into. As I recall Oracle DB tables do not support Bit fields so your True/False code could be an issue. I tweaked stuff to support this.

If you get the whole thing done in 3 weeks you deserve major kudos. I would have said 3 months.

Joe

Rick Nash replied on Thursday, December 13, 2007

mr_lasseter replied on Thursday, December 13, 2007

Your biggest problem is going to be getting the Recordsets out of the oracle procedures.  You are going to have to create a refcursor in order to get the record set out of the Oracle procedure.  When you do this the refCursor is going to be a parameter when calling the stored procedure, this is going to give you fits if you want to use the same class for Sql Server and Oracle.  This is why I created a ConnectionManager to abstract adding the refcursors to the list of parameters of the stored procedure.  I have attached some code snippets that should help show you what I am doing.  Hopefully it will help.

 

          

Rick Nash replied on Thursday, December 13, 2007

mr_lasseter replied on Thursday, December 13, 2007

The attachment appears for me.  Let me try it again.

 

Mike

Rick Nash replied on Thursday, December 13, 2007

ajj3085 replied on Thursday, December 13, 2007

I'd think that Oracle would require a different connection string.

Ado.Net can hide the actual database, but you have to stick to using interfaces, IDataCommand instead of OracaleCommand or SqlCommand.  You'll have to dig into the code, the connection strings won't help you figure out what you need to do.

mr_lasseter replied on Thursday, December 13, 2007

First, your conneciton string is not correct.  Data Source should be the name of the database, there is no such thing as initial catalog.

Other question, can you connect to the database using sqlplus?  If not then you are going to need the Oracle client installed on your machine and the tnsnames.ora file setup correctly. 

 

Rick Nash replied on Thursday, December 13, 2007

I have the database ported to Oracle and I can run SQL Plus, its not as easy to use as SQL Server.

I have also routed through the code and I can see that I need some #defines

#if ORACLE

#if SQL Server

I managed to get this info as well

http://www.connectionstrings.com/?carrier=oracle

I think my initial estimate of 3 weeks may not be correct, I am sure this was much easier back in the bad old days of ODBC and ADO (not ADO.Net)!!