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

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

  • Learn How A Global Entertainment Company Saw a 448% ROI Every business today uses software to manage systems, deliver products, and empower employees to do their jobs. But software inevitably breaks, and when it does, businesses lose money -- in the form of dissatisfied customers, missed SLAs or lost productivity. PagerDuty, an operations performance platform, solves this problem by helping operations engineers and developers more effectively manage and resolve incidents across a company's global operations. …

Most Popular Programming Stories

More for Developers

RSS Feeds