Automated Conversion of SQL Queries to Type Safe Parameterised SQL



  1. Set up a project and/or load the correct project.

  2. Select an Output Type (SqlDataAdapter, SqlCommand or Sql Parameter Array). This determines the parameter format.

  3. If the output type is Sql Parameter Array, optionally enter a name for the parameter array. (If you leave this empty, the default name is P.)

  4. Copy and paste the code to be converted into the Sql Editor pane. Generally you should copy the entire sql statement including surrounding quotes. If the output type is SqlCommand or SqlDataAdapter, copy the line of code that sets the CommandText or SelectCommand of the SqlCommand or DataAdapter.

    Examples of Code that can be Pasted into the Editor

    This example includes the name of the SqlCommand object. This allows fix SQL Editor to detect the correct name and use it in the output. Code

    This is the same query above but this time, only the sql statement has been included. This would be appropriate if the output type is SqlParameter and there is no need for fix SQL Editor to detect the name of a SqlCommand object or SQLDataAdapter object. Code

    This example shows a more complex sql statement that includes 2 queries concatenated together.

    Code

  5. Click Run F5. fix SQL Editor will connect to your database to determine the data type for each parameter, then will parameterize the query. If there are any issues with the output, see Troubleshooting Processing

  6. If you want to change the names given to any of the parameters, you can do so using the Find Replace button on the Sql Output pane.

  7. If you want to check the SQL statement in the output for errors, select just the commandText in the Sql Output pane and click SQL Parser. Note this can occasionally give false positives so use your programming skills to determine if there is a genuine issue. This can save you a lot of time as the parser will reliably find syntax issues like missing commas.

  8. Use the copy button on the Sql Output pane to copy the final result before pasting it back into your source code. Don't bother selecting the text, the copy button will do this for you.

Important Info

This application doesn't rely on string manipulation to process Queries although it does utilise some regex matching and other string manipulation this is not the core method for processing SQL. The system relies heavily on SQL Tokens. This technology is a fantastic enabler for the procedure however it comes with a couple of drawbacks. These are listed below

When the SQL is tokenised it will split variables into separate tokens. For example, Agent.Item("ID").ToString isn't one token it's a list of token id's Agent . Item ( " ID " ) . ToString. This is only important when it follows a WHERE, VALUES or AND etc so they are parameter values. A comprehensive list of multi part variable patterns are handled within the application however it is possible that a pattern is found which is not handled. If this happens you will see the conversion fail and it is obvious that a multi part variable has been broken up in the results. The fix is simple, just enclose the variable with a single quote making it a literal. The application will correctly write it out as a variable in the results.
For example, when a developer creates a WHERE clause string out of scope of the sql query call and concatenates this string into the query SQL text. Just copy the code that assembles the string into the editor with the query. Don't worry about coding rules just copy it to the top of the editor and follow with the query. Be sure to hit the Load Tables button to identify Tables, views and Aliases in the code. Now use the Manual Processing method to create parameters or you can use Manual Exclusion of code which means you can still Auto Process the code. Manual Exclusion is simple just select the text you want excluded from processing and press F8. The selection will now have a light red back color indicating it has been listed as excluded. If code is edited during processing these selected indicators will take account of any changes in position.
Helper classes use unpredictable method signatures. For example a Helper class might have a connection variable which is passed in to a method, eg dbConnection. These are often recurring in code so it's important to add these to the 'Words to Ignore in Processing' list accessed through the Project Menu wizard. Since there is no way fix SQL Editor can see the method signature this example of a connection string variable passed in will be assumed to be part of the query text as a variable which will fail processing. The issues described here can easily be fixed by either adding the string to the ingored strings list or you right click and select to ignore this string in this query only in which case it won't be processsed but it will be written unchanged into the results pane.

Contact Support

Community Forum