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

Oracle is commonly using database as a backend for .NET applications. But, the main problem with oracle is, there is no in-built easy-to-use GUI for accessing objects in 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 definition etc...

But, it's not possible to get license for those tools for small projects. Even if you get license for them, it is very hard to open these tools for small activities (like getting all tables, procedure's definition and for running simple select statements...). These tools takes more time to start, consumes more resources and its not based on threads. So, we can't do multiple operations in these tools at a time like getting 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.
 

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 as DBExplorer.

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



Figure 1.

Here, I am using OLEDB Data Provider and tnsnames file to connect to Oracle Database. We can use ODP.NET Data Provider to improve performance of this application. On clicking Login button, I am creating a connection using username, password and data source 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.ora file. After creating connection, I am closing this login form and opening new form named frmdbexplorer to explore oracle objects.

 

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



Figure 2.

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

 

I designed MainMenu with 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 this menuitems, I have to explain little bit about Data Dictionary in Oracle.

 

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

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

 

Normally in oracle, objects are classified as:

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

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

 

Execute Query will just execute select statement written in textbox of 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 application. So, we no need to rewrite any query, which is written already 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 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 definition into textbox and another tab page (Execute Query) will execute the query written in textbox and displays its result in 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. We can continue with doing another operation like loading all tables belonging to your account simultaneously. So we can do multiple tasks at a time, which is not possible in TOAD.

 

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

 

I am using following queries to get objects from 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, final output will be like this:

 

Figure 3.

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

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