Using ACE edit

Introduction

I often create admin pages for the web sites I deploy to make life easier both for myself and (sometimes) the users. I have found it very useful to have a page that can parse sql and display the results in a grid view for those times when you need to see what is going on but don't have remote access to the database.

Background

I wasn't satisfied using a simple textbox: I wanted to have a syntax coloring editor with gutter and line numbers. I have seen these used on a number of web sites so knew that someone had already invented this particular wheel. All that was needed from me was a bit of research.

As it transpires there are a number of solutions that would have solved this for me; notably Code Mirror[^] looked like a good choice. However, I chose to go with ACE[^] since it appeared to be the simplest to use (after some trial and error).

Using the code

This is very simple; the attached files can be dropped into a project and, with some minor tweaking, should simply work (see next section).

The SQL.aspx expects to be housed within a Master page but can be easily extracted to a standalone page if required.

The pages were created in VS2012 but may load and build in prior versions of VS though I have not tested this.

I am not going to go through the code here as that is not really the point of the tip; suffice to say that it works (as example) and that you are free to use or discard as you see fit. (If there is any demand to do so I can expand this tip to an article and discuss the code).

Tweaks

Plainly, you will have to supply an appropriate database connection string - see line 52 of SQL.aspx.cs

You may have to alter some of the references to the master page and namespaces as well as the path to the ACE folder and the location of the SQL.css file.

Finally, you may have to alter the css to enlarge the fonts sizes as required. Note that you should only change the font family in SQl.css and you must use a mono-spaced font or it will look hideous.

Problems

There were 2 problems with this; one simple, one a little harder.

If you plan to use the sql code you type in you need to be able to parse it on the server side. To enable this to happen and to be able to validate I used a textbox that had the style set to <pre>display:none</pre>. This way it can still be validated but remain invisible to the user. There is a simple javascript function triggered by the submit button which synchronizes the text in the editor with the text in the textbox and it is that text that is parsed.

The second and more difficult was that the control must use a monospace font or it will appear that the cursor is running ahead of the text. This appeared to be a common theme in the few controls that I tried but was the easiest to fix in the ACE control. It required a line in the css file as:

.ace_editor, .ace_editor * { font-family: "Monaco", "Menlo", "Ubuntu Mono", "Droid Sans Mono", "Consolas", monospace !important; font-size: 1em !important; font-weight: 400 !important; letter-spacing: 0 !important; }

Regardless of what it says in the forums or elsewhere this is the only thing I could find to smooth out the font and, having done that, it appears to work very nicely.

Usage

You will need to download the files from ACE (see the link above). I have used the 'src-min-noconflict' folder so that you can open and read the files. Feel free to use the minified version and to remove files not pertinent to your needs.

As you can see, there are a number of themes and supported languages - you should experiment until you find what you require.

Fire up the page, type in some sql and off you go - if the sql is valid and you have a connection, you should see a grid of data.

Note that jquery is not required for this to work: it is standalone.

Conclusion

This is a simple sql parser: it is not designed to validate complex sql statements: if you need more than this offers then feel free to make any alterations required but do so at your own risk.

Note that it would be fairly simple to remove the code that ensures only select statements are run. Feel free but you should be aware that is a dangerous thing to do as you could delete production data.

YOU HAVE BEEN WARNED!!!

USE AT YOUR OWN RISK!!!