Solved

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

Posted on 2011-03-15
11
554 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retreiving column names in Windows but not in Unix 11 47
SQL LINE CONTINUATION ISSUE 12 33
Reading the web config for a running service on Windows 10 16 41
asp enable view state 1 24
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

777 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