SQL Formatter Web Service

Sample Image - SQLFormatterWebService.gif

Introduction

SQL code is much easier and faster to read and understand when it is formatted, especially when it is a long and complicated SQL statement you are dealing with, or you have to read other peoples code. Writing a decent SQL formatter is not an easy task. In this article, I would like to create an online SQL formatter in C#, this consuming application send unformatted SQL to the server which will return the formatted SQL to the application.

Using the code

A web service at www.gudusoft.com which accepted SQL code, and returns formatted SQL. Take a look at the WSDL here. The important part is shown below, which describes the beautifySql method.

    <wsdl:operation name="beautifySql">
      <documentation xmlns="http://schemas.xmlsoap.org/wsdl/">Beautify the sql</documentation>
      <wsdl:input message="tns:beautifySqlSoapIn" />
      <wsdl:output message="tns:beautifySqlSoapOut" />
    </wsdl:operation>

Step 1: Build the proxy class
Executing the following...

C:>wsdl.exe http://www.gudusoft.com/SQLFormatterWebService.wsdl

... results in C# file, SQLFormatterWebService.cs which contains a class called SQLFormatterWebService. Examine how the methods of this class correspond to those detailed in the WSDL file, especially the following method:

public string beautifySql(string dbvendor, string sql)
dbvendor: SQL dialect of which database, support mssql,oracle,mysql and access currently.
sql :  SQL code need to be formatted.

Step 2: Compile the proxy class
Next, we have to compile the auto generated file. The file contains no entry point, and thus has to be built as a library.

C:\>csc /t:library c:\SQLFormatterWebService.cs

Resulting in a new file SQLFormatterWebService.dll.

Step 3: Create an ASP.NET application
This application sqlformatter.aspx consume the SQL Formatter Web Service.

<%@Assembly Name="SQLFormatterWebService" %> 

<html>
  <head>
    <title>Demo of SQL Formatter Web Service </title>
  </head>

  <body>
   <form RunAt="server">
          <asp:TextBox ID="inputsql" Text="select f1,f2 from t1" TextMode="MultiLine" Rows="10" Columns="60" Wrap="False" RunAt="server"/>
        <br><asp:Button Text=" Format Code " OnClick="onFormat" RunAt="server" />
          <br><asp:TextBox ID="outputsql" TextMode="MultiLine" Rows="10" Columns="60" Wrap="False" RunAt="server"/>
            
  </form>
  </body>
</html>

<script language="c#" RunAt="server">
  void onFormat (Object sender, EventArgs e)
{
    SQLFormatterWebService sqlformatter = new SQLFormatterWebService();
    outputsql.Text = sqlformatter.beautifySql("mssql",inputsql.Text);
}
</script>

Step 4: Setup this ASP.NET application on your IIS server

4.1 Put the sqlformatter.aspx under wwwroot for example.
4.2 Put the SQLFormatterWebService.dll into the bin directory under wwwroot.
4.3 Open your browser, type in http://localhost/sqlformatter.aspx

Enjoy it!

Points of Interest

This SQL Formatter Web Service can be used widely, especially in forums of database related website, where many SQL code will be submitted by user for discussion, it would be nice if these SQL code can be formatted before post to the forum.

For further information, These sites were useful if you are interested in SQL formatter: