Table of Contents
Oracle Database 2 Day + .NET Developer's Guide
PrefaceIntroductionInstalling .NET ProductsBuilding a Simple .NET Application Using ODP.NETRetrieving and Updating with Oracle Data Provider for .NETUsing Oracle Developer Tools for Visual StudioUsing PL/SQL Stored Procedures and REF CURSORsUsing ASP.NET with Oracle DatabaseDeveloping and Deploying .NET Stored ProceduresIncluding Globalization Support
Thank you for your feedback!This chapter contains:
Oracle Developer Tools for Visual Studio (ODT) is a tightly integrated Add-in for Visual Studio. Using enhancements that ODT brings to the Server Explorer, you can automatically create tables, indexes, constraints, data connections and other database schema objects. Additionally you can automatically generate application code.
This section shows you how to use the Server Explorer to connect to the Oracle Database for the purpose of automatically creating or modifying database schema objects.
From the View menu, select Server Explorer.
In Server Explorer, right-click Data Connections.
Select Add Connection.
When the Add Connection window appears, determine if the Data source says Oracle Database (Oracle ODP.NET).
If it does, skip to Step 6.
If Data source does not say Oracle Database (Oracle ODP.NET), select Change.
The Change Data Source window appears.
Choose Oracle Database and then select Oracle Data Provider for .NET.
On the Connection Details tab, in the Add Connection window, enter the following information:
Data source name: For this example, use the alias of the remote database instance, orcl.
If you are connecting to a database on the same computer, use the Local Database.
Select the Use a specific user name and password option.
For User name, enter HR.
For Password, enter the password created when the hr account was unlocked and set up.
To save the password for future sessions, check the Save password box.
Ensure that Role is set to Default. This refers to the default roles that have been granted to the user hr.
The Connection name should be generated automatically from the Data source name and the User name values. In this exercise, it will be HR.orcl.
Click the Apply Filters tab, and verify that the HR schema is in the Displayed schemas column. When you expand the schema category nodes in the data connection, only those schema objects (tables, views, and so on) selected in the Apply Filters tab appear.
Click Test connection.
The test should succeed. Click OK.
If the test fails, it may be due to one or more of the following issues that you must address before proceeding with further steps:
The database is not started.
The database listener is not started.
The database connectivity is not properly configured.
You do not have the correct user name, password, or role.
In the Add Connection window, click OK.
In the Server Explorer, expand the HR.ORCL connection to show the contents of the HR schema. You should see Tables, Views, Procedures, Functions, Packages, Synonyms, Sequences, and so on.
Oracle Developer Tools includes a user interface for creating database objects. In this section, you will create a table named DEPENDENTS.
In Server Explorer, right-click Tables and select New Relational Table.
A table design window appears.
In design view, enter DEPENDENTS for Table name.
In the Column Properties tab, add the following six columns in this manner:
Click Add. Then enter the new column information. Keep clicking add until you have added all the new columns.
Fields may differ depending on the data type. You might have to close windows such as Server Explorer or Solution Explorer to access the entire tab.
Name LAST_NAME, Data Type VARCHAR2, and Size 30. Leave all other properties at their default values.
Name FIRST_NAME, Data Type VARCHAR2, and Size 20. Leave all other properties at their default values.
Name BIRTH_DATE, Data Type DATE. Leave all other properties at their default values.
Name RELATIONSHIP, Data Type VARCHAR2, and Size 20. Leave all other properties at their default values.
Name EMPLOYEE_ID, Data Type NUMBER, deselect Allow null, enter Precision 6 and Scale 0.
Name DEPENDENT_ID, Data Type NUMBER, deselect Allow null check box, enter Precision 6 and Scale 0.
The SQL statement for constructing the table appears in the Preview SQL window, similar to this.
Click OK to close the Preview SQL window.
In the table design view, click Save.
This action creates the new table DEPENDENTS in the HR schema. The new table is listed in the Server Explorer.
Indexes are an optional but very powerful feature of relational databases. An index enables quick access to the rows (or records) in a table. In this section, you will create an index for the DEPENDENTS table.
In the DEPENDENTS Table Design view, click the Indexes tab.
Click Add under the Indexes area.
The Index Properties area becomes active.
Under Index Properties (to the right), enter the Name DEPENDENTS_INDEX, and leave all other properties in their default state.
At the bottom of the Index Properties area, click Add.
Under Index keys, click in the first cell of the Key column, and select DEPENDENT_ID from the list.
A Preview SQL window appears, displaying SQL statement to construct the index.
Click OK to close the Preview SQL window.
In the table design view, click Save.
This creates the new index on the table DEPENDENTS in the HR schema. To see this in the Server Explorer, expand the DEPENDENTS table and related Indexes.
The database uses constraints to automatically enforce data integrity defining rules for permissible data values. Constraints also implement primary and foreign keys in the table. In this section, you will add such constraints to the new table DEPENDENTS.
How to add foreign and primary keys:
In the DEPENDENTS table design view, click the Constraints tab.
Note that depending on your configuration, there may already be default check constraints in the list.
Under the Constraints area, add the following constraints in this manner:.
Under Constraint Properties, Click Add. Then enter the new constraint information. Keep clicking add until you have added all the new constraints.
Name EMPLOYEES_FK, Type Foreign Key, Table EMPLOYEES, Constraint EMP_EMP_ID_PK. Under Association, select Referenced Column: EMPLOYEE_ID, and Local Column: EMPLOYEE_ID, set the On delete value to Cascade. Leave all other properties at their default values.
Name DEPENDENTS_PK, Type Primary Key.
Under the Primary key columns area, click Add (you may need to scroll down). Under Primary Key Columns, select Key: DEPENDENT_ID, set the Using index value to DEPENDENTS_INDEX. Leave all other properties at their default values.
The Preview SQL window displays the code generated for constraints on table DEPENDENTS. Note that adding constraints is an ALTER TABLE command because constraints change the definitions of the DEPENDENT_ID and EMPLOYEE_ID columns of the table.
Click OK to close the Preview SQL window.
In the table design view, click Save.
This action creates the two new constraints on the DEPENDENTS table in the HR schema. To see the Server Explorer, expand the hierarchy tree for the table DEPENDENTS and constraints.
You must now add data to the new DEPENDENTS table.
In Server Explorer, right-click the DEPENDENTS table and select Retrieve Data.
A table grid for DEPENDENTS appears in design view.
Enter the four records listed in Table 5-1 into the table grid.
Table 5-1 New Data for the DEPENDENTS Table
| LAST_NAME | FIRST_NAME | BIRTH_DATE | RELATIONSHIP | EMPLOYEE_ID | DEPENDENT_ID |
|---|---|---|---|---|---|
|
Ernst |
Mary |
06-MAY-2000 |
daughter |
104 |
1041 |
|
Atkinson |
Sue |
12-JUL-1998 |
daughter |
130 |
1301 |
|
Ernst |
David |
02-APR-2007 |
son |
104 |
1042 |
|
Sciarra |
Aaron |
31-JAN-2008 |
son |
111 |
1111 |
The grid now looks as follows:
Note that the data is automatically saved as you move between rows.
To explore the content of the DEPENDENTS table, we will build a form that uses a simple table query. In this section you will use the Visual Studio integrated development environment (IDE), to automatically generate the code that corresponds to your actions.
Start a new project, as described in "Creating a New Project". Name the new project as indicated.
Visual C#:
HR_ODT_CS.
Visual Basic:
HR_ODT_VB.
Check Create Directory for Solution. Click OK.
Switch to the Form1 design view, if you are not already in it.
Note: All applications start with Form1, but this is not related to applications created in previous chapters.
Click on the Server Explorer window to enable the Show Data Sources window.
From the Visual Studio Data menu, select Show Data Sources.
The Data Source window appears.
In the Data Sources window, click Add New Data Source.
The Data Source Configuration Wizard opens.
In the Data Source Configuration Wizard, under Choose a Data Source Type, select Database.
Click Next.
Under Choose Your Data Connection, select HR.ORCL, or HR.(Local Database). For this example, we will use HR.ORCL.
Select Yes, include sensitive data in the connection string.
Click Next.
Under Save the Connection String to the Application Configuration File, select Yes, save the connection as: ConnectionString.
Click Next.
Under Choose Your Database Objects, expand Tables.
Check the DEPENDENTS(HR) table.
Change the DataSet name to tableDependents.
Click Finish.
See Also:
"Using the DataSet Class with Oracle Data Provider for .NET" for information about the DataSet ClassTo automatically generate code using drag-and-drop:
Switch to the Form1 Design view.
In the Data Sources window, expand tableDependents.
Select the DEPENDENTS table, and drag it onto Form1.
You may need to resize both the form and the table grid.
Note that along with the table grid (which includes record navigation elements), the following components were added to the design view of your project. These objects represent automatically generated code for Form1.
Visual C#:
tableDependents, dEPENDENTSBindingSource, dEPENDENTSTableAdapter, tableAdapterManager, and dEPENDENTSBindingNavigator
Visual Basic:
TableDependents, DEPENDENTSBindingSource, DEPENDENTSTableAdapter, TableAdapterManager, and DEPENDENTSBiningNavigator
Double-click the Save icon (floppy disk) near the top of Form1.
This opens the code window for the Save icon for Form1.
In the private method, xxxSaveItem_Click(), encapsulate the existing code in a try...catch block. See the code listed for the complete Visual C# and Visual Basic names of this automatically generated method.
Also, add a MessageBox.show() call to both the try and catch sections. The updated method code follows, with new or changed code in bold font.
Visual C#:
private void dEPENDENTSBindingNavigatorSaveItem_Click(object sender, EventArgs e) { try { this.Validate(); this.dEPENDENTSBindingSource.EndEdit(); this.tableAdapterManager.UpdateAll(this.tableDependents); MessageBox.Show("Update successful"); } catch (System.Exception ex) { MessageBox.Show("Update failed: "+ ex.Message.ToString()); } }
Visual Basic#:
Private Sub DEPENDENTSBindingNavigatorSaveItem_Click( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DEPENDENTSBindingNavigatorSaveItem.Click Try Me.Validate() Me.DEPENDENTSBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.TableDependents) MessageBox.Show("Update successful") Catch ex As Exception MessageBox.Show("Update failed: " + ex.Message.ToString()) End Try End Sub
To compile and run the application, follow the instructions in section "Compiling and Running the Application".
You can test the new application in the following manner. The floppy disk icon represents the Save command.
Change the DEPENDENT_ID value for Mary Ernst to 1110 and click the Save icon. The message box Update successful should appear. Click OK to dismiss the message box.
Change the EMPLOYEE_ID value for David Ernst to 99999 and click the Save icon. The following message should appear: Update failed: ORA-02291: integrity constraint (HR.EMPLOYEES_FK) violated - parent key not found. Click OK to dismiss the message box.
