Using Object Types in .NET v4.1.6.1

Suggest edits

The SQL CREATE TYPE command is used to create a user-defined object type, which is stored in the Advanced Server database.

These user-defined types can then be referenced within SPL procedures, SPL functions, and .NET programs.

The basic object type is created with the CREATE TYPE AS OBJECT command along with optional usage of the CREATE TYPE BODY command.

An example that demonstrates using an object type is shown in the following section.

Using an Object Type

To use an object type, you must first create the object type in the Advanced Server database. Object type addr_object_type defines the attributes of an address:

CREATE OR REPLACE TYPE addr_object_type AS OBJECT
(
    street          VARCHAR2(30),
    city            VARCHAR2(20),
    state           CHAR(2),
    zip             NUMBER(5)
);

Object type emp_obj_typ defines the attributes of an employee. Note that one of these attributes is object type ADDR_OBJECT_TYPE as previously described. The object type body contains a method that displays the employee information:

CREATE OR REPLACE TYPE emp_obj_typ AS OBJECT
(
    empno           NUMBER(4),
    ename           VARCHAR2(20),
    addr            ADDR_OBJECT_TYPE,
    MEMBER PROCEDURE display_emp(SELF IN OUT emp_obj_typ)
);

CREATE OR REPLACE TYPE BODY emp_obj_typ AS
  MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_typ)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee No   : ' || SELF.empno);
    DBMS_OUTPUT.PUT_LINE('Name          : ' || SELF.ename);
    DBMS_OUTPUT.PUT_LINE('Street        : ' || SELF.addr.street);
    DBMS_OUTPUT.PUT_LINE('City/State/Zip: ' || SELF.addr.city || ', ' ||
      SELF.addr.state || ' ' || LPAD(SELF.addr.zip,5,'0'));
  END;
END;

The following listing is a complete .NET program that uses these user-defined object types:

namespace ObjectTypesSample
{
    class Program
    {
        static void Main(string[] args)
        {
            EDBConnection.GlobalTypeMapper.MapComposite<addr_object_type>("enterprisedb.addr_object_type");
            EDBConnection.GlobalTypeMapper.MapComposite<emp_obj_typ>("enterprisedb.emp_obj_typ");
            EDBConnection conn = new EDBConnection("Server=localhost;Port=5444;database=test;User ID=enterprisedb;password=<PASSWORD>;");

            try
            {
                conn.Open();

                EDBCommand cmd = new EDBCommand("emp_obj_typ.display_emp", conn);

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                EDBCommandBuilder.DeriveParameters(cmd);

                addr_object_type address = new addr_object_type()
                {
                    street = "123 MAIN STREET",
                    city = "EDISON",
                    state = "NJ",
                    zip = 8817
                };

                emp_obj_typ emp = new emp_obj_typ()
                {
                    empno = 9001,
                    ename = "JONES",
                    addr = address
                };
                cmd.Parameters[0].Value = emp;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                emp_obj_typ empOut = (emp_obj_typ)cmd.Parameters[0].Value;
                Console.WriteLine("Emp No: " + empOut.empno);
                Console.WriteLine("Emp Name: " + empOut.ename);
                Console.WriteLine("Emp Address Street: " + empOut.addr.street);
                Console.WriteLine("Emp Address City: " + empOut.addr.city);
                Console.WriteLine("Emp Address State: " + empOut.addr.state);
                Console.WriteLine("Emp Address Zip: " + empOut.addr.zip);
                Console.WriteLine("Emp No: " + empOut.empno);
            }
            catch (EDBException exp)
            {
                Console.WriteLine(exp.Message.ToString());
            }
            finally
            {
                conn.Close();
            }
        }
    }

    public class addr_object_type
    {
        public string street;
        public string city;
        public string state;
        public decimal zip;
    }

    public class emp_obj_typ
    {
        public decimal empno;
        public string ename;
        public addr_object_type addr;
    }
}

The following .NET types are defined to map to the types in Advanced Server:

public class addr_object_type
{
    public string street;
    public string city;
    public string state;
    public decimal zip;
}

public class emp_obj_typ
{
    public decimal empno;
    public string ename;
    public addr_object_type addr;
}

A call to EDBConnection.GlobalTypeMapper.MapComposite maps the .NET type to the Advanced Server types:

EDBConnection.GlobalTypeMapper.MapComposite<addr_object_type>("enterprisedb.addr_object_type");
  EDBConnection.GlobalTypeMapper.MapComposite<emp_obj_typ>("enterprisedb.emp_obj_typ");

A call to EDBCommandBuilder.DeriveParameters() gets parameter information for a stored procedure. This allows you to just set the parameter values and call the stored procedure:

EDBCommandBuilder.DeriveParameters(cmd);

The value of the parameter is set by creating an object of the .NET type and assigning it to the Value property of the parameter:

addr_object_type address = new addr_object_type()
{
    street = "123 MAIN STREET",
    city = "EDISON",
    state = "NJ",
    zip = 8817
};

emp_obj_typ emp = new emp_obj_typ()
{
    empno = 9001,
    ename = "JONES",
    addr = address
};
cmd.Parameters[0].Value = emp;

A call to cmd.ExecuteNonQuery() executes the call to the display_emp() method:

cmd.ExecuteNonQuery();