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 45 minutes

Introduction

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 (ODP.NET), Unmanaged Driver supports 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.

Note: ODP.NET, Managed Driver does not support UDTs as of this writing. ODP.NET, Unmanaged Driver must be used instead.

Prerequisites

Before starting this tutorial, you should:

1. Install Microsoft Visual Studio 2013.

2. Install Oracle Database 10.2 or later.

3. Install Oracle Data Access Components (ODAC) 12.1.0.1.2 or later from OTN. The ODAC download includes Oracle Developer Tools for Visual Studio and ODP.NET that will be used in this lab.

4. Download and unzip the files.zip to a local folder on your file system. In this tutorial, we use the C:\files folder.

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 a connection to the HR schema, delete it by highlighting it and pressing the delete key.

   

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

   

4. Enter HR for the User name and Password. Click Save password. Choose a Data source name such as ORCL, and click Test Connection.

   Note: The Data Source selected should be Oracle Database (ODP.NET, Unmanaged Driver). ODP.NET, Managed Driver does not currently support User Defined Types.

   

5. Test connection succeeded. Click OK.

   

6. Click OK.

   

7. Your connection has been created. Expand HR.ORCL.

   

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.

    

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 Tables 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 Tables and select New Relational Table.

   

4. Enter CUSTOMER_MEETINGS for the Table name. Under Column Properties, enter MEETING_ID for the name and uncheck Not Null. Click Add.

   

5. Enter TIME for the Name, select DATE for the Data type, uncheck Not Null and click Add.

   

6. Enter LOCATION for the Name, select VARCHAR2 for the Data type, uncheck Not Null and enter 20 for the Size. Then click Add.

   

7. Enter CUSTOMER for the Name, select CUSTOMER_OBJ for the Data type, uncheck Not Null 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.

   

8. Enter ACCOUNT_REP for the Name, select ACCOUNTREP_OBJ for the Data type, uncheck Not Null, 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.

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.. or press Ctrl+O.

   

2. Select udtobe_insert.txt from the files folder that you extracted 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 to select Copy.

   

5. Switch to the Query window and Paste the code that you just copied.

   

6. Right-click in the Query Window again and select Select All or press Ctrl+A.

   

7. Click the Execute Query icon.

   

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.

    

You will now create a new project. Perform the following steps:

1. Select File > New > Project...

   

2. Select Visual C# > Windows from the left navigator, select Windows Forms Application for the Template and click OK.

   

3. Your project was successfully created.

   

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 View > Other Windows > Data Sources or use the shortcut Shift+Alt+D.

   

2. Click Add New Data Source...

   

3. Select Database and click Next.

   

   Select Dataset and click Next.

   

4. Make sure HR.ORCL is selected, click Yes, include sensitive data in the connection string. 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 for 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.

   

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. Expand RowTemplate and select the icon for DefaultCellStyle.

   

3. Change the WrapMode parameter to True and click OK.

   

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. In 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. If you receive an error here, check to make sure your HR connection in server explorer is using ODP.NET Unmanaged Driver. The Managed Driver is not currently supported.

   

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

   

7. In 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. In 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. In 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.

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

1. Select Build > Build WindowsFormsApplication1.

   

2. Select Debug > Start Debugging or press F5.

   

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.

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().

   

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

   

4. Select the udtobe_tostring.txt file in the files folder on your desktop. 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, and Paste the code that you copied.

    

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, and Paste the code that you copied.

    

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, and Paste the code that you copied.

    

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

    

23. You can rerun the application. Select Build > Rebuild WindowsFormsApplication1.

    

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 of the CUSTOMER field values to see that when you hover, the rest of the data is displayed.

    

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(). Right - click and select Properties.

   

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. Save the Dataset. From the Visual Studio menu choose File > Save Dataset1.xsd.

   

7. 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.

   

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

   

9. The ACCOUNT_REP column has been refreshed.

   

10. Now you can rerun the application. Select Build > Rebuild WindowsFormsApplication1.

    

11. Select Debug > Start Debugging.

    

12. The form is displayed with the data.

    

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

    

In this tutorial, you have learnt 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 custom classes for Oracle user-defined types
• Modify the ToString() method of the custom classes
• Use the DEREF SQL operator to select the value of a REF

Resources

• You may visit the Oracle Technology Network Portal to know the latest developments in Oracle .NET.
• To learn more about Oracle .NET with Visual Studio refer to additional OBEs in the Oracle Learning Library.

Credits

• Lead Curriculum Developer: Ashwin Agarwal
• Other Contributors: Alex Keh, Christian Shay