This article describes how to work with Oracle Objects using dotConnect for Oracle.

• Introducing
• Requirements
• Creating Typed OracleObjects
• Using Typed OracleObjects

Introducing

dotConnect for Oracle leverages working with user-defined types in Oracle database providing mechanisms for using both typed and untyped OracleObjects.

Untyped OracleObjects are represented by instances of the OracleObject class. This is a flexible way of treating Oracle objects, but it provides almost no possibilities for type validation and is less convenient than using typed OracleObjects. See Objects article for more details about untyped Oracle Object usage.

Typed OracleObject represents the single user-defined type, allowing to work with its instances as with usual .NET objects. This tutorial shows how typed OracleObjects can be created and used.

Typed OracleObjects are available in the OCI mode only. Untyped OracleObjects can be used in both OCI and Direct modes.

Requirements

This walkthrough supposes that you know how to establish a connection to server, how to create the database objects, how to manipulate with the data stored on the server and how to pass parameters to the commands executed.

To follow the tutorial, you also need the user-defined type TAddress and a table EmpObject with a field of the TAddress type created in your Oracle database. This can be made by executing the following script:

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

CREATE TABLE EmpObject (
  Code NUMBER PRIMARY KEY,
  Person VARCHAR2(40),
  Address TADDRESS,
  Job VARCHAR2(9)
);
/

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (1, 'SMITH', TAddress('UK', 'London', 'Street', 12), 'CLERK');

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (2, 'JONES', TAddress('USA', 'New York', 'Street', 418), 'MANAGER');

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (3, 'SCOTT', TAddress('CANADA', 'Ottawa', 'Street', 26),'PRESIDENT');

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (4, 'MARTIN', TAddress('FRANCE', 'Paris', 'Street', 162), 'ANALYST');

Creating Typed OracleObjects

To create a typed OracleObject, one may perform the following steps:

1. Start Typed Oracle Objects Wizard: select Tools -> Oracle -> Oracle Objects Wizard item in main menu of Visual Studio.

2. Set the connection properties to the server where you created an object type and click Next:

   

   Pay attention that the connection can be made in the OCI mode only since Oracle Objects are unavailable in the Direct mode.

3. Select the type you want to work with and click Next. In this sample, the TAddress type is used:

   

   In this window, you can select to show either all object types in the database or only those belonging to the specific user. For this purpose, use the radio buttons All and User accordingly, and select the user in the corresponding list box.

4. The next window allows you to set up the name of the generated class, the namespace where it would be placed and the naming rules for attributes of the object. In this tutorial, the defaults are used, so just click Finish.
   

The wizard will generate the TAddress class representing the TAddress object type.

Using Typed OracleObjects

With the typed OracleObject class generated, attributes of Oracle UDTs can be used just as usual members of .NET objects. The following sample shows how Taddress instances may be used. In the sample, the contents of the EmpObject table are retrieved and printed using instances of the TAddress class:

// Create and open a connection. Connection should not be in Direct mode,
// as in this case Oracle Objects would be unavailable.
OracleConnection connection = new OracleConnection
	("Server = Ora; User Id = Scott; Password = tiger; Direct = false;");

connection.Open();

// Create a command selecting all rows from the table EmpObject
OracleCommand command = new OracleCommand
	("select * from empobject", connection);

// By default, all Oracle Objects are retrieved by the application as instances 
// of the OracleObject class. Thus, to use typed OracleObjects, we need to set 
// the mapping from the TADDRESS Oracle UDT to Taddress class used in the application.
OracleType.GetObjectType("TAddress", connection).UdtType = typeof(TAddress);

// Execute the command, retrieve the data reader and print the table content.
OracleDataReader r = command.ExecuteReader();
while (r.Read())
{
	TAddress addr = (Taddress)r["Address"];
	Console.WriteLine
		(r["Person"].ToString() + ": " + addr.Country + ", " + 
		addr.City + ", " + addr.Street + " " + addr.Apartment);
}

' Create and open a connection. Connection should not be in Direct mode,
' as in this case Oracle Objects are unavailable.
 Dim connection = New OracleConnection( _
	"Server = Ora; User Id = Scott; Password = tiger; Direct = false;")
connection.Open()

' Create a command selecting all rows from the table EmpObject
Dim command = New OracleCommand
	("select * from empobject", connection)

' By default, all Oracle Objects are retrieved by the application as instances
' of the OracleObject class. Thus, to use typed OracleObjects, we need to set
' the mapping from the TADDRESS Oracle UDT to Taddress class used in the application.
OracleType.GetObjectType("TAddress", connection).UdtType = GetType(TAddress)

' Execute the command, retrieve the data reader and print the table content.
Dim r As OracleDataReader = command.ExecuteReader()
While (r.Read())
	Dim addr As Taddress = r("Address")
	Console.WriteLine( _
		r("Person").ToString() + ": " + addr.Country + ", " + _
		addr.City + ", " + addr.Street + " " + addr.Apartment.ToString())
End While

In the next sample we update the EmpObject table using the parameter of the Taddress type:

// Create an UPDATE command with parameter
OracleCommand command = new OracleCommand
	("update empobject set address = :pAddress where code = 1", connection);
            
// Create and set a Taddress object
Taddress addr = new Taddress();
addr.Country = "USA";
addr.City = "New York";
addr.Street = "Street";
addr.Apartment = 418;

// Set up the command parameter. 
OracleParameter param = new OracleParameter("pAddress", OracleDbType.Object, "TAddress");
param.Value = addr;
command.Parameters.Add(param);

// Execute the command.
command.ExecuteNonQuery();

' Create an UPDATE command with parameter
Dim command = New OracleCommand( _
	"update empobject set address = :pAddress where code = 1", connection)

' Create and set a Taddress object
Dim addr As Taddress = New Taddress()
addr.Country = "USA"
addr.City = "New York"
addr.Street = "Street"
addr.Apartment = 418

' Set up the command parameter. 
Dim param = New OracleParameter("pAddress", OracleDbType.Object, "TAddress")
param.Value = addr
command.Parameters.Add(param)

' Execute the command.
command.ExecuteNonQuery()

See Also

Working with Objects  | OracleObject class