Working With Objects Using dotConnect for Oracle

dotConnect for Oracle allows you to query and update columns of Oracle object type. You can refer to object attributes, fill and update dataset by data from the table that contains objects. Note that attributes of the object will be represented as table columns.

 

For example we have such types and table

CREATE TYPE TAddress AS OBJECT (
  Country VARCHAR2(30),
  City VARCHAR2(30),
  Street VARCHAR2(30),
  Apartment NUMBER
);

CREATE TYPE TPerson AS OBJECT (
  Name VARCHAR2(30),
  Address TAddress,
  Phone VARCHAR2(20),
  BirthDate DATE
);

CREATE TABLE EmpObject (
  Person TPerson,
  Job VARCHAR2(9),
  HireDate DATE,
  Sal NUMBER(7,2)
);

If you execute following query

SELECT * FROM EmpObject

and fill dataset using data adapter, you can apply for table columns as,

[C#]

DataTable employee = DataSet.Tables["Table"];
return employee["PERSON.NAME"];

[Visual Basic]

Dim employee as DataTable = DataSet.Tables["Table"]
return employee("PERSON.NAME")

Using OracleObject Class

The another way to work with objects is using OracleObject class. You can get an instance of OracleObject by one of following ways:

• execute a query that returns an object as a field (OracleDataReader.GetObject(int i));
• return object instance as a parameter, note that type of the parameter (DbType) will be OracleDbType.Object;
• get object reference (OracleRef), execute pin of the object and return OracleObject (GetObject());
• an attribute of the object can also be an object.

For example, after the execution of the query given above, you get a data reader. And you can write the next code:

[C#]

OracleObject obj = datareader.GetOracleObject(i);
return obj["PERSON.NAME"];

[Visual Basic]

Dim obj as OracleObject = datareader.GetOracleObject(i)
return obj("PERSON.NAME")

Using SQLInsert Property

Also dotConnect for Oracle supports object parameters. Use this feature when you write statements to update dataset rows. So to insert a new row in EmpObject table it is enough to assign this statement to SQLInsert property.

INSERT INTO EmpObject
  (PERSON, JOB, HIREDATE, SAL)
VALUES
  (:PERSON, :JOB, : HIREDATE, :SAL)

To execute this INSERT statement by alone OracleCommand component use OracleParameter.Value property to set attributes value of PERSON parameter.

Before you should assign OracleDbType.Object to OracleParameter.DbType property.

For example:

[C#]

OracleCommand cmd = OracleConnection1.CreateCommand();
cmd.CommandText = "INSERT INTO EmpObject " +
    "(PERSON, JOB, HIREDATE, SAL) VALUES (:PERSON, :JOB, : HIREDATE, :SAL)";
...
OracleObject person = new OracleObject("SCOTT.TPERSON", OracleConnection1);
person["NAME"] = "SCOTT";
person["ADDRESS.CITY"] = "NEW YORK";
...
cmd.Parameters["PERSON"].DbType = OracleDbType.Object;
cmd.Parameters["PERSON"].Value = person;
...
cmd.ExecuteNonQuery();

[Visual Basic]

dim cmd as OracleCommand = OracleConnection1.CreateCommand()
cmd.CommandText = "INSERT INTO EmpObject & _
    (PERSON, JOB, HIREDATE, SAL) VALUES (:PERSON, :JOB, : HIREDATE, :SAL)"
...
dim person OracleObject = new OracleObject("SCOTT.TPERSON", OracleConnection1)
person("NAME") = "SCOTT"
person("ADDRESS.CITY") = "NEW YORK"
...
cmd.Parameters("PERSON").DbType = OracleDbType.Object
cmd.Parameters("PERSON").Value = person
...
cmd.ExecuteNonQuery()

Back to list