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.
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
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
C:\>csc /t:library c:\SQLFormatterWebService.cs
Resulting in a new file SQLFormatterWebService.dll. <%@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>
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:
First release 04-09-2006
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
