Online SQL Formatter for ORM generated SQL

With the recent introduction of so many ORM’s (Object-relational mapping) tools onto the market it is becoming less and less needful to write SQL statements. Tools like NHibernate, Entity Framework and SubSonic to name a few will take your database mappings and write an optimized query for you. However, during development, testing or production support you may need to look at the SQL query that these tools are writing for you. In most cases, these ORM tools can be configured to write logs which contain the SQL query that was constructed. These queries, however, are generally unformatted and hard to read. So I write here a possible solution for this problem. You should be able to reuse this code within a debug tool or simply format the SQL right here.

Ex: SQL from an ORM

[sourcecode language="sql" padlinenumbers="true" autolinks="false" gutter="false" toolbar="false"]
SELECT TABLE1_.NAME AS COL_0_0_, 
	TABLE1_.ADDRESS AS COL_1_0_, 
	TABLE2_.NAME AS COL_2_0_,
	TABLE3_.NAME AS COL_3_0_,
	TABLE4_.NAME AS COL_4_0_, 
	TABLE5_.NAME AS COL_5_0_ 
FROM 	TABLE1 TABLE1_, 
	TABLE2 TABLE2_, 
	TABLE3 TABLE3_, 
	TABLE4 TABLE4_, 
	TABLE5 TABLE5_ 
WHERE 	TABLE1_.COLUMN1_ID=TABLE2_.ID 
	AND TABLE1_.COLUMN2_ID=TABLE3_.ID 
	AND TABLE1_.COLUMN3_ID=TABLE4_.ID 
	AND TABLE4_.COLUMN1_ID=TABLE5_.ID 
ORDER BY TABLE1_.NAME
[/sourcecode]

What I do is really simple and easy. I take the above SQL and pass it to the below C# method.

[sourcecode language="csharp" autolinks="false" gutter="false" toolbar="false"]
public static string FormatSQL(string unformattedSQL)
{
    string NHSQL = unformattedSQL.ToUpper();
 
    string newSQL = NHSQL.Replace("SELECT", "SELECT\n\t");
    newSQL = newSQL.Replace("FROM", "\nFROM\n\t");
    newSQL = newSQL.Replace("WHERE", "\nWHERE\n\t");
    newSQL = newSQL.Replace("=", " = ");
    newSQL = newSQL.Replace(",", ",\n\t");
    newSQL = newSQL.Replace(" AND", " AND\n\t");
    newSQL = newSQL.Replace(" ON", "\n\t\tON");
    newSQL = newSQL.Replace("INNER JOIN", "\n\tINNER JOIN");
    newSQL = newSQL.Replace("ORDER BY", "\nORDER\t BY");
    newSQL = newSQL.Replace("GROUP BY", "\nGROUP\t BY");
    newSQL = newSQL.Replace(" AS", " \tAS");
 
    return newSQL;
}
[/sourcecode]




Download the source.

Leave a Comment

Your email address will not be published.