Solved

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

Posted on 2011-03-15
11
551 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

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 76

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 76

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now