Click here to Skip to main content
Click here to Skip to main content

An Oracle Query Tool (part 1)

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

Sample screenshot

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.

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

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

History

  • 23rd February 2006 – First submission.

License

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

About the Author

Danilo Corallo
Web Developer
Italy Italy
I am 29 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.

Comments and Discussions

 
QuestionInsert Query? Pinmembertrivedijignesh25-Dec-13 6:36 
QuestionNice post PinmemberTridip Bhattacharjee12-Dec-13 21:58 
GeneralMy vote of 5 Pinmemberpradiprenushe26-Aug-12 23:34 
Generalupdates Pinmemberalhambra-eidos12-Jan-11 3:45 
GeneralUse of code . . . PinmemberPosduif20-Aug-07 2:51 
GeneralRe: Use of code . . . PinmemberDanilo Corallo20-Aug-07 3:00 
GeneralPerformance question PinmemberTom Wright7-Jul-07 6:31 
I love this app. I've been working with Query reporter and while it's nice it lacks some things that I'm adding to your app. (Hope you don't mind)
 
So I have a design/performance question.
 
I've added in code to allow oquery to connect to different databases....currently MySQL and MS-SQL 2000 as well as Oracle... I'm wondering if I should move the database classes to a DLL and reference it so that I do not have to compile the whole program each time there is a change made or I want to add a new database type.....do you think this is a great idea or since there are really only a few database types keep them in the same namespace? I'm just wondering what type of design or performance hit you think I would see if I did this.
 
Some other things that I'm currently adding...
Exporting data to Excel, XML, HTML, and CVS formats.
Syntax highlighting for each type of database based on database choosen.
Allowing custom forms for values to be filled in at the time the query is run....this one may be a little more difficult but I think it can be done and will look cool.
Auto rollback on update, delete, insert errors.
 

Thanks again
 
Tom Wright
tawright915@gmail.com

GeneralRe: Performance question PinmemberPaul Chevalier9-Jul-07 3:32 
GeneralRe: Performance question PinmemberTom Wright10-Jul-07 5:54 
GeneralAdding a search and replace facility PinmemberPaul Chevalier4-Jul-07 21:49 
GeneralRe: Adding a search and replace facility PinmemberPaul Chevalier9-Jul-07 3:28 
GeneralRe: Adding a search and replace facility PinmemberTom Wright10-Jul-07 5:56 
GeneralText Changed Event PinmemberPaul Chevalier19-Jun-07 5:09 
GeneralRe: Text Changed Event PinmemberDanilo Corallo20-Jun-07 23:37 
GeneralRe: Text Changed Event PinmemberPaul Chevalier21-Jun-07 5:12 
GeneralAnother question - clsOracleReader PinmemberTom Wright11-Jun-07 8:52 
GeneralRe: Another question - clsOracleReader PinmemberDanilo Corallo11-Jun-07 10:57 
GeneralSettings PinmemberTom Wright9-Jun-07 9:12 
GeneralRe: Settings PinmemberDanilo Corallo9-Jun-07 9:21 
GeneralRe: Settings PinmemberTom Wright9-Jun-07 9:55 
QuestionPart II Pinmemberjimkeating14-Feb-07 11:38 
AnswerRe: Part II PinmemberDanilo Corallo21-Feb-07 2:42 
GeneralOquery deployment PinmemberCesareBrizio29-Nov-06 23:23 
GeneralRe: Oquery deployment PinmemberDanilo Corallo30-Nov-06 0:52 
GeneralRe: Oquery deployment PinmemberCesareBrizio30-Nov-06 2:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web04 | 2.8.140721.1 | Last Updated 23 Feb 2006
Article Copyright 2006 by Danilo Corallo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid