Building .NET Applications Using Oracle Developer Tools for Visual Studio
Building .NET Applications Using Oracle Developer Tools For Visual Studio
Building .NET
Applications Using Oracle Developer Tools for Visual Studio
This tutorial describes how to build .NET applications using Oracle Developer Tools for Visual Studio .
Approximately 30 minutes
This tutorial covers the following topics:
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.
The new Oracle Developer Tools for Visual Studio is a tightly integrated "Add-in" for Visual Studio that brings the power of the Oracle Database to .NET developers! Designed to be completely intuitive for database developers, your productivity will increase starting on day one. And if you are new to Oracle development, this is an easy way to learn!
Use the Server Explorer to browse your Oracle
schema, launch one of the many powerful designers and wizards to create
and alter schema objects, and drag and drop schema objects onto your form to
automatically generate code.
There's also a PL/SQL editor, Adhoc SQL Query Window, and integrated context sensitive online help, including the Oracle SQL and PL/SQL Users Guides. And with the Oracle Data Window you won't have to leave the Visual Studio environment for routine database tasks like inserting and updating Oracle data or testing stored procedures!
This and many other features greatly improve developer productivity and make developing for Oracle on Windows easy to learn!
Back to Topic List
Before you perform this tutorial, you should:
| 1. |
Install Microsoft Visual Studio 2008 |
| 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 |
Back to Topic List
Before you begin creating your application, you open Visual Studio and examine the interface. Perform the following steps:
| 1. |
From your Windows Program menu, start Visual Studio.
|
| 2. |
Select View > Server Explorer.
|
| 3. |
The Server Explorer panel opens. 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. |
The Add Connection dialog box opens. If Data Source
is not already set to Oracle Database Server (Oracle ODP.NET),
click Change.
In the Change Data Source dialog box, select Oracle
Database Server as the Data Source and Oracle Data Provider for
.NET as the Data Provider. Click OK.
|
| 5. |
Enter your Data source name. This
is the SID of the Oracle instance you want to use.
Enter hr
for the User name and Password, click Save password,
select Default as Role, and click Test connection.
Test connection was successful. Click OK.
|
| 6. |
Click OK.
|
| 7. |
The HR.ORCL connection has been created and
added under Data Connections in the Server Explorer panel.
|
Back to Topic List
Now you create a new table called DEPENDENTS which has a foreign
key with the EMPLOYEES table.
Back to Topic List
Create the Columns
To create the columns in the table, perform the following:
| 1. |
Expand HR.ORCL. Right-click Tables
and select New Relational Table...
|
| 2. |
Enter DEPENDENTS
for Table name and click Add in the Columns section.
|
| 3. |
Enter FIRSTNAME
for the Name, select VARCHAR2 for the Data type and enter
30 for the Size. Then click Add.
|
| 4. |
Enter LASTNAME
for the Name, select VARCHAR2 for the Data type and enter
30 for the Size. Then click Add.
|
| 5. |
Enter BIRTHDATE
for the Name, select DATE for the Data type. Then click
Add.
|
| 6. |
Enter RELATIONSHIP
for the Name, select VARCHAR2 for the Data type and enter
20 for the Size. Then click Add.
|
| 7. |
Enter EMPLOYEEID
for the Name, select NUMBER for the Data type, deselect
Allow null and enter 6 for the Precision and
0 for the Scale. Then click Add.
|
| 8. |
Enter DEPENDENTID
for the Name, select NUMBER for the Data type, deselect
Allow null and enter 6 for the Precision and
0 for the Scale. Then click Preview SQL.
|
| 9. |
Here is the SQL that will execute when the table is
created. Click OK.
|
| 10. |
Click Save.
|
| 11. |
The Output window shows execution messages. The DEPENDENTS
table has been created. Close the Output window.
|
| 12. |
Click the + in front of DEPENDENTS table in the
Server Explorer panel. You can view the columns that have been created
in this topic. Now you are ready to create the index.
|
Back to Topic
Create the Index
To create the index for the table, perform the following:
| 1. |
Click the Indexes tab.
|
| 2. |
Click Add.
|
| 3. |
Enter DEPENDENTS_INDX
for the Name and click Add in the Index Keys area.
|
| 4. |
Select DEPENDENTID for
the Key and then click Save.
|
| 5. |
The Output window shows execution messages. The DEPENDENTS_INDX
index has been created. Close the Output window.
|
Back to Topic
Create the Foreign Key
To create the foreign key for the table, perform the following:
| 1. |
Click Constraints tab.
|
| 2. |
Click Add.
|
| 3. |
Enter EMPLOYEES_FK for the Name, select Foreign Key for the type, select EMPLOYEES for the table, and select the constraint EMP_EMP_ID_PK. Select EMPLOYEE_ID as the Referenced Column and EMPLOYEEID as the local column. Then click Add.
|
| 4. |
Enter DEPENDENTS_PK
for the Name, select Primary Key for the type, and click
Add in the Primary key columns area.
|
| 5. |
Select DEPENDENTID for
the primary key column. Then click Save.
The output window shows the execution messages.
Close the Output window.
|
| 6. |
Click the + in front of Indexes and Constraints
in the Server Explorer panel. You can see the table, its indexes and constraints.
|
Back to Topic
You can access the data in a table by performing the following
| 1. |
Right-click DEPENDENTS table and select Retrieve Data...
|
| 2. |
Enter at least 4 lines of data as shown in the table
below. Make sure you enter a valid Employee ID. When done, right-click the current tab and click Save.
|
FIRSTNAME
|
LASTNAME
|
BIRTHDATE
|
RELATIONSHIP
|
EMPLOYEEID
|
DEPENDENTID
|
|
Mary
|
Martin
|
06-MAY-80
|
daughter
|
104
|
9999
|
|
Sue
|
Littlefield
|
12-JUL-68
|
daughter
|
130
|
8888
|
|
David
|
Griffiths
|
02-APR-97
|
son
|
104
|
7777
|
|
Aaron
|
Young
|
31-AUG-99
|
son
|
111
|
6666
|
|
Back to Topic List
In this topic, you perform the following:
Create
a Visual C# Windows Application
To create a Visual C# Windows application, perform
the following:
| 1. |
Assuming you did not exit Visual Studio, from File menu,
select New > Project.
|
| 2. |
From the Project types list, select Visual
C#: Windows. Select the Template Windows Application.
You can enter an appropriate name for the application
in the Name field. Optionally, in the Location field, enter the
directory where you want to save the files. (Note: if the directory does
not exist, it is created).
For now, accept the default name and click OK.
|
| 3. |
A Form1.cs [Design] tab is added.
|
Back to Topic
Create a new Data
Source
You use the DEPENDENTS table you created in the
HR schema, for this application. To create a new data source, perform the following
steps:
| 1. |
From Data menu, select Show Data Sources.
A Data Sources panel is added.
|
| 2. |
In the Data Sources panel, click Add New Data Source...
Data Source Configuration wizard opens.
|
| 3. |
Select Database as the Data Source type. Click
Next.
|
| 4. |
In the previous topic, you already created a data connection,
HR.ORCL.Select HR.ORCL.Select Yes, include sensitive
data in the Connection string option. Click Next.
|
| 5. |
Accept the default selection for saving the connection string to the
application configuration file. Click Next.
|
| 8. |
Now, you select the database object you want in your data set. Expand
Tables. Expand DEPENDENTS. Click the checkbox beside DEPENDENTS.
A check mark appears beside the table name as well as its attributes.
Also note the name of the data set.
Click Finish. The Dependents table gets added in the Data Sources
panel.
|
Back to Topic
Designing the
Form
To design a form, perform the following steps:
| 1. |
Now, you work in the Form1.cs[Design] tab. Resize the
form as required.
|
| 2. |
In the Data Sources panel, expand DEPENDENTS.
Drag DEPENDENTS to the Form1 canvas.
A grid of column names of the DEPENDENTS table gets
added. You can resize the grid as required.
|
| 3. |
Double click the Save icon (  ).
Note: If the icon is not enabled, right-click the icon and select
Enabled.
Form1.cs code window opens.
In the Form1.cs code window, examine the
private void dEPENDENTSBindingNavigatorSaveItem_Click(object sender, EventArgs
e)procedure.
|
| 4. |
Add a try { } and a catch { } block for the three lines
of code. Also, add a MessageBox statement as shown below: (the statements
in bold are the ones you need to add)
try
{
this.Validate();
this.dEPENDENTSBindingSource.EndEdit();
this.dEPENDENTSTableAdapter.Update(this.dataSet1.DEPENDENTS);
MessageBox.Show("Update successful");
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed");
}
|
Back to Topic
Running the Application
To run the application, perform the following
steps:
| 1. |
From Build menu, select Build WindowsFormApplication1.
Ensure there are no errors reported in the output window.
|
| 2. |
From Debug menu, select Start Debugging.
Form1 shows the Dependents tables data.
|
| 3. |
Modify the DEPENDENTID of the record where FIRSTNAME
is 'Mary' to 5555. Click the Save icon.
A Message box displays with an "Update successful"
message. Click OK.
|
| 4. |
Delete the employee record with FIRSTNAME as 'Sue'.
Click the Delete icon.
Click the Save icon. A Message box displays with
an "Update successful" message. Click OK.
Close Form1.
|
Back to Topic
Back to Topic List
Now you create a stored procedure and run it.
Back to Topic List
Create a Stored Procedure
| 1. |
From the Server Explorer panel, right-click Packages
and select New Package...
|
| 2. |
Accept the default Package name and click Add under Methods.
|
| 3. |
Enter GETCURSORS for the method name, select Procedure for the method type and click Add under Parameters.
|
| 4. |
Enter MAXROWS for the Name and click Add.
|
| 5. |
Enter EMPLOYEESCUR for the Name, select OUT for Direction, and select SYS_REFCURSOR for Data type. Then click Add.
|
| 6. |
Enter DEPENDENTSCUR
for the Name, select OUT for the Direction and select
SYS_REFCURSOR for the Data type. Click OK.
|
| 7. |
Click Preview SQL >> to see the SQL that will be executed.
|
| 8. |
Review the SQL and click OK.
|
| 9. |
Click OK to create the Package.
The output window shows the execution messages. Close
the Output window.
|
Back to Topic
Edit the Package Body
| 1. |
The Package is created and the Package Body is opened.
If you do not see the code that is generated for the Package Body, right-click
PACKAGE1 Package in the Server Explorer and select
Edit Package Body. Scroll down to find the line NULL;
|
| 2. |
Replace the line NULL;
with the following:
OPEN EMPLOYEESCUR FOR SELECT * FROM EMPLOYEES;
OPEN DEPENDENTSCUR FOR SELECT * FROM DEPENDENTS;
|
| 3. |
Right-click the window tab and select Save.
The output window shows execution messages.
Close the Output window.
|
Back to Topic
Run the Stored Procedure
| 1. |
Expand the Package PACKAGE1 in the Server Explorer panel. Right-click GETCURSORS and select Run.
|
| 2. |
Enter 999 for the MAXROWS parameter value and click OK.
|
| 3. |
Select the value for EMPLOYEESCUR.
|
| 4. |
The details for the EMPLOYEESCUR
are shown. Select the value for the DEPENDENTSCUR.
|
| 5. |
The DEPENDENTSCUR details are shown. Close the
Output window.
|
Back to Topic
The query window allows you to run one or more
queries against data in your database. Perform the following steps:
| 1. |
Right-click your database connection HR.ORCL
and select Query Window.
|
| 2. |
Enter the SQL command SELECT
* FROM DEPENDENTS; and click Execute.
|
| 3. |
Your results are shown. To format the results, right-click
in the query area, select Set Query Output > Text and
click Execute again.
|
| 4. |
The results are shown in text format.
|
| 5. |
You can execute more than one SQL statement at a time.
Add the SQL command SELECT * FROM EMPLOYEES;
In order for both statements to be executed at the same time, you need
to select both statements, then click Execute. Note:
if both statements are not selected, only the last SQL statement in the
list will run.
|
| 6. |
Click Yes to run the query.
|
| 7. |
Scroll up to the top of your output. You see the data
from both queries.
|
Back to Topic List
In this lesson, you learned how to:
 |
Create
a table, an index and constraints. |
|
View a tables data |
|
Automatically generate code |
|
Create and run a stored procedure
|
|
Use a Query Window |
Back to Topic List
Move
your mouse over this icon to hide all screenshots.
|
Bookmark
