Interaction between C# Application and Oracle through Custom Object
来源:互联网 发布:qq苹果手机在线软件 编辑:程序博客网 时间:2024/05/18 00:21
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 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
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 Stored Procedure
//Person Entity – C# Custom ObjectPersonBO objPersonBO = new PersonBO();objPersonBO.Address = "Kolkata";objPersonBO.Age = 20;objPersonBO.Name = "Mr.Jhon";//------ Establish the connection with Oracle-----/////Insert the Person object into database tableOracleCommand cmd = new OracleCommand("ODP_RND_InsertPerson_Proc", objCon);cmd.CommandType = CommandType.StoredProcedure; //Database store procedure//Oracle ParameterOracleParameter objParam = new OracleParameter();//Denotes, we are going to pass a custom objectobjParam.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 // typeobjParam.UdtTypeName = "ODP_RND_PERSON_TYPE";//Attach the C# custom object as input parameterobjParam.Value = objPersonBO;//Attach parameter to command objectcmd.Parameters.Add(objParam);//Insert the UDT into the tablecmd.ExecuteNonQuery();
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 statementstring strSql = "SELECT c.contact FROM odp_rnd_person_table c"//------ Establish the connection with Oracle-----/////Pass the SQL statementOracleCommand objCmd = new OracleCommand(strSql, objCon);objCmd.CommandType = CommandType.Text;//Issue the statementOracleDataReader objReader = objCmd.ExecuteReader();//Fetch each rowwhile (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 IOracleCustomType
. IOracleCustomType
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.
[OracleObjectMappingAttribute("PNAME")] public virtual string Name{get;set;}
[OracleObjectMappingAttribute("ADDRESS")] public virtual string Address{get;set;}
[OracleObjectMappingAttribute("AGE")] 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
CREATE TABLE ODP_RND_PERSON_TABLE( CONTACT ODP_RND_PERSON_TYPE)
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)) NOT FINAL
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.pname
, person.address
, etc.
CREATE OR REPLACE procedure ODP_RND_InsertPerson_Proc(person IN ODP_RND_Person_Type) as begin Insert into ODP_RND_Person_Table values (person); end
!! Enjoy coding !!
- Interaction between C# Application and Oracle through Custom Object
- 5.4. Interaction Between Devices and Kernel
- Conversion between json and object
- oracle between and 边界问题
- Oracle between and 边界问题
- Difference between Object.keys() and Object.getOwnPropertyNames()
- The Difference between write through and write back for cache
- Difference between application/xml and text/xml
- Difference between application/xml and text/xml
- difference between string literal and string object
- What is the difference between User Control and Custom Control?
- oracle 不要乱用between and
- Interaction Between the Data Guard Broker and a Data Guard Configuration [ID 249703.1]
- Interaction between services and applications at user level in Windows Vista
- TCP Performance problems caused by interaction between Nagle's Algorithm and Delayed ACK
- Interaction Between the Data Guard Broker and a Data Guard Configuration [ID 249703.1]
- Object-Oriented Event Listening through Partial Application in JavaScript
- Firewalls and NAT Interaction
- 通过ContentResolver调用系统URI实现的通讯录示例
- android 的TextView空间动态设置字体颜色
- Java 多播套接字
- Java字符串与整数之间的互相转换
- Apache2.4.1安装手册
- Interaction between C# Application and Oracle through Custom Object
- 第三章:字符设备驱动程序
- 通常也是32位的HTML5
- Linux Notifier Chains
- mysql5安装手册
- Vim 快捷键整理
- camera isp
- php5环境安装
- 表达式字符串转化为后缀表达式格式