Interaction between C# Application and Oracle through Custom Object

转载 2012年03月29日 11:22:02


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 transport 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 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 these platforms are object oriented so I decided to 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 at

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

Send Custom Object to Oracle Stored 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 Parameter
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-sensitive - 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;

//Attach parameter to command object

//Insert the UDT into the table

From the above code snippet, we come across a new keyword “UdtTypeName” which refers to Oracle user type. We will explain this later on in the discussion.

Receive Data as Custom Object from Oracle Store Procedure

This requires few steps to fetch the data from database.

//SQL statement
string strSql = "SELECT FROM odp_rnd_person_table c"

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

//Pass the SQL statement
OracleCommand objCmd 		= new OracleCommand(strSql, objCon);
objCmd.CommandType 		= CommandType.Text;

//Issue the statement
OracleDataReader objReader 	= objCmd.ExecuteReader();

//Fetch each row
while (objReader.Read())
      //Custom object
      PersonBO objPersonBO 	= new PersonBO();

       //Fetch the objects as a custom type
      objPersonBO 		= (PersonBO)objReader.GetValue(0);

We are done with data exchange between C# and Oracle which requires nominal steps to be performed. More interesting part we are going to discuss is the custom object creation.

Namespaces are required:

using System;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Xml.Serialization;
using System.Xml.Schema;

Create the custom class and make it derive from IOracleCustomTypeIOracleCustomType is an interface for conversion between C# Custom Type and an Oracle Object Type.

public class PersonBO :  IOracleCustomType

Create the following public property underneath PersonBO and make it decorated byOracleObjectMappingAttribute.The OracleObjectMappingAttribute needs to be specified on each members of custom type that represent the Oracle object type. This attribute must specify the name or zero-based index of the attribute in the Oracle object that the custom class property maps to. This also allows the custom type to declare field or property names which differ from the Oracle Object type.

 public virtual string Name{get;set;}
 public virtual string Address{get;set;}
 public virtual decimal Age { get; set; }

Create the following method FromCustomObject underneath PersonBO and override it. This interface method creates an Oracle Object by setting the attribute respectively on the specified Oracle UDT.

public virtual void FromCustomObject(OracleConnection objCon, IntPtr objUdt)
    //The FromCustomObject method is used to build an Oracle Object or   
    //Collection from a custom object by 
    //setting attribute or element values respectively through the 
    //OracleUdt.SetValue method.

    OracleUdt.SetValue(objCon, objUdt, "PNAME", this.Name);
    OracleUdt.SetValue(objCon, objUdt, "ADDRESS", this.Address);
    if (this.Age > 0) OracleUdt.SetValue(objCon, objUdt, "AGE", this.Age);

Create the following method ToCustomObject underneath PersonBO and override it. It provides the Oracle Object with the attribute values to set on the custom type. This interface method initializes a custom object using the specified Oracle UDT.

public virtual void ToCustomObject(OracleConnection objCon, IntPtr objUdt)
   //The ToCustomObject method is used to initialize a custom object from the 
   //specified Oracle 
   //Object or Collection by retrieving attribute or element values 
   //respectively through the OracleUdt.GetValue method.
    this.Name    = ((string)(OracleUdt.GetValue(objCon, objUdt, "PNAME")));
    this.Address = ((string)(OracleUdt.GetValue(objCon, objUdt, "ADDRESS")));

    bool AgeIsNull = OracleUdt.IsDBNull(objCon, objUdt, "AGE");
    if ((AgeIsNull == false)) this.Age = 
		((decimal)(OracleUdt.GetValue(objCon, objUdt, "AGE")));

Prepare the Database Object


The field CONTACT in the above script is type of ODP_RND_PERSON_TYPE which is an Oracle user defined type.

CREATE OR REPLACE type ODP_RND_Person_Type as object 
     pname varchar2(30), 
     address varchar2(60), 
     age number(3)

Here, we must remember the structure of C# and Oracle user define type should be identically same.

Here is the DB stored procedure for inserting data into table. This procedure accepts the above type as input parameter. This type encapsulates the actual value passed from UI level.

Within the procedure, if we want to access the value of individual property, we can do it in the following way:

Person.pnameperson.address, etc.

 ODP_RND_InsertPerson_Proc(person IN ODP_RND_Person_Type) as
    Insert into ODP_RND_Person_Table values (person); 

!! Enjoy coding !!

【python】RuntimeError: Lock objects should only be shared between processes through inheritance

在使用multiprocessing模块时,使用了同步锁Lock,结果出现了如下错误: RuntimeError: Lock objects should only be shared betwee...
  • moxiaomomo
  • moxiaomomo
  • 2013年09月09日 10:15
  • 4627

Learning Through Dialogue Interactions——基于对话交互的学习

原文:Learning Through Dialogue Interactions 作者: JiweiLi,AlexanderH.Miller,SumitChopra,Marc’AurelioRa...
  • liuyuemaicha
  • liuyuemaicha
  • 2016年12月26日 23:51
  • 1904

sql中的 between and 用法

BETWEEN 指定测试范围。 语法 test_expression [ NOT ] BETWEEN begin_expression AND end_expression 参数 test_...
  • oppo62258801
  • oppo62258801
  • 2018年01月21日 11:50
  • 74


oracle between 测试环境: Oracle Database 10g Enterprise Edition/SQL*Plus 遇到一个要使用between...
  • u012881151
  • u012881151
  • 2016年09月22日 17:30
  • 10500

Oracle BETWEEN 条件

-- Start 好多人搞不清楚 BETWEEN 条件到底包不包含临界值,呵呵,今天我负责任的告诉你,下面的语句是等价的. x BETWEEN 1 AND 10 x >=1 AND x ...
  • shangboerds
  • shangboerds
  • 2015年02月26日 18:53
  • 1999

oracle 的日期 between and 用法

//程序: //介绍: //如果downloadDateBegin 和 downloadDateEnd 日期不为空 //格式化日期 if(downloadDateBegin != null && ...
  • huaishuming
  • huaishuming
  • 2013年11月01日 18:59
  • 12000

关于 oracle between and的用法!

鸣谢: oracle date等类型是带时间部分的,所以你这个2...
  • Hollboy
  • Hollboy
  • 2013年06月27日 15:56
  • 8095

There is a circular dependency between XMPPFramework/Core and XMPPFramework/Authentication

执行"pod install" 或者 "pod update" , xmppframework 会出现循环依赖问题。 There is a circular dependency betwe...
  • CB1234CB
  • CB1234CB
  • 2015年06月29日 09:41
  • 893

oracle between and 边界问题

oracle between and 边界问题,对于日期类型的to_date的格式进行了详细介绍
  • h249059945
  • h249059945
  • 2015年07月31日 14:05
  • 14545

oracle 中BETWEEN命令

IN 这个指令可以让我们依照一或数个不连续 (discrete) 的值的限制之内抓出资料库中的 值,而 BETWEEN 则是让我们可以运用一个范围 (range) 内抓出资料库中的值。 BE...
  • ZHOU452840622
  • ZHOU452840622
  • 2015年07月05日 11:11
  • 834
您举报文章:Interaction between C# Application and Oracle through Custom Object