Partners
Download
BuyMS SQL Maestro online Help
| Prev | Return to chapter overview | Next |
DML procedures generation
MS SQL Maestro allows you to create DML (also known as CRUD) procedures automatically. CRUD is an acronym for the four essential database operations: Create, Read, Update, and Delete. The application designer has many choices for accomplishing the CRUD operations but the most efficient choice in terms of Microsoft SQL performance is to create a set of stored procedures to perform the operations.
The reasons for using DML Procedures instead of allowing ad hoc SQL statements are:
| • | The best possible performance |
After the first use of each stored procedure, the plan for executing the procedure is cached in the server's procedure cache. For subsequent invocations of the stored procedure, the plan is reused. This avoids the parsing and optimization steps with their overhead.
| • | Removing of the SQL code from the other layers of the application |
By removing the SQL statements from the application code, all the SQL can be kept in the database.
| • | Preventing of SQL injection attacks |
Anytime a client application uses string concatenation to create SQL statements, there is a possibility of a SQL injection attack. In short, these attacks involve clever entry of SQL in the data entry fields of an application in such a way that the SQL statements executed are different from the ones intended by the programmer. They require that the application developer is careless about not cleaning any user input to prevent the attack.
| • | Preventing of casual table browsing and modifications |
If an application uses ad hoc SQL statements, the users of the application must have the required permissions on the database tables. Once they are given permission on the tables, they can work with them in any application that can read and manipulate the data such as Excel, Word and various report writers. Casual examination of the data and even updates that bypass the application's business rules become possible. Stored procedures have long been used to prevent casual browsing and updates. This is implemented by granting permission to execute the CRUD stored procedures to the users and revoking permission to access the tables directly.
To generate DML procedure,
| • | select the Object | Generate DML procedures... main menu item (to create procedures for several tables) or use the corresponding popup menu item of the table's node at the Explorer tree (to create procedures for one table). |
| • | Specify tables the procedures will be created for (in case of several tables). |
| • | Uncheck the operations the procedures will not be created for. By default the procedures are generated for inserting, reading, updating, and deleting of table data. |
| • | Adjust templates of procedures names. |
| • | Select the action to perform after the generation. The created definitions can be copied to Clipboard, saved to a file, sent to SQL Script Editor or executed immediately. |
| Prev | Return to chapter overview | Next |
