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

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?
AidenAAsked:
Who is Participating?
 
Asim NazirCommented:
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
 
tiagosalgadoCommented:
Take a look at this page:
http://www.connectionstrings.com/oracle
0
 
Asim NazirCommented:
Use System.Data.OracleClient Namespace
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Asim NazirCommented:
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
 
AidenAAuthor Commented:
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
 
AidenAAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
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
 
Asim NazirCommented:
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
 
AidenAAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.