Manual processing can be used for particulary complex code that doesn't convert correctly using automatic processing.
Before you go ahead with manual processing, consider if you could use manual exclusion to exclude non sql code from the processing instead. To do this, just select non sql text in the SQL Editor and press F8 to exclude the selected text from processing. You can select text from multiple areas in the code. This method allows auto processing to be used for more complex conversions that include a lot of non sql code.
However for particularly complex sql statements, you may need to use manual processing. It's not as fast or simple as automated processing, but sometimes it's the best option to ensure accuracy converting complex code, particularly if you have batched queries or you need to combine method calls for SQL that is assembled in the source code.
-
Set up a project and/or load the correct project.
-
Select the Manual Process SQL option.
-
Select an Output Type (SqlDataAdapter, SqlCommand or Sql Parameter Array). This determines the parameter format.
-
Identify field/value pairs in the code. For each pair, highlight the field name (you can do this by double clicking on the field name) then press F9. Now find the matching value and select it by dble clicking the value which will select the concatenation surrounding the value. In the unlikely event that the dbl clicked values doesn't automatically select concatenation characters then manually select the value and any enclosing concatenation characters like single quotes, double quotes, etc then press F10. When you do this the value is changed to a parameter name in place in the editor so you can keep track of what has been done and what remains to be done.
-
When all of the pairs have been processed, press F5 or click the RUN button. The parameterized query will be output to the Results pane. If you find you missed a pair you can add them even after processing. Just select the pair in the sql editor (using the F9 F10 approach described above) and then press F5 or RUN again and the new parameter will be added to the result.
-
In a situation where the developer has assembled WHERE clauses into a single string you will need to include the code for the Method that assembles the string in the editor. You paste the query and the Method into the editor. Optionally you can click the load button on the treeview on the right to load all tables in the query to the list as this will enable autocomplete. Now you select pairs in the same way as descibed in point 1. The ability to load tables used in a query you paste into the editor is very usefull when you need to edit a query. Once the tables are loaded you will have autocomplete for schema's, tables, columns and ADO syntax making editing very much simpler and intuitive.
-
Finally, the results command bar button called SqlParser can be used to parse the pure SQL part of your code. Highlight the commandtext for the query then press the SqlParser button to see a report on the query syntax. False positives are possible here use your programmer skill to identify the validity of any errors detected. To copy the query preparatory to pasting back into the source code click the Copy button.
The Find Replace button will open a search dialogue. You can use this to search the query text however it's real job is to replace and refactor innapropriately named parameters. For eg. a date parameter could be named @01/01/2019. The Replace functionality in the dialogue can edit all instances of this parameter name to @DateVal, for example.