Interaction between C# application and Oracle through custom Object

Introduction

As a developer I am very much fond of OOPS and its implementation in C#. When we talk about OOPS most of us are quite comfortable to play with custom object (user defined object) and transporting them across different application layer. The real pain comes when we plan to send or retrieve custom object to/from database. More specifically this is really being a challenging task to achieve database communication through C# entity.

In my career I mostly work with Oracle and C#. As we know both of this platform are object oriented so I decided let’s put into practice the OOPS approach for DB communication.

After a long struggle and digging into various available options I found ODP.Net allows interaction to database in terms object passing.

Here in this example I referred to ODP.NET (Oracle Data Provider for .Net, Release – 11.1), Oracle 10g and Visual Studio 2008.

ODP.NET is freely available and one can download the executable from oracle site.

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

Below I am going to discuss the implementation steps in detail.

Send custom object to Oracle store procedure:

//Person Entity – C# Custom Object
PersonBO objPersonBO = new PersonBO();
objPersonBO.Address = "Kolkata";
objPersonBO.Age = 20;
objPersonBO.Name = "Mr.Jhon";

//------ Establish the connection with Oracle-----///

//Insert the Person object into database table
OracleCommand cmd = new OracleCommand("ODP_RND_InsertPerson_Proc", objCon);
cmd.CommandType = CommandType.StoredProcedure; //Database store procedure

//Oracle Paramater
OracleParameter objParam = new OracleParameter();

//Denotes, we are going to pass a custom object
objParam.OracleDbType = OracleDbType.Object;

objParam.Direction = ParameterDirection.Input;

//Note: The UdtTypeName is case-senstive - Should be in upper case
//This is a database object and physically exists in the database as custom // type
objParam.UdtTypeName = "ODP_RND_PERSON_TYPE";

//Attach the C# custom object as input parameter
objParam.Value = objPersonBO;

Read more: Codeproject

Posted via email from .NET Info