SQL Formatter Web Service

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 people's 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 sends unformatted SQL to the server, which will return the formatted SQL to the application.

Using the Code

A Web service at www.gudusoft.com accepts SQL code and returns formatted SQL. Take a look at the WSDL here. The important part is shown below; it 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 a C# file, SQLFormatterWebService.cs, that 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 supports MSSQL, Oracle,
          MySQL, and Access currently.
sql:      SQL code needs to be formatted.

Step 2: Compile the proxy class

Next, you 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

This results in a new file, SQLFormatterWebService.dll.

Step 3: Create an ASP.NET application

This application, sqlformatter.aspx, consumes 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: Set up 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 and type in http://localhost/sqlformatter.aspx.

Enjoy it!

Points of Interest

This SQL Formatter Web Service can be used widely, especially in the forums of a database-related Web site, where a lot of SQL code will be submitted by users for discussion. It would be nice if this SQL code can be formatted before it's posted to the forum.

For further information, these sites are useful if you are interested in a SQL formatter:



Downloads

Comments

  • Another SQL Formatter online service

    Posted by guidomarcel on 01/31/2007 06:11pm

    Hi, here is another online web servie for an sql formatter: http://www.sqlinform.com Regards GuidoMarcel

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Webinar on September 23, 2014, 2 p.m. ET / 11 a.m. PT Mobile commerce presents an array of opportunities for any business -- from connecting with your customers through mobile apps to enriching operations with mobile enterprise solutions. Join guest speaker, Michael Facemire, Forrester Research, Inc. Principal Analyst, as he discusses the new demands of mobile engagement and how application program interfaces (APIs) play a crucial role. Check out this upcoming webinar to learn about the new set of …

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds