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.
