An Oracle Query Tool (part 1)

• Download source files - 484 Kb
• Download binaries only - 444 Kb

Introduction

This is a small tool for creating and executing queries. The main purpose of this article is to create an Oracle query tool with syntax coloring. This article is written using the Visual Basic 2005 Express Edition, and requires Microsoft NET Framework v2.0 to run.

Using the DockPanel suite

The user interface will be an MDI container. To improve the ability of the developer to work with multiples queries, the project will use the beautiful component from Weifen Luo called DockPanel. You can retrieve the latest version of component here: SourceForge.

First of all, you'll need to have the component stored somewhere on your computer. I like to keep components in the source directory of my projects. After that, we need to add the component to our Toolbox. Right click on the Toolbox, and press Choose Items.... Select the component using the Browse button.

We can now insert the component into a newly created form. To save some time, you can create your own form using the template MDI Parent Form that already contains some basics interfaces for MDI projects. Right click on your solution in the Solution Explorer, and select Add > New Items and select the MDI Parent Form template. Call the form 'frmMain'.

We can now proceed and put the component into our form. Finally, remember to "Dock" the component to the form. Our parent form is now ready to display tabbed MDI childs.

Creating a query form

We now need to create the child form. Create a standard form and edit the source code as follows:

Imports WeifenLuo.WinFormsUI
Public Class frmQuery
    Inherits DockContent
End Class

The Visual Basic IDE will raise an exception because a class cannot inherit from more than one base class. Select from the Error Correction Options, the entry Change class so 'frmQuery' inherits from 'WeifenLuo.WinFormsUI.DockContent'.

We are finally ready! Every child created will be automatically docked. For convenience, we'll add a ContextMenu to the right click event of the tab. This is done by setting one of the new properties of the form called 'TabPageContextMenuStrip' to a ContextMenuStrip.

We will also add two buttons, one for executing the query and one for aborting the query, called 'btnExecute' and 'btnAbort', respectively.

SQL Syntax Color

I was busy for a long time looking for something easy to use to include in this project. I finally looked at the open-source component ICSharpCode.TextEditor. This component is shipped together with the IDE #develop. You can retrieve the latest version of the component here.

To use the component, we must add it to the Toolbox as we did for the DockPanel.

We can now insert the TextEditor into our query form. I’m sad to say that the integration with the IDE of Visual Basic is not perfect. This causes some small problems during the filling of some parameters.

Finally, we can bind the component with an XML file containing the syntax highlight rules for Oracle SQL. The file is provided together with the project.

Imports ICSharpCode.TextEditor
Imports ICSharpCode.TextEditor.Document

Private Sub frmQuery_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load 

    Dim strPathXSHD as String = _
        My.Application.Info.DirectoryPath & "\res\"
    Dim provider As New FileSyntaxModeProvider(strPathXSHD)
    Dim manager As HighlightingManager = _
        HighlightingManager.Manager

    manager.AddSyntaxModeFileProvider(provider)
    texteditorQuery.Document.HighlightingStrategy = _
               manager.FindHighlighter("ORACLESQL")

End Sub

The component will do the rest! The syntax highlighter manager will highlight the text with the rules contained into the file OSQL.xshd. Feel free to edit the file and test different combinations of styles. In the end, the TextEditor has other cool features. It will show the line number on the left of the control and highlight the matching bracket.

Execute the query and show the result on a DataGridView

As a first step, for starting a project that wants to connect to Oracle, we have to add a reference to 'System.Data.OracleClient'. This can be done from the menu Project > Add a reference. All the operations on the database will be forwarded through a class called 'clsOracleReader'. To give the user an opportunity to break long queries, we will run the query on a separated thread. When the user clicks the button 'btnExecute', the following code will be executed:

Private oOracleReader As New clsOracleReader
Private ThreadQuery As System.Threading.Thread

Private Delegate Sub BindDatagrid()

Public Sub ExecuteQuery()
    AddHandler oOracleReader.QueryCompleted, _
               AddressOf QueryCompleted
    ThreadQuery = New System.Threading.Thread(AddressOf _
                             oOracleReader.ExecuteQuery)
    ThreadQuery.IsBackground = True
    ThreadQuery.Start(texteditorQuery.Text)
End Sub

Public Sub QueryCompleted()
    If Me.InvokeRequired Then
        Dim d As New BindDatagrid(AddressOf _
                     BindDatagridHandler)
        Invoke(d)
    Else
        BindDatagridHandler()
    End If
    ThreadQuery = Nothing
    oOracleReader.Clear()
End Sub

Private Sub BindDatagridHandler()
    Dim oDataset As DataSet = oOracleReader.Dataset        
    If Not oDataset Is Nothing Then            
        DataGridView1.DataSource = oDataset.Tables(0)
        DataGridView1.Show()
    End If
End Sub

The form will start the execution on a new thread and the object will raise an event at the end of the execution of the query. This event will be handled by the method QueryCompleted(). This method will execute the bind with the DataGridView. Because the DataGridView was created on a different thread, we will need to use a delegate and the function Invoke() to set the binding of the object.

Final Notes

I hope that you find this article useful. If you found this article stupid, annoying, incorrect, etc., express this fact by rating the article as you see fit. I intend to extend this software with auto complete feature and other improvements, and I hope to be back soon with more articles.

References

• DockPanel suite by Weifen Luo
• ICSharpCode.TextEditor from the #develop project
• QueryCommander, the SQL editor

History

• 23^rd February 2006 – First submission.