Web SQL Utility

• Download source - 15.9 Kb
• Download demo - 62.1 Kb

Introduction

This little ASP.NET utility lets you run SQL queries against databases accessible to the web server. Initially, this started as a script to run queries against an Access database, without having to download it. Every now and then, you might need to do maintenance chores, or retrieve some raw data to check something. This utility facilitates that by allowing queries to be saved as a favourite, as well as keeping the history of previous queries.

Features

• Configure multiple connections (OLEDB, SQL Server, ODBC)
• Display multiple result grids
• Store query history (remembers target database)
• Save favourite queries
• Cross-browser compatible
• View schema for database, with clickable column names (replace selection in query textbox)
• Generate INSERT queries
• Download DataSet XML
• Download CSV

Details

This utility is not meant to replace database admin tools like phpMyAdmin. Rather, it was built to be simple and light-weight, allowing it to be integrated into other web applications. It's meant to provide quick and easy access to your data, and allow you to test queries, as well as modify your databases. You can configure multiple connections (not necessarily of the same database type), which gives you a common interface regardless of which database you need to query. The history and favourites-list remembers which database a query was executed against, and will select the appropriate radio button when you click on a query in those lists.

It also generates schema details for OLEDB, SQL Server, and MySQL connections. In the schema popup, you can click on the column names or table name headings to insert the name into the query textbox (replacing the current selection). The schema popup does not reload when you click the Schema link if that schema is already open in the popup. The popup will merely get focused to the forefront.

The Execute button, in addition to the obvious, adds the query to the history, and if the "Add to favourites" checkbox is checked, adds the query to the favourites. The shortcut key for this button is Alt-E. Multiple queries can be submitted simultaneously - separated by GO statements or ";;;" (customizable). Multiple result grids will be displayed. Note: the user-account that aspnet_wp runs as will need Modify permission on the configured paths for favourites and history (normally, ASPNET or INTERACTIVE_NETWORK).

The INSERTs button will generate INSERT SQL queries that you can execute against another database (e.g., for backing up a single table). If you use this feature with SQL Server, note that you should specify that you are overriding IDENTITY values, by running the following queries before and after the INSERT queries:

SET IDENTITY_INSERT table_name ON

--- INSERT QUERIES ---

SET IDENTITY_INSERT table_name OFF

The DataSet XML button will let you download the DataSet XML for your SELECT queries. Multiple result sets are supported. The tables are named Table1, Table2, Table3 etc., for each result set.

The CSV button will let you download a single CSV, with each result set underneath the other.

The HTML encode results checkbox escapes any HTML characters in your result set.

The Wrap query textbox checkbox changes the text-wrapping on the query textbox (i.e., horizontal scrollbar on/off for long lines). This was a major headache to implement for Firefox (v1.5) - which required coming up with a complete workaround since setting the wrap attribute on the TEXTAREA element just didn't have any effect. I assume this is a bug in Firefox, and is the first time I feel let down by this impressive browser. (More details on the workaround below.)

Initially, the SQL page shows the Favourites "tab" selected. But once a query is executed, the History "tab" becomes selected by default. They're not really tabs, visually, but the bold text shows which list is selected. The History list has a "Complete History" link underneath which will be shown queries executed for previous sessions in a popup. This is useful to keep a record of queries run by yourself or anybody else. If you suddenly have 1000 rows missing from your database, you can investigate the query history for the potential cause. The popup has delete links to clear a particular history session, or all of them.

Configuration

There is a custom configuration section that lets you customise this utility. Here is an example:

<DbUtilConfig>
   <Connections>
      <Connection name="Access" type="oledb" cs="" csAppKey="AccessDb" />
      <Connection name="SQL Server" type="mssql" cs="" 
                  csConfKey="SqlServerConnStringIAM" />
      <Connection name="MySQL" type="odbc" 
                  cs="DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;
                      USER=test; PASSWORD=test; DATABASE=test" />
   </Connections>
   <FavouritesFile>data/SqlFavourites.xml</FavouritesFile>
   <HistoryFolder>data/SqlHistory</HistoryFolder>
   <QuerySeparator>;;;</QuerySeparator>
   <ResultGridColors Border="#777" HeaderBg="#ffc" 
                     Header="#000" ItemBg="#fff" AltItemBg="#eee" />
</DbUtilConfig>

Connection

• The name attribute is the label displayed in the RadioButtonList control. This is also the key that is associated with history queries (so they remember which database they were executed against).
• The type attribute can have three possible values: oledb, mssql, odbc (case insensitive).
• The cs attribute specifies the connection string.
• If your connection string is already defined in Configuration.AppSettings, you can specify the key name in the csConfKey attribute.
• If your connection string is already defined in the Application variable, you can specify the key name in the csAppKey attribute.

FavouritesFile

Path to the XML file where favourite queries are stored.

HistoryFolder

Path to the directory where history XML files are stored.

QuerySeparator

This is the string used to delimit multiple queries. When executing, if there are any occurrences of this string, the query will be split with this string. If there are no occurrences of this string, the code will attempt to split the query with GO statements (e.g., for SQL Server scripts).

ResultGridColors

Lets you change the colour scheme of the result DataGrids. (By the way, #fff is equivalent to #ffffff).

• Border attribute - DataGrid border colour
• HeaderBg attribute - DataGrid header background colour
• Header attribute - DataGrid header text colour
• ItemBg / AltItemBg attributes - DataGrid item / alternating item background colour

Code

Snippet 1 (JavaScript)

The JavaScript code to replace the selection in the TEXTAREA with the column name is a fair bit simpler in Internet Explorer compared to that in Firefox:

if (document.all) // IE
   window.opener.document.selection.createRange().text = o.innerHTML
else // FF
{
   var selStart = queryTextArea.selectionStart
   queryTextArea.value = 
      queryTextArea.value.substring(0, selStart) + o.innerHTML +
      queryTextArea.value.substring(queryTextArea.selectionEnd)
   queryTextArea.selectionStart = selStart + o.innerHTML.length
   queryTextArea.selectionEnd = queryTextArea.selectionStart
}

As you can see, Firefox gives you access to the selection positions, not the selection itself. So you have to use string manipulation to create the new value. Then, I set the selection start/end to select the string that was just inserted.

Snippet 2 (C#)

Where possible, the System.Data interfaces were used, to support different database types. But in some places, the object had to be cast to its actual provider-specific type. Here is an example:

IDbConnection conn = conf.GetConnection(RblWhichDb.SelectedValue);
cmd = conn.CreateCommand();
cmd.CommandText = query;
conn.Open();

try
{
   IDataReader reader = cmd.ExecuteReader();

   bool hasRows = false;
   if (conn is OleDbConnection)
      hasRows = ((OleDbDataReader)reader).HasRows;
   else if (conn is SqlConnection)
      hasRows = ((SqlDataReader)reader).HasRows;
   else if (conn is OdbcConnection)
      hasRows = ((OdbcDataReader)reader).HasRows;

Snippet 3 (JavaScript)

As I mentioned earlier, a workaround was required for Firefox to toggle the wrapping of the query TEXTAREA. The strange thing is that Firefox does support the wrap attribute when rendering the page, but has absolutely no effect when setting this attribute with JavaScript.

This HTML gives you a TEXTAREA with no text wrapping (i.e., horizontal scrollbar for long lines):

<textarea wrap="false" rows="7" cols="80" id="TxtQuery">

But this JavaScript has no effect on the wrapping:

document.getElementById("TxtQuery").wrap = true

Nor does this:

document.getElementById("TxtQuery").setAttribute("wrap", "true")

I found this really peculiar, but I didn't give up there. I came up with a workaround to re-create the HTML, and assign it as the innerHTML of the parent element:

function toggleWrap(checked)
{
   if (window.navigator.userAgent.indexOf("Gecko") != -1)
   {
      // workaround for Firefox because setting
      // 'wrap' attribute just doesn't work :(
      var val = document.getElementById("TxtQuery").value
      var html = document.getElementById("TxtQuery").parentNode.innerHTML
      html = html.replace('wrap="off"', '')
      if (!checked)
         html = html.substring(0, html.indexOf('<textarea') + 9) + 
                ' wrap="off" ' +
                html.substring(html.indexOf('<textarea') + 9)
      document.getElementById("TxtQuery").parentNode.innerHTML = html
      document.getElementById("TxtQuery").value = val
   }
   else
      document.getElementById('TxtQuery').wrap = (checked) ? 'soft' : 'off'
}

It first gets the value of the TEXTAREA. It then retrieves the innerHTML string from the parent element, adds or removes the string "wrap="off"" from the TEXTAREA element, and then assigns it back as the innerHTML of the parent element. It then sets the value of the TEXTAREA. Nothing too involved... but compared to the single line for Internet Explorer, a bit of a headache.

By the way, I'm experimenting with omitting the optional semi-colons in my JavaScript code.

Snippet 4 (C#)

This ASP.NET application uses a custom configuration section in Web.config. You've probably encountered this before - it lets you put your own custom section in Web.config. All custom sections have to be declared at the top of Web.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <configSections>
   <section name="DbUtilConfig" 
            type="WebSqlUtility.ConfigHandler, WebSqlUtility" />
 </configSections>

The name attribute indicates the element name of the custom section. The type attribute indicates your .NET class to handle the custom section - i.e., "[namespace.classname], [assembly name]".

A simple configuration handler class would look like this:

public class ConfigHandler : IConfigurationSectionHandler
{
   public object Create(object parent, object configContext, 
                                       XmlNode sectionNode)
   {
      return sectionNode.ChildNodes[0].InnerText;
   }
}

Then you would access the value in your application as follows:

string val = (string)ConfigurationSettings.GetConfig("Blah");

where "Blah" would be the section name as defined in configSections.

Here is an excerpt from the configuration handler class for the DbUtilConfig custom section in this application:

XmlNodeList nodes = sectionNode.SelectNodes("Connections/Connection");
foreach (XmlNode node in nodes)
{
 string name = node.Attributes["name"].Value;
 string type = node.Attributes["type"].Value;
 string cs = null;
 if (node.Attributes["csAppKey"] != null)
    cs = (string)
          HttpContext.Current.Application[node.Attributes["csAppKey"].Value];
 else if (node.Attributes["csConfKey"] != null)
    cs = ConfigurationSettings.AppSettings[node.Attributes["csConfKey"].Value];
 else
    cs = node.Attributes["cs"].Value;

 if (type == "oledb")
    arrConnections.Add(new OleDbConnection(cs));
 else if (type == "mssql")
    arrConnections.Add(new SqlConnection(cs));
 else if (type == "odbc")
    arrConnections.Add(new OdbcConnection(cs));

 arrNames.Add(name);
}

Important notes

• Never deploy this without restricting access to authorized users. This app provides full access to your databases, so obviously you need to lock it down. The web.config included in the ZIP files denies anonymous users.
• The INSERT queries generated by the INSERTs button have not been tested with image column types. This scenario, and possibly others, might cause issues and need improving.
• If you want to prevent download access to the history and favourites XML files, you'll need to disable Directory Browsing and deny IUSR_xx access on the history folder and favourites file.

Future ideas

• Ability to add a note/description for a favourite.
• Button to toggle ScrollingGrid^ for a result grid.
• Ability to enlarge query textbox.
• Generate queries option to exclude primary key.
• Compress download XML/CSV as a ZIP file.

I'm not sure what kind of priority I can give to the future development of this app, but feel free to post any other suggestions and ideas below.

Conclusion

This has been yet another interesting adventure in solving browser issues and achieving common behaviour across the two major browsers (IE, Firefox). I hope either all or parts of this app are useful to others.