Using object types in .NET v7.0.6.2

Suggest edits

The SQL CREATE TYPE command creates a user-defined object type, which is stored in the EDB Postgres Advanced Server database. You can then reference these user-defined types in SPL procedures, SPL functions, and .NET programs.

Create the basic object type with the CREATE TYPE AS OBJECT command. Optionally, use the CREATE TYPE BODY command.

Using an object type

To use an object type, you must first create the object type in the EDB Postgres 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. 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;

This example is a complete .NET program that uses these user-defined object types:

using EnterpriseDB.EDBClient;
using System.Data.Common;
namespace TypesTest
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            var connString = "Server=localhost;Port=5444;database=edb;User ID=enterprisedb;password=edb;";
            var dataSourceBuilder = new EDBDataSourceBuilder(connString);
            dataSourceBuilder.MapComposite<addr_object_type>("enterprisedb.addr_object_type");
            dataSourceBuilder.MapComposite<emp_obj_typ>("enterprisedb.emp_obj_typ");
            await using var dataSource = dataSourceBuilder.Build();
            await using var conn = await dataSource.OpenConnectionAsync();
            try
            {
                var address = new addr_object_type()
                {
                    street = "123 MAIN STREET",
                    city = "EDISON",
                    state = "NJ",
                    zip = 8817
                };
                var emp = new emp_obj_typ()
                {
                    empno = 9001,
                    ename = "JONES",
                    addr = address
                };
                await using (var cmd = new EDBCommand("emp_obj_typ.display_emp", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    EDBCommandBuilder.DeriveParameters(cmd);
                    cmd.Parameters[0].Value = emp;
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();
                    var 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 EDB Postgres 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 EDBDataSourceBuilder.MapComposite maps the .NET type to the EDB Postgres Advanced Server types:

dataSourceBuilder.MapComposite<addr_object_type>("enterprisedb.addr_object_type");
dataSourceBuilder.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);

Set the value of the parameter 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();