Ez SQL, An easy to use query editor with many useful features

• Download source and setup files - 5.19 MB

Introduction

A few years ago, when I began to program as a professional, a coworker (actually he taught me a lot, he was like my "sensei" =)), had an application to query against Microsoft SQL Server, it was really helpful had some intellisense for tables, alias and fields, also code highlighting and few others functionality, it was really helpful, in that time we were using SQL Server 2005, so the management studio had no intellisense and was pretty plain (IMHO).

Inspired by his application (although I took some of his code, mostly to get the database objects schema and scripts), I did a first a attempt to make an application like his, and well it was fine, and then I did another attempt, and then another, the last 2 attempts where pretty good actually (in fact my friend asked me how I did certain things to include those in his query editor, that made me really proud), and well this is the fourth time I have written this application.

Since the previous version of this application I was pretty impressed by the design of the web browser opera, so I tried to use a design similar, to maximize the work space in the application.

This application uses several DLLs, which can be found all in code project (except the “SmartGrid” control which is a custom control I made, also like this project that grid is based in some articles found in CodeProject, maybe one day I will make an article for this control too), see the references section for details.

Note: this article is quite large, it is about How to use (not including any code in it, because it would make this article just unreadable being so big as it is) a custom application to query against a Microsoft SQL Server, you can watch a preview of some the features of this application in this video, this way you can decide if you want to read the whole article or not before hand.

Main Window

This form is a borderless form, was made this way to be able to maximize the working space. To drag, minimize, maximize, restore or close; the top right buttons must be used.

To resize the window must use the bottom right button.

Top controls in main window

Database connection selector

Control to navigate through connection groups, and select a database connection

Refresh connection

Request for all the database items of the selected database connection and loads/reloads all the snippets.

Add database connection

This button opens a pretty straight forward dialog to manage the database connections.

Copy connection string

A click in this button will copy to the clip board the connection string of the selected database connection

Query form

This window is has two main parts, the query editor and the results tabs.

Query editor

The query editor is a ICSharp.Texditor (SharpDevelop) with a tool bar and a context menu assigned. The control used (ICSharp.TextEditor), is a control with a lot of nice features (check the references), like syntax highlighting, code completion, bracket matching, code folding, etc..

Syntax Highlighting

To define the syntax highlighting the Text editor loads an XML file (“Exec dir \SintaxHighLight\SQL.xshd”), this file is based on the XML file that the application “Mini SQL Query” uses, I added some rules and modified the colors used. Feel free to play with it if you want to change the highlighting colors or add new rules to the highlighting; if this file is deleted on the next execution will be generated again.

Autocomplete

To fire the autocomplete must press “Ctrl + space” or “Ctrl + .”.It will show a dropdown with all the schemas, tables, views, functions and procedures found in the current database. As you press keys it will filter the objects to match the filter string. If fired after a "." it will try to identify the parent (can be an alias for a table, view or table function) to show his fields

Snippets

To fire a snippet completion you must input the shortcut and then press the “tab” key, depending on the content of the snippet you will be asked to select a database object and his fields or parameters depending on the tags.

Tool bar

From left to right:

• Execute: Creates a thread that executes the selected/whole script in the editor
• Stop execution: Tries to stop the execution thread by setting a flag value to cancel the execution, this may not work if the server has not responded, if the server already responded this cancel would show partial results (if any).
• Extreme stop: The thread created to execute the is aborted, so responsiveness is regained.
• Comment lines: Insert “--” at the beginning of the current or selected lines.
• Uncomment lines: Removes “--” at the beginning of the current or selected lines.
• Search: Shows the search/replace dialog.
• Toggle bookmark: Add/remove a bookmark to the current line.
• Previous bookmark: Moves the cursor to the previous bookmark in the script.
• Next bookmark: Moves the cursor to the next bookmark in the script.
• Clear bookmarks: Removes all the bookmarks of the documents.
• Save: Open dialog to create a file with the current script on it.
• Open: Open a dialog to select a file to be read and loaded into the current query form.
• Show/hide results: Shows or hides the results of the query execution.

Context menu

• Go to definition: Shows the script of the object below the cursor.
• Reserved words – To upper case: Tokenize the script and the reserved words detected are converted to upper case.
• Reserved words – To lower case: Tokenize the script and the reserved words detected are converted to lower case.
• Selection – To upper case: Converts the current selection to upper case.
• Selection – To lower case: Converts the current selection to lower case.
• Outlining – Collapse: Collapse all the outlinings detected in the script.
• Outlining – Expand: Expand all the outlining detected in the script.
• Outlining – Toggle: Expand the collapsed outlining and collapse the expanded outlining.

Results Tab

The results tab is generated on every execution, showing 1 tab for each “select” in the script executed and 1 other tab with the execution messages. Each result grid has several features, on right click on any column caption you should get this menu:

As you must noticed, it is on Spanish language, this is a custom control I made long ago, and to rewrite it to change (and bug fixing) the text is out of the scope of this article, so ill give a brief explanation every option (top to bottom), hopefully you can memorize the functionality of each option or you can read some Spanish =).

• Export grid to file: Opens a save dialog to create a file the data in the grid, for XLS and HTML the conditional formatting is preserved (check the references to see how the XLS file is created).
• Conditional formatting: Opens a dialog to define conditional formatting (font color and background color) for the rows in the grid.
• Search in table: Opens a dialog to search a value in the grid.
• Summarize column: Shows a dialog to pick an operation to apply in the column clicked, the result is shown in the column caption.
• Filters: Shows controls in the grid to allow filtering (this is based on the article of Robert Rhodes, see references for details).
• Preview/Print: Shows a preview of the data in the grid, ready to print (see references for details).

