Using Oracle Developer Tools with Visual Studio

If you’re curious about the Oracle Developer Tools Beta (ODT) and how well it works with the Visual Studio 2003 IDE, this article gets you off to a reasonable start. It gives you a basic description of some of the product’s features and how they work.

Introduction

In February 2006, Oracle released the Oracle Developer Tools Beta (ODT) for Visual Studio .NET to be used in conjunction with the Oracle 10G Express Edition. Like Oracle 10G XE, this is a junior version of the same product used with the larger databases. For details follow the Oracle link in the next paragraph. This product works with Visual Studio 2003 (.NET FrameWork 1.1) but will not work with VS 2005. This basic article describes only some of the features of this product when used with Visual Studio 2003 IDE.

Downloading and Installing ODT

The ODT may be downloaded from this Oracle link. After the download it can be installed very easily as shown. When the executable is clicked the InstallShield installer starts installing. Here is the first screen.

When you click the Next button on this screen, the next step starts. Of course to proceed further you need to accept the license. Read the license and click on the Next button.

You indicate a destination folder or change to a desired folder by hitting the Change button. It is suggested that you take the default location. Click on the Next button.

The next picture shows the items in the installation directory.

{mospagebreak title=Accessing Oracle Explorer}

Downloading and installing the ODT adds the Oracle Explorer to the VS Studio IDE. This can be accessed by going from View–>Oracle Explorer as shown in this picture.

When you click on this link, the Oracle Explorer screen starts up at another location on the IDE as shown. It looks as if the connection is broken, but it is not.

When you click on the + symbol next to hr.XE, it expands the node showing the various objects in the Oracle 10G XE database.

For example you may expand the Tables node to reveal the various tables, views, etc as shown in the next picture.

{mospagebreak title=Reviewing the Connection Properties}

Connection is one of the most important properties. Some of the connection properties for the Oracle 10G XE database will be explored using a Windows form. A Windows Application is created with the name GetXeData as shown for this purpose.

Onto Form1, drag and drop the COUNTRIES table after highlighting it in the Oracle Explorer. This will not add anything visible to the form, but it adds the countriesOracledataAdpter1 icon to the tray below the form as shown.

After adding the above data adapter a window comes up with a message as shown.

If you accept and click the Yes button, the countriesOracleConnection1 icon will also be added. Now the form has enough data related information.

Add a number of textboxes, an equal number of labels and a command button as shown. You may format the form suitably.

Click open Button1 which will take you to the code behind the form as shown. Add the imports Oracle.DataAccess.Client statement at the top of this page as shown. To the click begin to add the code shown. You will get drop-down help to choose properties and methods as shown.

You can see that the imports statement has added a number of objects related to the countries table. You can not only select, but you can also manipulate data in the server using code. This tutorial will only show how the data is displayed.

 

{mospagebreak title=The Code Behind Form1}

The next paragraph shows the code behind Form1 in the click event of the command Button1 as shown.

You instantiate a new connection and set its value the same as the one provided by the ODT which you created by dropping the “Countries” table on the form. The rest of the code follows the properties of the Oracle connection which you may want to look up in the object browser. To know the server version, the connection has to be open. The open state of a connection has a value 1 and the closed state a value of 0.

When you build the project and click on the button, you will see the results for this connection as shown in the next picture. It is showing the password because we asked for it in an earlier screen

{mospagebreak title=Displaying Data from the Employees Table}

Add a form Data.vb to the same project, and drag and drop the Employees table from the Oracle Explorer to the data form. This adds the employeesOracleDataAdapter1 icon followed by the same previous message. When you click Yes to this message the employeesOrcleConnection1 will also be added. We will use the Oracle Data Reader object to read the EmployeeID, First Name, and Last Name and display them in the list box. For this purpose add a ListBox and a command button as shown.

In a manner similar to what was done with Form1, add an imports statement at the top of the data form’s code page. To the click event of the button, insert the code shown in the next picture.

The following sequence of code execution takes place when the command button is clicked: after instantiating a connection it will be assigned the employeesOracleConnection1. The connection  then opens. A new Oracle data adapter gets instantiated. It will be assigned to the employeesOracleDataAdapter1. This adapter’s select command will be assigned the SQL statement to get all fields from the Employees table. The OracleDataReader grabs the executed SelectCommand. While the data in the reader is being read, the ListBox gets populated. Before terminating this event, the reader and the connection are both closed. The display shown next appears after you click on the OK button to the message box, which says that the OracleDataReader does have data denoted as True by the Boolean.

Summary

Oracle Data Tools add a comprehensive set of tools to the Visual Studio IDE, only a very small subset was explored in this basic tutorial. The ODT gives full support for most of the activities that one can can do on the Oracle Server such as working with stored procedures, making changes to the tables, views, and so forth. Although the ODT are added to several versions of VS 2003, to exploit the full capability of this resource one may require the Professional or Enterprise version of VS 2003. The drag and drop feature adds to the RAD capability immensely. Although in this tutorial code was used to make a connection and display data, the same could be done during design time, reducing the code even more. The password was shown in the display for no good reason, but in a production scenario it is of course serious.

One thought on “Using Oracle Developer Tools with Visual Studio

  1. Yes, that is true. It took some time to write the article, but the whole mouse clicking here and there did not take more than a few minutes. It cannot be any easier than this. There were days when I was struggling with writing code to the oracle data control, but now, it is a different story. Drag and drop does it. Don’t forget to look up the Object Browser. My VS 2003 is standard Edition, probably one of the earliest builds and I may not be able to show you all the other things it can do, but I am sure this is as good a starting point as any.

    Please direct your comments and suggestions to this article blog.

    Thanks
    Jay

[gp-comments width="770" linklove="off" ]