How to Integrate Oracle With .NET Framework

Background

Basically ASP.NET C# Web applications and websites use Microsoft SQL Server as a database. But in some companies and organizations there may be a case that they are using .NET with Oracle. Developers have problems finding the solutions for accessing Oracle databases.

So, here is a quick guide for beginners.

Introduction

In this article I would like to explain how to integrate Oracle with the .NET Framework and provide support for versioning issues that often occur when deploying projects on IIS.

Let's start

• Prerequisits
  
  
  • First ensure that you have the Oracle Database Client.
    
    
  • You are aware of creating connections, tables and SPs in Oracle.
    
    
• Check for Oracle assemblies in the GAC
  
  
  • Run -> assembly
    
    
  • Look for Oracle.DataAccess in Assemblies.
    
    
  • If not present then download Oracle ODAC from the Oracle site depending on your OS version.
    
    
• Create a database in Oracle and add a sample table to contain some data.
  
  
• Write a Stored Procedure in Oracle for fetching data from the sample table.
  
  
• Now let's start C# coding.
  
  
  • Create a new blank web project in Visual Studio. (I am using VS12 and specifying the procedure accordingly.)
    
    
  • Add a Web form say Default.aspx
    
    
  • Add a GridView to it. (For displaying the fetched data.)
    
    
  • Go to the Add reference screen and add an Oracle.DataAccess reference using the following procedure:
    
    
  Right-click on References under the project in Visual Studio
  
  Go to Add references.
  
  The window for adding references will open.
  
  On the left hand side panel go to Extensions and search for Oracle.DataAccess.
  
  Check the checkbox in front of it and press OK.
  
  
  • Now everything you need to do has been created for an OracleConnection and utilize it by means of Adapter and OracleCommand.
    
    Note: You can apply your Microsoft SQL to .NET integration knowledge here. And can implement the code depending on the implementation of Microsoft SQL integration.
    
    
  • We have some special things in the implementation of this integration.
  
  The first is the parameter format of the OracleCommand parameter.
  
  You need to specify the parameter direction (in other words Input, Output, InputOutput) and datatype.
  
  When implementing a DML Select command in an Oracle procedure, the result returned should be in a Cursor.
  
  Hence a special output parameter of type RefCursor should be there in the parameter list.

  Special Note: The sequence of the parameters used does matter. The parameters should be in the same sequence that they are declared in the Oracle SP.

For reference I am attaching sample code. Please check it by adding a valid connection string in the Web.config and valid SP with its subsequent parameters.

Conclusion

Finally I am concluding here with this topic. Though this subject is very vast, we cannot cover it in a single article.

Please reply your feedbacks so that I can plan my next article accordingly.

Thank you.