Enhanced Oracle Explorer in C# and .NET using Windows Forms

• Download source - 109.7 KB

Introduction

Oracle commonly uses databases as a backend for .NET applications. But, the main problem with Oracle is, there is no built-in, easy-to-use, GUI for accessing objects within it. So the only option is to use third party tools like TOAD or PL/SQL Developer for accessing objects without writing queries, for activities like getting all tables, procedures, functions and their definitions, etc. But, it's not possible to license those tools for small projects. Even if you license them, it is very hard to open these tools for small activities (like getting all tables, procedure's definitions and for running simple select statements). These tools take more time to start, consume more resources and are not based on threads. So, we can't do multiple operations in these tools. For example, getting a procedure's definition and executing a query simultaneously.

By using this application we are just a click away from accessing those database objects. I will explain the features present in it followed by design and coding.

I designed this application using C#, VS.NET 2003 and Windows Forms.

The following are the features present in this Application:

• Easy access to Tables, Procedures and Functions.
• Entirely based on threads.
• We can do multiple activities simultaneously.
• Ability to export table data to xml file.
• Less startup time when compared to TOAD.
• Executes any select statements without blocking user from doing other operations in application.
• Ability to cancel any thread at any point of time.
• Ability to maintain list of recent queries executed in application.
• Shows objects which are accessible to current login account.

Create a new Windows Application in C# and name it DBExplorer.

Then rename Form1 to frmlogin and add the following controls shown in below figure:

Here, I am using OLEDB Data Provider and tnsnames file to connect to the Oracle Database. We can use the ODP.NET Data Provider to improve the performance of this application. By clicking the Login button, I am creating a connection using username, password and data source which will be in this format:

(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(
    HOST = <host-name>)(PORT = 1000)))(CONNECT_DATA =(SID  = dbname)));

I am getting this data source string from my tnsnames.oraM file. After creating a connection, I am closing this login form and opening a new form named frmdbexplorer to explore Oracle objects.

Now, add a new form to the solution - name it frmdbexplorer and design it as shown below:

I will explain each control present in this form followed by its purpose:

I designed MainMenu with the following menuitems:

View Objects of -> ALL [MINE] -> Tables à Full Details (will load table name, its row count, column name and its size, type and default value into TreeView).

-> Partial Details (will load just table name and its row count into TreeView).

-> Procedures (Loads all Procedure names into Dropdown).

-> Functions (Loads all Functions into Dropdown).

Before explaining these menuitems, I have to explain a bi about the Data Dictionary in Oracle.

Data Dictionary is a set of tables containing information about all objects like tables, views, PL/SQL Procedures, and triggers created by each user.

For example, USER_TABLES contains all tables that belong to the current login account. Similarly, ALL_TABLES contains tables that you can access through your account and DBA_TABLES contains tables that are accessible to a user with DBA privileges.

Normally in oracle, objects are classified as

• Objects accessible to you (tables starting with USER_).
• Objects accessible to all accounts (tables starting with ALL_).
• Objects accessible to DBA (tables starting with DBA_).

So by clicking the proper menuitem you can get the objects of your choice.

Execute Query will just execute select statements written in the textbox of the Execute Query tabpage and returns the result in a dataset. Finally, this dataset is binded to DataGrid Control to display results of Query.

Export to XML will export selected Table's data into an XML file and displays it in IE.

Recent Queries will maintain the list of queries executed by you in an application. So, there is no need to rewrite any query, if it has already been written once.

Cancel Current -> Loading Tables will stop the thread that is loading all tables in Treeview.

Cancel Current -> Loading Objects will stop the thread that is loading all procedures and functions into DropDownlist.

In the left side of the form, there is a Treeview control which will load all tables. Next to it, there is a tab control having two tab pages. One tab page (Selected Item Defn) will load selected function/procedure definitions into textbox, and another tab page (Execute Query) will execute the query written in textbox and display its result in the DataGrid control present below the textbox.

The main logic behind this application is, whenever you select an operation like executing a time-consuming query, we will just create a separate thread to handle that operation. And, we can continue doing another operation like loading all tables belonging to your account simultaneously. So we can do multiple tasks at the same time, which is not possible in TOAD.

All the controls present in this form are created by a main thread. So, it's not possible for the other threads to update those controls. In order to achieve this, we are using the Invoke() method along with delegates.

I am using the following queries to get objects from a database:

Select table_name from +type +_tables
Select distinct name from +type +_source where type like 'PROCEDURE'

where type can be of USER_ or ALL_ for getting list of tables and its 
properties.

So, the final output will be like this:

We can still enhance this application by including access to other database objects like sequences, indexes, etc.

I am attaching code to this for further reference. I hope this code will be useful for all.