NET Refactor - Refactor Dynamic SQL to Stored ProcedureGet Rid of As Much Dynamic SQL As PossibleThe ultimate Refactoring of Dynamic SQL is to replace it with a Stored Procedure. NET Refactor can do that for you automatically. Additionally, it can generate a method that calls the new Strored Procedure, for which you would generate a call, probably from the original location of the Dynamic SQL. Optionally, it can generate in-line code that will call the Strored Procedure.
This feature works for both C# and VB.NET, but at the present, it only works with Stored Procedures for SqlServer.
Creating a Stored Procedure
The first step in converting Dynamic SQL into a Stored Procedure is to select a block of Dynamic SQL in the IDE Code Window, as shown in Figure 1. Next, select the Generate Stored Procedure menu option, found under the Sql Refactor menu of NET Refactor's main menu. When this menu option is invoked, the dialog, shown in Figure 2, will be displayed, and if you have selected a code block, it will be loaded as shown in Figure 2.
The code shown in Figure 1 is fairly convulated, and is simply a test block of code to demonstrate how badly Dynamic SQL can be coded. It in no way represents how Dynamic SQL, if it has to be used, should be coded.
Figure 1 - Selected Dynamic SQL.
Figure 2 - Stored Procedure Generation Dialog.
Once the dialog is displayed, you must enter your own Stored Procedure name. Check the appropriate options and click the Generate button. Clicking any of the option buttons or check boxes should cause the calling code to be regenereated dynamically. Figure 3 shows the Generated Stored Procedure that has been created from the selected Dynamic SQL.
Figure 3 - Generated Stored Procedure.
Figure 4 shows the method generated to call the Stored Procedure. You can click the respective language option that you would like to see generated, and the code will be generated in that language. This should not be required, since the dialog will know the type of code window from which the selection came. Generating code for another language will not be of much use and the code would not work if pasted into the wrong project.
You will want to copy this procedure to the clipboard by clicking the Copy button. Then you will go to the code window where you want the new calling procedure to be placed, and past the code.
Figure 4 - Calling Method.
Figure 5 shows the in-line code that will be generated if you select the option to generate in-line calling code. This code would normally be copied and pasted over the original Dynamic SQL selection.
Figure 5 - In-Line Stored Procedure Calling Code.
Note: If there is VB or C# code embedded in the Dynamic SQL, such as loops or decision making code, a Stored Procedure cannot be created from that Dynamic SQL. The selected block of code must simply be concatenating SQL and Variables to create a Dynamic SQL statement. At the present, the generator cannot handle code which is already using SqlParameters.
|
