CodeProject: SQL Formatter Web Service. Free source code and programming help

Report Article

Discuss

Send to a friend

8 votes for this Article.

Popularity: 1.34 Rating: 1.48 out of 5

Note: This is an unedited contribution. If this article is inappropriate, needs attention or copies someone else's work without reference then please Report This Article

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 has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Tako.Lee

Location:

China

Other popular Web Services articles:

Web Service Dispatcher
A simple web service to manage other web services
Your first C# Web Service
An introduction to writing your first WebService
Webservice sample - example of using SOAP protocol to access Internet Database Server
Use SOAP to communicate with a remote DB Server (Here I use SQL Server) on the Internet and get information or interact with the database
Using Web Services for Remoting over the Internet.
This article describes a design and implementation (C#) of the Remoting over Internet using the Web Service as a gateway into the Remoting infrastructure.
IFS - an Internet File System implementation based on Web services and peer-to-peer technology
Internet File System from scratch - making web services and peer-to-peer technology work together to build a virtual file system