Click here to Skip to main content
11,920,413 members (36,901 online)
Click here to Skip to main content
Add your own
alternative version


68 bookmarked

An Oracle Query Tool (part 1)

, 23 Feb 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
An Oracle query tool with syntax highlighting and a tabbed MDI interface.

Sample screenshot


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.

Sample screenshot texteditor

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 = _

    texteditorQuery.Document.HighlightingStrategy = _

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.

Sample screenshot texteditor

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 _
    ThreadQuery.IsBackground = True
End Sub

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

Private Sub BindDatagridHandler()
    Dim oDataset As DataSet = oOracleReader.Dataset        
    If Not oDataset Is Nothing Then            
        DataGridView1.DataSource = oDataset.Tables(0)
    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.



  • 23rd February 2006 – First submission.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Danilo Corallo
Software Developer (Senior)
Italy Italy
I am 35 years old and I've been working with C++, Visual Basic .NET, C# and ASP.NET. I have a large experience in Industrial Automation solutions, but I've worked also as Web developer and DBA. I like to share knowledge and projects with other people.

You may also be interested in...

Comments and Discussions

QuestionInsert Query? Pin
trivedijignesh25-Dec-13 7:36
membertrivedijignesh25-Dec-13 7:36 
QuestionNice post Pin
Tridip Bhattacharjee12-Dec-13 22:58
memberTridip Bhattacharjee12-Dec-13 22:58 
GeneralMy vote of 5 Pin
pradiprenushe27-Aug-12 0:34
memberpradiprenushe27-Aug-12 0:34 
Generalupdates Pin
alhambra-eidos12-Jan-11 4:45
memberalhambra-eidos12-Jan-11 4:45 
GeneralUse of code . . . Pin
Posduif20-Aug-07 3:51
memberPosduif20-Aug-07 3:51 
GeneralRe: Use of code . . . Pin
Danilo Corallo20-Aug-07 4:00
memberDanilo Corallo20-Aug-07 4:00 
GeneralPerformance question Pin
Tom Wright7-Jul-07 7:31
memberTom Wright7-Jul-07 7:31 
GeneralRe: Performance question Pin
Paul Chevalier9-Jul-07 4:32
memberPaul Chevalier9-Jul-07 4:32 
GeneralRe: Performance question Pin
Tom Wright10-Jul-07 6:54
memberTom Wright10-Jul-07 6:54 
GeneralAdding a search and replace facility Pin
Paul Chevalier4-Jul-07 22:49
memberPaul Chevalier4-Jul-07 22:49 
GeneralRe: Adding a search and replace facility Pin
Paul Chevalier9-Jul-07 4:28
memberPaul Chevalier9-Jul-07 4:28 
GeneralRe: Adding a search and replace facility Pin
Tom Wright10-Jul-07 6:56
memberTom Wright10-Jul-07 6:56 
GeneralText Changed Event Pin
Paul Chevalier19-Jun-07 6:09
memberPaul Chevalier19-Jun-07 6:09 
GeneralRe: Text Changed Event Pin
Danilo Corallo21-Jun-07 0:37
memberDanilo Corallo21-Jun-07 0:37 
GeneralRe: Text Changed Event Pin
Paul Chevalier21-Jun-07 6:12
memberPaul Chevalier21-Jun-07 6:12 
GeneralAnother question - clsOracleReader Pin
Tom Wright11-Jun-07 9:52
memberTom Wright11-Jun-07 9:52 
GeneralRe: Another question - clsOracleReader Pin
Danilo Corallo11-Jun-07 11:57
memberDanilo Corallo11-Jun-07 11:57 
GeneralSettings Pin
Tom Wright9-Jun-07 10:12
memberTom Wright9-Jun-07 10:12 
GeneralRe: Settings Pin
Danilo Corallo9-Jun-07 10:21
memberDanilo Corallo9-Jun-07 10:21 
GeneralRe: Settings Pin
Tom Wright9-Jun-07 10:55
memberTom Wright9-Jun-07 10:55 
QuestionPart II Pin
jimkeating14-Feb-07 12:38
memberjimkeating14-Feb-07 12:38 
AnswerRe: Part II Pin
Danilo Corallo21-Feb-07 3:42
memberDanilo Corallo21-Feb-07 3:42 
GeneralOquery deployment Pin
CesareBrizio30-Nov-06 0:23
memberCesareBrizio30-Nov-06 0:23 
GeneralRe: Oquery deployment Pin
Danilo Corallo30-Nov-06 1:52
memberDanilo Corallo30-Nov-06 1:52 
GeneralRe: Oquery deployment Pin
CesareBrizio30-Nov-06 3:19
memberCesareBrizio30-Nov-06 3:19 
GeneralRe: Oquery deployment Pin
Danilo Corallo30-Nov-06 5:23
memberDanilo Corallo30-Nov-06 5:23 
GeneralRe: Oquery deployment Pin
CesareBrizio30-Nov-06 7:02
memberCesareBrizio30-Nov-06 7:02 
QuestionHook up? Pin
Mikael Håkansson19-Oct-06 20:25
memberMikael Håkansson19-Oct-06 20:25 
AnswerRe: Hook up? Pin
Danilo Corallo28-Oct-06 6:32
memberDanilo Corallo28-Oct-06 6:32 
GeneralCool and useful little tool Pin
Eric Engler17-Jul-06 11:50
memberEric Engler17-Jul-06 11:50 
General[Message Deleted] Pin
toxcct23-Feb-06 4:06
membertoxcct23-Feb-06 4:06 
General[Message Deleted] Pin
Danilo Corallo23-Feb-06 5:17
memberDanilo Corallo23-Feb-06 5:17 
GeneralRe: not bad... Pin
toxcct23-Feb-06 5:28
membertoxcct23-Feb-06 5:28 
Joke[Message Deleted] Pin
Danilo Corallo23-Feb-06 5:33
memberDanilo Corallo23-Feb-06 5:33 
GeneralRe: not bad... Pin
toxcct23-Feb-06 5:36
membertoxcct23-Feb-06 5:36 
GeneralRe: not bad... Pin
Cleavitt7628-Feb-06 6:19
memberCleavitt7628-Feb-06 6:19 
GeneralRe: not bad... Pin
toxcct28-Feb-06 6:22
membertoxcct28-Feb-06 6:22 
GeneralRe: not bad... Pin
Cleavitt7628-Feb-06 6:28
memberCleavitt7628-Feb-06 6:28 
GeneralRe: not bad... Pin
toxcct28-Feb-06 6:31
membertoxcct28-Feb-06 6:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151120.1 | Last Updated 23 Feb 2006
Article Copyright 2006 by Danilo Corallo
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid