Click here to Skip to main content
Click here to Skip to main content

SQL Formatter Web Service

, 22 Aug 2010
Rate this:
Please Sign up or sign in to vote.
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:

History

First release 04-09-2006

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tako.Lee

China China
No Biography provided

Comments and Discussions

 
GeneralNo Web Service available in the link provided. PinmemberDineshSIS19-Aug-10 3:58 
GeneralRe: No Web Service available in the link provided. PinmemberTako.Lee22-Aug-10 16:37 
GeneralVisual Studio 2008 detects no services at this address ... PinmemberMuaddubby16-May-08 10:01 
GeneralSource Code for the Web Service Pinmemberhartmuth.lohmiller10-Apr-06 0:10 
GeneralRe: Source Code for the Web Service PinmemberTako.Lee10-Apr-06 0:41 
QuestionWhy??? Pinmemberjaxxter9-Apr-06 17:01 
AnswerRe: Why??? PinmemberTako.Lee9-Apr-06 17:57 
GeneralRe: Why??? Pinmemberjaxxter9-Apr-06 19:06 
GeneralRe: Why??? PinmvpEddy Vluggen23-Aug-10 10:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 22 Aug 2010
Article Copyright 2006 by Tako.Lee
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid