Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a machine generated SQL code which is no good. I found some formatting tools online which helped me to tidy up and organise the code. But there are some unessary aliasing created by the machine generated code, so I need to delete blocks of code from the source script to get a decent SQL code. I am looking for some C# script that will delete blocks of code.

What I have tried:

I am a beginner I donno anything about C#. any help on this would be great
Posted
Updated 15-Jan-19 2:43am
Comments
Maciej Los 15-Jan-19 4:56am    
Are you talking about SQL parser?
CHill60 15-Jan-19 14:29pm    
Just a thought... if you know nothing about C# why on earth do you want a C# script?

You are very, very unlikely to find any C# code which will automatically "clean" SQL, I've never heard of any, and if it existed it would be expensive - it would require a considerable amount of work to produce, and the market is quite small. I'd certainly be looking to recoup at least some of the effort I put into producing it if I had, and releasing the source code for free would probably not be high on my list of priorities!

I suspect you will have to "knuckle down" and manually tune the SQL - or find an expert and pay him to do it as a "one-off" exercise.
 
Share this answer
 
Comments
Maciej Los 15-Jan-19 6:00am    
5ed!
Quote:
But there are some unessary aliasing created by the machine generated code, so I need to delete blocks of code from the source script to get a decent SQL code.

Trully? I don't believe you can clean up your SQL code (query) by removing aliases. Aliases are very important in SQL. See: SQL Server: ALIASES[^]. I prefer to use aliases, due to more human-readable form of sql code, for example:
Without aliases version:
SQL
SELECT [dbo].[DatabaseName1].[FirstTableName].[Column1],  [dbo].[DatabaseName2].[SecondTableName].[Column5], ...
FROM [dbo].[DatabaseName1] INNER JOIN [dbo].[DatabaseName2] ON [dbo].[DatabaseName1].[FirstTableName].PrimaryKey = [dbo].[DatabaseName2].[SecondTableName].ForeignKey
WHERE [dbo].[DatabaseName1].[FirstTableName].[Column2]='Whatever'

With aliases version:
SQL
SELECT A.[Column1],  B.[Column5], ...
FROM [dbo].[DatabaseName1].[FirstTableName] AS A INNER JOIN [dbo].[DatabaseName2].[SecondTableName]  AS B ON A.PrimaryKey = B.ForeignKey
WHERE A.[Column2]='Whatever'


My advice: Stop looking for tool, buy a book and learn about SQL!
 
Share this answer
 
v2
Comments
CHill60 15-Jan-19 8:21am    
"Stop looking for tool, buy a book and learn about SQL! " - Good advice! 5ed.
Maciej Los 15-Jan-19 13:18pm    
Thank you, Caroline.
[no name] 15-Jan-19 8:23am    
"Aliases are very important in SQL": I would say "Aliases are in some circumstances very important in SQL". A 5.

Btw: Trull, Trully should be more Truly I think :-)
Maciej Los 15-Jan-19 13:18pm    
Thank you, Bruno.
Further to the excellent advice in solutions 1 through 3 - the phrase you are looking for is "refactor" (Try using "SQL Code refactor" as a search term in your favourite search engine[^])

There are some free tools that might help - have a look here SQL Code refactoring - ApexSQL[^] - but as always, if something is free it may not be worth the cost!

There is plenty of advice out there e.g. Painless Refactoring of SQL Server Database Objects - Simple Talk[^] which is extracted from a book on the subject.

The Redgate tool(s) have been reviewed/talked about here on CodeProject:
Faster database development with SQL Refactor[^]
SQL Refactor – making order out of chaos[^]
Writing Readable SQL[^]
 
Share this answer
 
v2
Comments
Maciej Los 15-Jan-19 13:18pm    
5ed!
In my opinion, the best tool to clean up machine generated SQL is the human mind. And the best programs to do this in are the ones whose primary purpose is SQL; in particular SSMS (SQL Server Management Studio). Azure Data Studio is another option.

SSMS will allow multiple views of you query which can be quite helpful when renaming the aliases and the text editor portion can then be used so that you format into human-friendly text.

Something I highly recommend is to take the basic SQL statements (Select, Update, etc) and convert them into stored procedures. This will make the C# code appear cleaner as you only need to specify the Procedure name instead of all of the text. You can add parameters if needed for any variables that need to be included for the statement. Using parameters for this as opposed to piecing together SQL statements and variables is the best way to avoid SQL Injection vulnerabilities.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900