A Very Simple Oracle Query Tool

• Download source - 99.47 KB

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 have 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. Sometimes, I would forget what a column 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 are a few screen shots of the working program. Figure 1.1 shows the login form. This form requires that you enter the data source name, user id and password.

After logging in, you will be presented with the following form (Figure 1.2).

The Code

I have separated 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 occurred: " + 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.

Update

Previously there was no way of executing an INSERT/UPDATE/DELETE statement, but thanks to No_Namer and dadox for their comments, the query tool now returns a message to the user if an INSERT/DELETE/UPDATE statement was successful. It also produces any errors that might occur when constructing the SQL statement.