Developer Geeks Home

Working with User Defined Type (Oracle UDT) and Custom Type using ODP .Net 11g

RSS
 

  • Posted 3 years ago
  • Category: .Net Framework 3.5, .Net Framework 4.0, ODP .Net, Oracle,
  • Audiences: Architect, Developer, System Analyst,

This article provides you with the sample source code showing that how to write User Defined Types (Oracle UDT) and .Net Custom types using ODP .Net 11g. It also demonstrates how to provide schema and UDT type names in configuration file.

Following are Customer_Type and Customer_Table .Net custom types. Customer_Table type represesnts a collection/array of Customer_Type types.

You could copy these types and change them as per your UDT type requirements. Or you could auto-generate these custom types using ODP .Net “Generate Custom Class" wizard. To see how to auto-generate these custom types using ODP .Net “Generate Custom Class" wizard, refer: How to auto-generate .net custom types using ODP .Net and VS2008.

using System;
using Oracle.DataAccess.Types;

namespace UDT_Sample_Liabrary
{
    public class Customer_Type : IOracleCustomType
    {
        public Customer_Type()
        {
            // TODO : Add code to initialise the object
        }

        [OracleObjectMappingAttribute("CUSTOMER_ID")]
        public int CustomerId { get; set; }

        [OracleObjectMappingAttribute("CUSTOMER_NAME")]
        public string CustomerName { get; set; }

        #region IOracleCustomType Members

        public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
        {
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CUSTOMER_ID", this.CustomerId);

            if (this.CustomerName != null)
            {
                Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CUSTOMER_NAME", this.CustomerName);
            }
        }

        public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
        {
            if (!Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "CUSTOMER_ID"))
            {
                this.CustomerId = ((int)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "CUSTOMER_ID")));
            }

            if (!Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "CUSTOMER_NAME"))
            {
                this.CustomerName = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "CUSTOMER_NAME")));
            }
        }

        #endregion
    }
        
    //[OracleCustomTypeMappingAttribute("CUSTOMER_DB_SCHEMA.CUSTOMER_TYPE")] 
    //Remove this class attribute and add custom type mapping to Config file.
    public class Customer_TypeFactory : IOracleCustomTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            Customer_Type obj = new Customer_Type();
            return obj;
        }
    }
}

Customer_Table is a collection is Customer_Type types:

using System;
using Oracle.DataAccess.Types;

namespace UDT_Sample_Liabrary
{
    public class Customer_Table : IOracleCustomType
    {
        public Customer_Table()
        {
            // TODO : Add code to initialise the object
        }

        [OracleArrayMappingAttribute()]
        public virtual Customer_Type[] Value { get; set; }

        public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, this.Value);
        }

        public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
        {
            this.Value = ((Customer_Type[])(OracleUdt.GetValue(con, pUdt, 0)));
        }
    }

    //[OracleCustomTypeMappingAttribute("CUSTOMER_DB_SCHEMA.CUSTOMER_TABLE")]
    //Remove this class attribute and add custom type mapping to Config file.
    public class Customer_TableFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            Customer_Table obj = new Customer_Table();
            return obj;
        }

        public virtual System.Array CreateArray(int length)
        {
            Customer_Type[] collElem = new Customer_Type[length];
            return collElem;
        }

        public virtual System.Array CreateStatusArray(int length)
        {
            return null;
        }
    }
}

Following configuration file will have the custom type mappings for above created types:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <sectionGroup name="oracle.dataaccess.client" 
                type="Oracle.DataAccess.Client.OracleSectionGroup, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342">
    <section name="settings" 
             type="Oracle.DataAccess.Client.SettingsSection, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    <section name="udtCustomTypeMappings" 
             type="Oracle.DataAccess.Client.UdtCustomTypeMappingsSection, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
  </sectionGroup>
  <oracle.dataaccess.client>
    <settings>
      <add name="Customer_Type" value="udtMapping factoryName='UDT_Sample_Liabrary.Customer_TypeFactory,
          UDT_Sample_Liabrary' typeName='CUSTOMER_TYPE' schemaName='CUSTOMER_DB_SCHEMA'"/>
      
      <add name="Customer_Table" value="udtMapping factoryName='UDT_Sample_Liabrary.Customer_TableFactory,
          UDT_Sample_Liabrary' typeName='CUSTOMER_TABLE' schemaName='CUSTOMER_DB_SCHEMA'"/>
    </settings>
  </oracle.dataaccess.client>
</configuration>

Following is the oracle stored procedure that takes in the CUSTOMER_TABLE type as input parameter, loop through it and then inserts the records in Customers table:

PROCEDURE insert_customers (i_customer_array in customer_table,
                            o_new_id out integer) IS
BEGIN

-- loop through the customers
   for i in 1..i_customer_array.count loop

INSERT
          INTO Customers (customer_id,
			   Customer_name)
	VALUES (i_customer_array(i).CustomerId,
		i_customer_array(i).CustomerName);

   end loop;
END

Following sample code demonstrates how to call the above created stored procedure with custom types, from .Net enviroment:

      Customer_Table cust_table = new Customer_Table
            {
                Value = new List<Customer_Type>
                        {
                            new Customer_Type { CustomerId= 0, CustomerName = "John Trivolta" },
                            new Customer_Type { CustomerId= 0, CustomerName = "Mark Robinson" }
                        }.ToArray()
            };
  
      OracleConnection conn = new OracleConnection("User Id=user;Password=pwd;Data Source=datasource");   
      OracleCommand cmd = new OracleCommand();   
  
      cmd.Connection = conn;   
      cmd.CommandType = CommandType.StoredProcedure;   
      cmd.CommandText = "INSERT_CUSTOMERS";   
  
      // First, lets send this UDT Object type as an input parameter to the stored proc, for storage.   
      OracleParameter p1 = new OracleParameter();   
      p1.ParameterName = "I_CUSTOMER_ARRAY";   
      p1.OracleDbType = OracleDbType.Object;   
      p1.UdtTypeName = "CUSTOMER_TABLE";   
      p1.Direction = ParameterDirection.Input;   
      p1.Value = cust_table;   
      cmd.Parameters.Add(p1);   
  
      OracleParameter p2 = new OracleParameter();   
      p2.ParameterName = "O_NEW_ID";   
      p2.Direction = ParameterDirection.Output;
      cmd.Parameters.Add(p2);   
  
      conn.Open();   
      cmd.ExecuteNonQuery();   
      int id = cmd.Parameters["O_NEW_ID"].Value;   
comments powered by Developer Geeks

My Recent Articles [86]