Using Oracle User-Defined Types with .NET and Visual Studio

Purpose

This tutorial describes how you can create applications that access Oracle user-defined types with .NET and Visual Studio.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating an Oracle Connection
 Creating User-Defined Datatypes
 Creating the Object Table and Relational Tables
 Inserting and Viewing Data
 Creating a Project
 Adding a Data Source
 Modifying Properties on the Grid
 Generating Custom Classes for User-Defined Types
 Running the Project
 Modifying the ToString method of the UDT Custom Classes
 Modifying the Application to Access REFs to Object Data
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

User-defined types (UDTs) make it possible to model real-world entities such as "Customers" or "Purchase Orders" as objects in the Oracle Database. The Oracle Data Provider for .NET 11g (ODP.NET) has added support for UDTs, so that developers can access their objects, VARRAYs, and nested table types from C# or VB.NET code. Oracle Developer Tools for Visual Studio also supports UDTs, enabling you to use Visual Studio to create and modify object types and to autogenerate .NET code to access them.

In this tutorial, you will create an application that displays the contents of a relational table that has UDTs defined for some of the columns. You will create the type definition in Oracle using Visual Studio, create the relational table that contains the UDT, load data into the table, and then auto generate the .NET data access code for the application that will display the data.

It is important to understand that Oracle object data can be stored "inline" with each attribute in the object being stored as hidden columns inside of a relational table alongside the other relational columns. They can also exist in their own dedicated "object table" where they become "referencable" via a "pointer" or object id which is a text string known as a "REF". In this tutorial, you will access UDT data in both cases: where Oracle objects are stored inline in a relational table, and those cases where simply the REF to the object is stored in the table.

UDTs will be of particular interest to .NET developers leveraging Oracle Spatial and Oracle Advanced Queuing, which accept UDTs as part of their PL/SQL interfaces. VARRAYs and nested table types are also frequently used in PL/SQL packages.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Microsoft Visual Studio 2008 (Note: if you are using Microsoft Visual Studio 2005, the screenshots below will be slightly different)

2.

Install Oracle Database 9.2 or later or Oracle Database XE

3.

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.1.0.6.20 or later from OTN

4.

Download and unzip the codesnippets.zip file into your working directory.

Back to Topic List

Creating an Oracle Connection

Before you begin creating your application, you open Visual Studio and examine the interface. Perform the following steps:

1.

Open Visual Studio.

 

2.

Select View > Server Explorer.

 

3.

 

In Server Explorer, if you already have an HR.ORCL connection, you can expand it to connect to the database.

Then enter HR for the password and click OK.

Otherwise, right-click Data Connections and select Add Connection....

 

4.

Enter your Data source name. Enter hr for the User name and Password. Click Save password, select Default as the Role, and click Test Connection.

 

5.

Test connection succeeded. Click OK.

 

6.

Click OK.

 

7.

Your connection has been created. Expand HR.ORCL.

 

Back to Topic List

Creating User-Defined Types

In this section, you will create the UDT definitions using the Visual Studio designer. Perform the following steps:

1.

Expand the HR.ORCL connection. Right-click User-Defined Types and select New Object Type.

 

2.

Enter ADDRESS_OBJ for Type Name and under Attributes, click Add.

 

3.

Under Attribute Properties, enter STREET for the Name, select VARCHAR2 for the Type and enter 200 for the Size. Then click Add again to add another attribute.

 

4.

Under Attribute Properties, enter CITY for the Name, select VARCHAR2 for the Type and enter 200 for the Size. Then click Add again to add another attribute.

 

5.

Under Attribute Properties, enter STATE for the Name, select CHAR for the Type and enter 2 for the Size. Then click Add again to add another attribute.

 

6.

Under Attribute Properties, enter ZIP for the Name, select VARCHAR2 for the Type and enter 20 for the Size. Then click OK to create the object type.

 

7.

The ADDRESS_OBJ user-defined type was created successfully.

 

8.

Next, you need to create a phone number list, PHONELIST_OBJ which will be a VARRAY of as many as 10 phone numbers. Right-click User-Defined Types and select New Varray.

 

9.

Enter PHONELIST_OBJ for the Type Name, enter 10 for the Limit, select VARCHAR2 for the Type and enter 20 for the Size. Then click OK.

 

