A very simple Oracle query tool
Download
Introduction
While working on an Oracle XE project a while back, I developed a simple query tool to allow me to execute sql statements and view the results in a
DataGridView control. This functionality was already provided by Oracle APEX, a web application supplied by Oracle XE, which allowed me to enter Sql
statements to execute. However it did not list the table structure or table data, which would of helped, while constructing Sql statements. For example, if I
wanted to retrieve specific columns from a table, I would need to know what the columns were called. Some times I would forget what a columns was called or
how it was spelt, so I would have to go into the object browser and copy the column names. The SQL command interface does not list the tables or show you the
table structure. It only allows you to enter SQL statements, if the statement was a SELECT query, it would show you the results of the query.
At times I would need column names from multiple tables, to construct an Sql JOIN statement. For this reason, I developed a simple query application. The
application works in the following way. When logged in, a ListView control is populated when the "List Tables" button is clicked, showing the tables that
belong to the user logged in. By selecting a table name from the ListView control, you can view the structure of the table. By clicking the appropriate
buttons you can switch between structure view or data view. The structure view will show the data types for each column. By switching to data view, you can
see the data stored in each column. While constructing an SQL statement, you can easily view the table structure or the data. I have also included the
ability to List VIEWS. You can see the structure of a VIEW as well as the data the VIEW will produce.
Below is a few screen shots of the working program. Figure 1.1 below shows the login form. This form requires that you enter the data source name, user id
and passwrord.
Figire 1.1
After loggin in, you will be presented with the following form (Figure 1.2).
Figure 1.2
Figure 1.3
Figure 1.4
The Code
I have seperated the connection to the database code from the UI code. orcConnection Class contains a Connect() Method, which connects to the database.
Listing 1.1 below shows the Connect() method.
Listing 1.1
public static string Connect(string strDataSource, string strUserId, string strPassword)
{
string strConResult = "";
string orcConString = "Data Source=" + strDataSource + ";User Id="
+ strUserId + ";Password=" + strPassword + ";";
orcCon = new OracleConnection(orcConString);
try
{
orcCon.Open();
}
catch (OracleException orcEx)
{
strConResult = "An error occured: " +
orcEx.Message;
}
return strConResult;
}
|
The Connect() method, takes three arguments, a data source, user id and password all of type string. I have used a try/catch block to catch any errors. If any errors are produced, the error message is returned to the calling method, where it is displayed in a message box.
|