Search Form

This form will help you to search an object in the database.

Animated wait text

In this control you must introduce the text to search, it will execute the search after a second after edition, so even if the database has many objects should have a good performance.

Object type

In this combo you define what or were to search in the database.

Search results

Shows the results of the search.

Fields and parameters

If an object is selected in the search results, here If an object is selected in the search results, here the fields/parameters of the object will be shown. The fields/parameters will be shown.

Historic form

Pretty straight forward, shows a grid with all the query executed in the application, with the info of the execution, duration, server, database, script, error messages (if any).

Snippet editor

In this form you can add/edit/delete the snippets of the application.

Fields

• Name: the name of the snippet
• Description: A brief description of the snippet
• Shortcut: The text that must used to insert the snippet in the query form.
• Script: The text that must be inserted when the snippet is activated. In here can be defined prompts to the user, like which object should be inserted and what fields or parameters.

How to define an object prompt

• The label $OBJ:"id"$ defines a prompt to the user of a database object.
• The label $OBJF:"id":"separator"$ defines a prompt to the user for the fields of the database object with Id = "Id".
• The label $OBJF*:"id":"separator"$ defines that all the fields of the database object with Id = "Id", must be inserted.
• The label $OBJP:"id":"separator"$ defines a prompt to the user for the parameter of the database object with Id = "Id".
• The label $OBJP*:"id":"separator"$ defines that all the parameters of the database object with Id = "Id", must be inserted.

I feel like this might be a little hard to get (mostly because me, not knowing the best way to explain this), but you can check the predefined snippet in the application and how to use them, also you can check this video.

References

I will separate the references in two parts, the direct references which are DLLs or code I got from some other article and helped me to create this app and the indirect references (references of the custom grid control) which are references to articles which helped me create a custom control that I use in this application

Direct references

ICSharp.TextEditor

An amazing control to handle syntax highlighting, intellisense, line numbers, bracket matching, code folding, etc.

It is part of the IDE SharpDevelop in the old versions, the new version uses a new control named “AvalonEdit”, which is WPF control, so I stuck to the old version (I am not versed on WPF).

You can check this article which shows some basic usage of the control.

Also here is the download of the IDE if you are interested

A big thank you to Daniel Grunwald and the SharpDevelop team for this great control.

WeifenLuo Docking suite

A really nice docking suite, so easy to use and beautiful UI, this made the app look really good.

Thanks to Weifen Luo for this simple, yet beautiful suite.

You can check this article which shows some basic usage of the control.

Also you can download the latest version of the suite here.

AddressBarExt

This control is on top of the main form, is the bread crumb bar where you select the database connection, in which you will be working.

When I started this application one of my objectives was to make it simple to use and maximize the working space, thanks to this control I could use a borderless form to achieve that.

I modified the original the code to fit my needs, since in the original article is used to navigate through directories, but mostly is the original code.

You can check the original article here.

Thanks to “jwraith” for this useful control.

SimplePopup

This control shows the progress when reading the objects of the database, is a nice and simple to use control.

You can check the original article here.

Thanks to Lukasz Swiatkowski for this simple and nice control.

CustomXmlSerializer

In the previous version of this application I serialized to /deserialized from XML, but I used XmlDocument, so is not that simple/straight forward, in this version I changed some objects structure but dindt want to do again the serialization/deserialization code again, found this article and well after a few tests I was good to go, to serialize anything to XML =).

It is so easy to use, and I barely touched the code to fit my needs (just a small code change and used the main functions as extensions just to look even nicer =)).

You can check the original article here.

Thanks to Antoniu-Gabriel Rozsa for this useful code.

Indirect references

Grid filtering

Robert Rhodes made and article about a DataGrid (some time later a DataGridView) with filtering capabilities, just amazing, the custom controls in this app is the third version, and in the previous version I did the code for filtering but is not even close to what Robert achieved. So I admitted my defeat =), and used Robert’s code.

You can check the original article here.

Thanks to Robert Rhodes for that amazing grid.

Creating “XLS” files

Creating a “XLS” file (a file to be opened with Microsoft Excel, not necessarily a file with “XLS format”), can be done in a lot ways, writing the data as an html table, using excel automation, writing a CSV file, but when I did the last version of the grid control the best way I found was XML.

An article of written by Yogesh Jagota, shows a beautiful and simple to use library to create xml files to be read with Microsoft Excel, this files can use the most common features of the xls filed, cell merge, cell/row/column formatting, freeze columns/rows, text orientation, etc., so I used this library in my grid custom control.

You can check the original article here.

Thanks to Yogesh Jagota for this great library.

Inspiration for UI

As I said before the custom grid used in this application has been rewritten 3 times, the first 2 versions of it has a toolbar instead of an embedded context menu, this article changed my mind, gave me an excellent idea of how to handle the extra features of the grid without wasting space.

You can check the original article here.

Thanks to Vincenzo Rossi for the nice and inspiring UI.

Grid’s data preview and printing

This was the last feature I added to the custom grid, and well I have used it just a few times, but still is quite a nice feature to have.

You can check the original article here.

Thanks to Alessandro Lentini for a great article.

Conclusion

I worked a lot in this app, and this version I tried to translate my code and comments in the source code to English, I hope it will be as useful to you as it has been to me.

Right now I am beginning to work in a new project with LINQ, lambda expressions and Entity Framework, so I wont be able to work on this for a while, but if you like this application and have a suggestion and/or a bug to report, please feel to do so, whenever I have some free time, or switch back to a project with SQL Server scripts I will work on it and update this article.