10.

The PHONELIST_OBJ Varray was created successfully.

 

11.

Next, you will create the CUSTOMER_OBJ user defined type which will have as attributes the ADDRESS_OBJ and PHONELIST_OBJ objects you just created. Right-click User-Defined Types and select New Object Type.

 

12.

Enter CUSTOMER_OBJ for Type Name and under Attributes, click Add.

 

13.

Under Attribute Properties, enter CUSTNO for the Name, and click Add again to add another attribute.

 

14.

Under Attribute Properties, enter CUSTNAME for the Name, select VARCHAR2 for the Type and enter 200 for the Size. Then click Add again to add another attribute.

 

15.

Under Attribute Properties, enter ADDRESS for the Name, select ADDRESS_OBJ for the Type and click Add again to add another attribute.

 

16.

Under Attribute Properties, enter PHONELIST for the Name, select PHONELIST_OBJ for the Type and click OK to create the object type.

 

17.

The Object type was created successfully.

 

18.

Next, you will create the ACCOUNTREP_OBJ user defined type. Right-click User-Defined Types and select New Object Type.

 

19.

Enter ACCOUNTREP_OBJ for Type Name and under Attributes, click Add.

 

20.

Under Attribute Properties, enter ID for the Name, make sure NUMBER is selected for Type and click Add again to add another attribute.

 

21.

Under Attribute Properties, enter NAME for the Name, select VARCHAR2 for the Type and enter 200 for the Size. Then click Add again to add another attribute.

 

22.

Under Attribute Properties, enter PHONELIST for the Name, select PHONELIST_OBJ for the Type and click OK to create the type.

 

23.

The ACCOUNTREP_OBJ user-defined type was created successfully.

 

Back to Topic List

Creating the Object Table and Relational Table

In this section, you will create an "object table" which is where ACCOUNTREP_OBJ objects will be stored and will be accessed via a "REF". These objects will be "dereferenced" by the application in the last section of this tutorial. Perform the following steps:

1.

Under Server Explorer, right-click Table and select New Object Table.

 

2.

Enter ACCOUNTREP_TAB for the Table name and select ACCOUNTREP_OBJ for Object Type, then click Save.

 

3.

Your object type table was created successfully. Now, you will create a relational table. Right-click Table and select New Relational Table.

 

4.

Enter CUSTOMER_MEETINGS for the Table Name and under Columns, click Add.

 

5.

Enter MEETING_ID for the Name and under Columns, click Add.

 

6.

Enter TIME for the Name, select DATE for the Data type and click Add.

 

7.

Enter LOCATION for the Name, select VARCHAR2 for the Data type and 20 for the Size. Then click Add.

 

8.

Enter CUSTOMER for the Name, select CUSTOMER_OBJ for the Data type and click Add. Note that you did not check the "Create as REF" check box. This means that the CUSTOMER_OBJ data will be stored as hidden columns inside the CUSTOMER_MEETINGS table.

 

9.

Enter ACCOUNT_REP for the Name, select ACCOUNTREP_OBJ for the Data type, click the Create as Ref check box and click Save. By selecting this check box, a "REF" (which is a text string pointer to the ACCOUNTREP_TAB object table) will be stored in this column.

The object and relational tables have been created successfully.

 

Back to Topic List

Inserting and Viewing Data

In this section, you will insert some data into the object table and the relational table and then view the data in the Data Window. Perform the following steps:

1.

Select File > Open File.

 

2.

In the codesnippets folder on the Desktop, select udtobe_insert.txt from the list of files and click Open.

 

3.

Next, you need to open a query window so that you can execute the insert statements from the file you just opened. Right-click HR.ORCL and select Query Window.

 

4.

From the udtobe_insert.txt window, select all the text and right-click the white space and select Copy.

 

5.

Switch to the Query window and right-click the white space and select Paste.

 

6.

Right-click the white space in the Query Window again and select Select All.

 

7.

Click the Execute Query button.

 

8.

Your query executed successfully.

 

9.

To view the data, in Server Explorer, right-click the CUSTOMERS_MEETINGS table and select Retrieve Data.

 

10.

The data is displayed. Note that the inline object value is displayed as XML, while the REF is a text string that is a pointer to an object in the ACCOUNTREP_TAB table.

 

