An Oracle Query Tool, Part 1

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 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 find the article here. You can retrieve the latest version of Weifen component here.

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

You now can 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 basic interfaces for MDI Projects. Right-click on your solution into the Solution Explorer and select Add > New Items and select the MDI Parent Form template. Call the form 'frmMain'.

You now can put the component into your form. Finally, remember to "Dock" the component to the form. Your parent form is now ready to display tabbed MDI children.

Creating a Query Form

You 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 'frmQuery' to inherit from 'WeifenLuo.WinFormsUI.DockContent'.

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

You will add also two buttons, one to execute the query and one to abort the query, called 'btnExecute' and 'btnAbort' respectively.

SQL syntax color

I looked a long time for something easy to use to include in this project. I finally went into 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: http://www.icsharpcode.net/.

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

You now can insert the TextEditor into your 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, you can bind the component with a XML file containing the syntax highlight rules for Oracle SQL. The file is provided 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 OSQL.xshd file. 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.

An Oracle Query Tool, Part 1

Execute the Query and Show the Result on a DataGridView

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

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 query's execution. This event will be handled by the QueryCompleted() method. This method will execute the bind with the DataGridView. Because the DataGridView was created on a different thread, you will need to use a delegate and the Invoke() function to set the object's binding.

Final Notes

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

References

History

  • 28rd February 2006: First submission


About the Author

Danilo Corallo

I am 26 years old and I'm working with C++ and Visual Basic .NET. I have a large experience in Industrial Automation solutions, but I've worked also as Web developer and DB Administrator. I like to share as much as possible knowledge and projects with other people.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

  • Mobile is introducing sweeping changes throughout your workplace. As a senior stakeholder driving mobile initiatives in your organization, you may be lost in a sea of technologies and claims from vendors promising rapid delivery of applications to your employees, customers, and partners. To help explain some of the topics you will need to be aware of, and to separate the must-haves from the nice-to-haves, this reference guide can help you with applying a mobile strategy in the context of application …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds