Solved

ASP.NET connection to Oracle: What provider should I use?

Posted on 2011-03-15
11
555 Views
Last Modified: 2012-06-27
Hi, I'm trying to connect to oracle using asp.net vb.net. I've done that ok using the provider 'Provider=OraOLEDB.Oracle'. But, I'm running into problems with CLOB datatypes and how to enter them... so on further investigation, people are talking about ODP.NET dataprovider and this seems to be the one that people are using.

So, firstly, what is the OraOLEDB.Oracle provider. Is this deprecated? It is (or was) provided by microsoft is that right? Are there issues with using this provider? Or should I be going with ODP.NET and why?
0
Comment
Question by:AidenA
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 35136909
Take a look at this page:
http://www.connectionstrings.com/oracle
0
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 35136925
Use System.Data.OracleClient Namespace
0
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 35136934
Here is sample code:
 
public void ReadOracleTypesExample(string connectionString)
   {
   OracleConnection myConnection = 
      new OracleConnection(connectionString);
   myConnection.Open();
   OracleCommand myCommand = myConnection.CreateCommand();

   try
      {
      myCommand.CommandText = "SELECT * from OracleTypesTable";
      OracleDataReader oracledatareader1 = myCommand.ExecuteReader();
      oracledatareader1.Read();

      //Using the oracle specific getters for each type is faster than
      //using GetOracleValue.

      //First column, MyVarchar2, is a VARCHAR2 data type in Oracle
      //Server and maps to OracleString.
      OracleString oraclestring1 = 
        oracledatareader1.GetOracleString(0);
      Console.WriteLine("OracleString " + oraclestring1.ToString());

      //Second column, MyNumber, is a NUMBER data type in Oracle Server
      //and maps to OracleNumber.
      OracleNumber oraclenumber1 = 
        oracledatareader1.GetOracleNumber(1);
      Console.WriteLine("OracleNumber " + oraclenumber1.ToString());

      //Third column, MyDate, is a DATA data type in Oracle Server
      //and maps to OracleDateTime.
      OracleDateTime oracledatetime1 = 
        oracledatareader1.GetOracleDateTime(2);
      Console.WriteLine("OracleDateTime " + oracledatetime1.ToString());

      //Fourth column, MyRaw, is a RAW data type in Oracle Server and
      //maps to OracleBinary.
      OracleBinary oraclebinary1 = 
        oracledatareader1.GetOracleBinary(3);
      //Calling value on a null OracleBinary throws
      //OracleNullValueException; therefore, check for a null value.
      if (oraclebinary1.IsNull==false)
      {
         foreach(byte b in oraclebinary1.Value)
         {
            Console.WriteLine("byte " + b.ToString());
         }
      }
      oracledatareader1.Close();
   }
   catch(Exception e)
   {
       Console.WriteLine(e.ToString());
   }
   finally
   {
       myConnection.Close();
   }
}

Open in new window

0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:AidenA
ID: 35137018
thanks but i'm not sure that really claifies it for me. So, what about OraOLEDB.Oracle? What is that exactly, is it deprecated, and should I be using ODP.NET?
0
 

Author Comment

by:AidenA
ID: 35137067
OracleClient namespace looks like it might be deprecated anyway, based on this article

http://reddevnews.com/articles/2009/06/16/microsoft-kills-oracle-data-provider-for-adonet.aspx

it's available but deprecated. that's what i was reading before... microsoft says use a third party provider, which is where ODP.NET comes in?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35137193
>>and should I be using ODP.NET?

Yes.  I strongly encourage you to use ODP.Net.  Microsoft is even deprecating Oracle drivers.

You can download it from:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

It comes with sample code to do just about everything.  Even CLOB examples.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35137209
You need to be careful or this will quickly turn into a duplicate of:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_26885362.html

As soon as you ask: How do I insert a CLOB, it will and will have to be deleted.
0
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 35137272
Microsoft never says use Third party providers :)
Anyway, Oracle Data Provider for .NET (ODP.NET) features optimized ADO.NET data access to the Oracle database. ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, XML DB, and advanced security. The data provider can be used with the latest .NET Framework 4 version.

Details here: http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

So if you want to use any feature from this provider by Oracle, use it else implementation by Microsoft is good enough.

I hope this helps.
Asim
0
 

Author Comment

by:AidenA
ID: 35137471
Ok so if i'm getting this, then system.data.oracleclient is not a data provider right, it's just a dll of methods that helps you deal with Oracle using .NET. And that is being deprecated. So, probably I should move to ODP.NET... although I've read bad things about this? People saying system.data.oracleclient is much better? By the way, I'll have to use Oracle 10g so will have to download that specific version I guess?

So, the data provider I'm using, OraOLEDB.Oracle, is not an issue here, it's not currently deprecated. Just the oracleclient namespace? This is getting confusing. So what is that exactly? If I download ODP.NET... am i using this instead of the OraOLEDB.Oracle dataprovider AND the oracleclient namespace?

0
 
LVL 10

Accepted Solution

by:
Asim Nazir earned 250 total points
ID: 35137650
Yes! You are right. That ADO.Net and Oracle implementation exists in .Net 4.0 but it will be removed in future releases. This is what Microsoft says:

"The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider." at http://msdn.microsoft.com/en-us/library/77d8yct7.aspx

So you can download latest ODP.NET and that will work with 10g.
Now don't get confused, forget about oracleCLient namespace and start working with ODP.Net.

Asim
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 35138100
>>move to ODP.NET... although I've read bad things about this

The only people that would say bad things about it are MSoft people.  I've used ODP.Net since it was introduced and haven't had many problems with it.

Granted it isn't as tightly integrated with Visual Studio as say, SQL Server access, but would you really expect it to be?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question