11.

To view the data, in Server Explorer, right-click the ACCOUNTREP_TAB table and select Retrieve Data.

 

12.

The data is displayed.

 

Back to Topic List

Creating a Project

You want to create a new project. Perform the following steps:

1.

Select File > New Project.

 

2.

Select Visual C#: Windows from the left navigator, select Windows Form Application for the Template and click OK.

 

3.

Your project was created successfully .

 

Back to Topic List

Adding a Data Source and Grid

In this section, you will use Visual Studio's automatic code generation capabilities to create an application that uses a DataGridView to display the Oracle data. To do that you first need to create a data source. Perform the following steps:

1.

Select Data > Show Data Sources.

 

2.

Click Add New Data Source...

 

3.

Select Database and click Next.

 

4.

Make sure HR.ORCL is selected, click the Yes, include sensitive data in the connection string and click Next.

 

5.

Click Next.

 

6.

Initially you will create an application that displays only the CUSTOMER_OBJ data that is stored "inline" inside of the CUSTOMER_MEETINGS table. You will examine how to display the ACCOUNTREP_OBJ data that is stored as a "REF" in a later section of this tutorial. Expand Tables > CUSTOMER_MEETINGS and select the check box in front of CUSTOMER_MEETINGS. Then click Finish.

 

7.

Drag and drop the CUSTOMER_MEETINGS data source to the Form1 canvas.

 

8.

The DataGridView control, a dataset, a TableAdapter and a few other classes have been automatically generated.

 

Back to Topic List

Modifying Properties in the Grid

In this section, you will modify the grid so that it is capable of displaying multiple lines of data per cell. This will make it possible to display all of the UDT data when the user hovers the mouse over a UDT column cell. Perform the following steps:

1.

Right-click the grid and select Properties.

 

2.

Scroll down the list.

 

3.

Expand rowTemplate and select the ... icon for DefaultCellStyle.

 

4.

Change the WrapMode parameter to True and click OK.

The parameter has been changed.

 

Back to Topic List

Generating Custom Classes for User-Defined Types

When using User Defined Types, you need to define some .NET classes that can hold the data when it is returned from Oracle. Oracle Developer Tools for Visual Studio includes a Custom Class Code Generation Wizard for this purpose. Perform the following steps:

1.

Select the Server Explorer tab. Under User-Defined Types, right-click ACCOUNTREP_OBJ and select Generate Custom Class.

 

2.

At the Oracle Custom Class Wizard Welcome page, click Next.

 

3.

Accept the defaults and click Next.

 

4.

Accept the defaults and click Next.

 

5.

Click Finish.

 

6.

Under User-Defined Types, right-click ADDRESS_OBJ and select Generate Custom Class.

 

7.

At the Oracle Custom Class Wizard Welcome page, click Next.

 

8.

Accept the defaults and click Next.

 

9.

Accept the defaults and click Next.

 

10.

Click Finish.

 

11.

Right-click CUSTOMER_OBJ and select Generate Custom Class.

 

12.

At the Oracle Custom Class Wizard Welcome page, click Next.

 

13.

Accept the defaults and click Next.

 

14.

Accept the defaults and click Next.

 

15.

Click Finish.

 

16.

Right-click PHONELIST_OBJ and select Generate Custom Class.

 

17.

At the Oracle Custom Class Wizard Welcome page, click Next.

 

18.

Accept the defaults and click Next.

 

19.

Accept the defaults and click Next.

 

20.

Click Finish.

 

21.

Click the Solution Explorer tab. Notice that the Accountrep_Obj.cs, Address_Obj.cs, Customer_Obj.cs, Phonelist_Obj.cs file was created.

All classes have been generated. In the next section, you run the project.

 

Back to Topic List

Running the Project

In this section, you run the application which will display the data. Perform the following steps:

1.

Select Build > Build WindowsFormApplication2.

 

2.

Select Debug > Start Debugging.

 

3.

The application is run and the data is displayed. Notice that there is no data contained in the Customer field. The field is a user-defined type and therefore you need to define how the grid should represent this data by modifying the ToString() method of each of the .NET custom classes you created earlier. This method is called whenever the class data is to be displayed in a text field.

Close the form.

 

Back to Topic List

Modifying the ToString method of the UDT Custom Classes

