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

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Companies must routinely transfer files and share data to run their business, work with partners, and speed operations. However, many find the traditional approach to file transfer lacks necessary security, is too complex and difficult to manage, does not support the levels of automation needed, and breaks down when addressing the file transfer requirements of new areas like Big Data analytics and mobile applications. This QuinStreet SmartSelect discusses how the changing business environment is making the use …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds