Accessing/Manipulating Relational Data using XML - Employee Management System

Table of Contents

• Objective
• Overview
• User Notes with Screen Shots
• Required Software
• Extracting the Sample
• Database Setup
• Preparing and Running the Sample
• Using the Sample
• Description of Sample Files

The purpose of this sample application is to demonstrate the following:

1) Retrieval of relational data from an Oracle database as XML through Oracle Data Provider for .NET (ODP.NET)

2) Insertion/Update/Deletion of relational data in an Oracle database using XML as input through ODP.NET

With Oracle database version 8.1.7 and later, it is possible to convert data from relational tables into XML. Data in the form of XML can be used for insertion, update and deletion back to relational data in the database. ODP.NET 9.2.0.4 and higher provides this XML support to query and manipulate the relational data using XML. The relational data queried as XML can then be exchanged seamlessly with the Microsoft .NET framework. An XML document can be used to insert, update or delete relational data in the Oracle database with ODP.NET.

ODP.NET 9.2.0.4 provides enhancements to OracleCommand objects like XMLCommandType, ExecuteXmlReader etc. that help in accessing/manipulating relational data using XML.

This sample, an Employee Management System, is a windows application using Visual Basic .NET. It uses the 'Scott' schema available with the Oracle database. Some of the XML features provided by this sample include:

• Querying employee data
• Inserting a new employee
• Updating/Deleting employee details

On establishing a successful connection to an Oracle database, the employee data from the 'Emp' table is retrieved and displayed as XML in a textbox and a tabular format in a datagrid. Users can update/delete this XML and submit it to get the corresponding details updated in the database. The user is given the facility to insert a new employee record using an XML document.

When this sample is run, the user should first enter the appropriate connection details and click the 'Connect' button to connect to the Oracle database. After the database connection is established successfully, the list of employees from the Emp table is displayed in tabular (DataGrid) and XML format, as shown in Figure 1.

Figure 1: Screenshot after connection is established

To insert a new record into the 'Emp' table using an XML document, click on the 'Create New Record' button. A sample XML record appears in the textbox, which can be modified to conform the required data within the appropriate tags, as shown in Figure 2. Please refer to the hints displayed in the 'Hints to Execute the Sample' text on the application screen for the instructions to perform the current operation.

Figure 2: Displays sample XML record for insertion

To save the new record click on the 'Insert' button. The new data is reflected in the DataGrid and the XML document.

To update the existing records, the user can make changes to the XML document displayed in the text area. Refer to the 'Hints to Execute the Sample' section for more details. The user can click on the 'Update' button to update data in the database.  The update is then reflected in the Datagrid and the XML document. The user can also update individual records by clicking on a record in the datagrid. The corresponding XML of the selected record is displayed in the textbox. The user can modify the XML and click on the 'Update' button to update the record in the database.

To delete record(s), the user can select a record from the datagrid and click on the 'Delete' button. The changed data, with all records is reflected in the datagrid and the XML document. Users can also delete all these records in a batch by using the 'Delete' button.

• Visual Basic .NET installed with Microsoft Visual Studio .NET 2002 or higher.

• Connectivity to an Oracle Database 8.1.7 or higher, running SQL*Net TCP/IP listener

• Oracle Data Provider for .NET (ODP.NET) 9.2.0.4

Unzip RelationalDataVBSample.zip using Winzip or command utility to your convenient directory referred to as <Extract_Dir>. Extraction of the zip file results in the creation of "RelationalDataSample" directory.  Refer Description of Sample Files section for more details.     

No special database setup is required to run this sample. This sample uses 'Scott' sample schema available with the Oracle databases. It uses 'Emp' and 'Dept' tables. In case you do not have the 'Scott' schema, you can alternatively connect to a different database user and create the required tables by running the demobld.sql script available at <Extract_Dir>\RelationalDataSample\setup at SQL*plus prompt:

SQL> @<Extract_Dir>\RelationalDataSample\setup\demobld.sql

<Extract_Dir> - is the directory where you have extracted the RelationalDataVBSample.zip file.

1. Ensure completion of the Database setup.

2. Open RelationalDataSample.sln using Visual Studio .NET from <Extract_Dir>\RelationalDataSample\src folder. 

3. Ensure that the following .NET Component References have been added to RelationalDataSample project: 
          System, 
          System.Data, 
          System.Drawing, 
          System.Windows.Forms, 
          System.XML,
          Oracle.DataAccess. 

To add the above .NET Components 
a)Go to Menu->View-> Solution Explorer
b)Right click on RelationalDataSample project, choose 'Add Reference'
c)Choose the above .NET Components from the list displayed
d)Click 'Select', then OK
e) The chosen .NET component gets added to the project

5. Build and run the sample by pressing Ctrl+F5. Refer to Using the Sample section on how to use this sample.

1. When running this sample, enter the connection details and provide the username and password for the user that has the required Emp and Dept tables. Enter the host string as appropriate to your database setup.

2. After providing the connection details, click on the 'Connect' button. In case the connection could not be established successfully, the connection status is displayed as 'Not Connected' in red color. On successful connection, the connection status is displayed as 'Connected' in green color, the DataGrid and textArea are populated with employee data and the UI controls are enabled.

3. To update the existing Emp records, the user can edit the XML data displayed. To change the value of deptno the user can check the value from the list of valid deptno. Do not modify the empno as it is a key column. Also, refer to the hints given on the screen. Then, click on the 'Update' button to update the Emp data in the database. On a successful update, a message indicating this is displayed.

4. To insert a new record, the user can click on the 'Create New Record' button. On clicking this button, the text area displays a sample XML record. The user can edit this new record. Do not change 'empno' as it is a key column that is generated automatically by the application as a unique value. The user can view the list of valid 'deptno' from the list. Refer to the hints given. After editing the sample record, the user can click on the 'Insert' button to save the data in the database. On successful insertion of the Emp record, a message indicating this is displayed. If any error occurs, the error message is displayed to the user.

5. To delete a record, the user can click on the row in the datagrid that is to be deleted. The corresponding XML of that row appears in the textbox. The user can then click 'Delete' button. A meesage dialog appears with Yes/No option. On selecting 'Yes' the record(s) gets deleted and a confirmation message is displayed. On selecting 'No', no delete happens.

6. To change the connection, the user can click on the 'Disconnect' button and connect to the other database by providing relevant connection details.

7. To exit the application, click on the 'Exit' button.

Description of Sample Files

Back To Top

Following is the directory structure of the RelationalDataVBSample.zip :

Please enter your comments on this sample in the OTN Sample Code Discussion Forum.