In this section, you will now add code to the ToString() method in each of your 4 custom classes . Perform the following steps:

1.

In the Solution Explorer, right click Customer_Obj.cs and select View Code.

 

2.

From the drop down list, select ToString() section.

 

3.

You want to replace this section in the code with the code contained in a file. Select File > Open File.

 

4.

In the codesnippets folder on the Desktop, select the udtobe_tostring.txt file and click Open.

 

5.

Select the code in the Customer_Obj.cs section from the file. Right-click and select Copy.

 

6.

Select the Customer_Obj.cs tab. Select the ToString() section, right-click and select Paste.

 

7.

Right-click the Customer_Obj.cs tab and select Save Customer_Obj.cs.

 

8.

In the Solution Explorer, right click Address_Obj.cs and select View Code.

 

9.

From the drop down list, select ToString() section.

 

10.

Select the udtobe_tostring.txt tab. Select the code in the Address_Obj.cs section from the file. Right-click and select Copy.

 

11.

Select the Address_Obj.cs tab. Select the ToString() section, right-click and select Paste.

 

12.

Right-click the Address_Obj.cs tab and select Save Address_Obj.cs.

 

13.

In the Solution Explorer, right click Phonelist_Obj.cs and select View Code.

 

14.

From the drop down list, select ToString() section.

 

15.

Select the udtobe_tostring.txt tab. Select the code in the Phonelist_Obj.cs section from the file. Right-click and select Copy.

 

16.

Select the Phonelist_Obj.cs tab. Select the ToString() section, right-click and select Paste.

 

17.

Right-click the Phonelist_Obj.cs tab and select Save Phonelist_Obj.cs.

 

18.

In the Solution Explorer, right click Accountrep_Obj.cs and select View Code.

 

19.

From the drop down list, select ToString() section.

 

20.

Select the udtobe_tostring.txt tab. Select the code in the Accountrep_Obj.cs section from the file. Right-click and select Copy.

 

21.

Select the Accountrep_Obj.cs tab. Select the ToString() section, right-click and select Paste.

 

22.

Right-click the Accountrep_Obj.cs tab and select Save Accountrep_Obj.cs.

 

23.

You can rerun the application. Select Build > Rebuild WindowsForm Application2.

 

24.

Select Debug > Start Debugging.

 

25.

The grid is displayed. Notice that only a small portion of the UDT data is visible in the CUSTOMER field.

 

26.

Place your cursor over one the CUSTOMER field values to see that when you hover, the rest of the data is displayed.

 

Back to Topic List

Modifying the Application to Access REFs to Object Data

In the last section, you saw that the application displayed a REF in the ACCOUNT_REP column. In this section, you will change the SQL being used by the application so that it will "dereference" this REF and display the value of the ACCOUNTREP_OBJ object. To acheive this, you will need to use the dataset designer to change the SQL that is being executed. Perform the following steps:

1.

Select the Form1.cs tab. Right-click dataSet1 and select Edit in DataSet Designer.

 

2.

Select Fill, GetData().

 

3.

In the Properties window, for CommandText, select the ... icon.

 

4.

In the Select statement, change ACCOUNT_REP to DEREF(ACCOUNT_REP) REP and click OK.

 

5.

Click Yes to regenerate update commands based on the new command text.

 

6.

You need to refresh the data source for the grid. To do this, you will delete and reselect the datasource. Select the Form1.cs tab. Select the Grid, then select the arrow to get the menu and select None for Choose Data Source.

 

7.

Reselect the data source. With the Grid selected, select the arrow to get the menu and select cUSTOMERMEETINGSBindingSource.

 

8.

The ACCOUNT_REP column has been refreshed.

 

9.

Now you can rerun the application. Select Build > Rebuild WindowsFormApplication2.

 

10.

Select Debug > Start Debugging.

 

11.

The form is displayed with the data.

 

12.

Hover over one the cells under REP to see the detail.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Create user-defined datatypes
 Create and insert data into object and relational tables
 Create a project with an Oracle datasource and a DataGridView control
 Generate .NET customer classes for Oracle user-defined types
 Modify the ToString() method in custom classes
 Use the DEREF SQL operator to select the value of a REF

Back to Topic List

 Move your mouse over this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve
Untitled Document