{"id":3471,"date":"2022-04-22T23:06:34","date_gmt":"2022-04-23T07:06:34","guid":{"rendered":"https:\/\/www.gudusoft.com\/?p=3471"},"modified":"2022-04-22T23:13:36","modified_gmt":"2022-04-23T07:13:36","slug":"sql-formatter-web-service","status":"publish","type":"post","link":"https:\/\/www.gudusoft.com\/de\/sql-formatter-web-service\/","title":{"rendered":"SQL Formatter-Webdienst"},"content":{"rendered":"

Updated Info (2022)<\/h3>\n

This is an article written back in 2006 that was published on codeguru<\/a> , and the technology used in this article may out of date. If you are looking for a good SQL formatter that can be used in your Java or Dotnet program, please check this page<\/a> instead. If you are looking for a free while fantastic SQL query formatter, please check the Instant SQL formatter.<\/a><\/p>\n

Introduction<\/h3>\n

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\u2019s 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.<\/p>\n

Using the Code<\/h3>\n

A Web service at www.gudusoft.com<\/a> 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.<\/p>\n

  <wsdl:operation name=\"beautifySql\">\r\n   <documentation >\r\n      Beautify the sql\r\n   <\/documentation>\r\n   <wsdl:input message=\"tns:beautifySqlSoapIn\" \/>\r\n   <wsdl:output message=\"tns:beautifySqlSoapOut\" \/>\r\n<\/wsdl:operation> <\/code><\/pre>\n
Step 1: Build the proxy class<\/h4>\n
Executing the following \u2026<\/p>\n
C:>wsdl.exe http:\/\/www.gudusoft.com\/SQLFormatterWebService.wsdl<\/code><\/pre>\n
\u2026 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:<\/p>\n
public string beautifySql(string dbvendor, string sql)\r\ndbvendor: SQL dialect of which database supports MSSQL, Oracle,\r\n          MySQL, and Access currently.\r\nsql:      SQL code needs to be formatted.<\/code><\/pre>\n
Step 2: Compile the proxy class<\/h4>\n
Next, you have to compile the auto-generated file. The file contains no entry point and thus has to be built as a library.<\/p>\n
C:>csc \/t:library c:SQLFormatterWebService.cs<\/code><\/pre>\n
This results in a new file, SQLFormatterWebService.dll.<\/p>\n
Step 3: Create an ASP.NET application<\/h4>\n
This application, sqlformatter.aspx, consumes the SQL Formatter Web Service.<\/p>\n
<%@Assembly Name="SQLFormatterWebService" %>\r\n\r\n<html>\r\n   <head>\r\n      <title>Demo of SQL Formatter Web Service <\/title>\r\n   <\/head>\r\n\r\n   <body>\r\n    <form runat="server" action="">\r\n          <asp:textbox id="inputsql" text="select f1,f2 from t1"\r\n                       textmode="MultiLine" rows="10" columns="60"\r\n                       wrap="False" runat="server"\/>\r\n   <br><asp:button text=" Format Code " onclick="onFormat"\r\n                          runat="server" \/>\r\n          <br><asp:textbox id="outputsql" textmode="MultiLine"\r\n                           rows="10" columns="60" wrap="False"\r\n                           runat="server"\/>\r\n\r\n   <input type="hidden" name="trp-form-language" value="de"\/><\/form>\r\n   <\/body>\r\n<\/html>\r\n\r\n<script language="c#" runat="server">\r\n   void onFormat (Object sender, EventArgs e)\r\n{\r\n      SQLFormatterWebService sqlformatter =\r\n         new SQLFormatterWebService();\r\n      outputsql.Text =\r\n         sqlformatter.beautifySql("mssql",inputsql.Text);\r\n}\r\n<\/script><\/code><\/pre>\n
Step 4: Set up this ASP.NET application on your IIS server<\/h4>\n