Kentico CMS Is an extremely flexible and customizable platform to develop on. I have frequently created new custom functionality to enhance and extend the platform to accommodate the needs of my projects. I wrote this blog to detail a new module I created to allow SQL Execution within the CMS Site Manager.
While Kentico provides a large amount of access to data and layouts, I found myself needing to update a stored procedure within the database. Without direct SQL Access, achieving that functionality is a cumbersome process of writing custom code to execute SQL, registering some web part or module within the site, and then accessing the code and executing the command. For that reason, I decided to create the Bit-Wizards SQL Execution module to allow quick access to executing a SQL command.
Overview
The Bit-Wizards SQL Execution module isn’t that entirely complex, but accomplishes a great deal in its simplicity. It allows any SQL text to be entered and executed against the main site database. The type of command to execute can be selected and the appropriate response will be displayed. All commands are executed against the main site database. By default, access to the module is limited to only Global Administrators. This is to ensure only the appropriate users can execute a command against the database (a gift few developers every get…).
Modules Module Base
NOTE
The commands are not validated in any way by the module! Users will need to be sure their commands are correct and valid, and that they don’t wipe out the database.
Types of Command
Currently there are 3 types of commands supported in the module.
- NonQuery
This type will execute a command with no response. The user will only see a message displaying that the command was executed.
NonQuery
- Scalar
This type will execute the command and return the first scalar value form the results.
Scalar
- Reader
This type will execute the command and return a dataset of the returned values. The dataset will be loaded into a data grid and displayed to the user.
Reader
All of the commands are executed within a transaction, using the ConnectionHelper API.
TIP
More information on the ConnectionHelper API can be found in the Kentico API reference
here.
Logging
All commands executed by the module are logged to the Event Log within Kentico. Included in the event log entry is the user name / id of the user, the type of command, and the actual command executed. This information is extremely helpful in determining what commands were executed and by whom.
Event Log Logged Event Error Event
NOTE
The actual command is included within the Event Log entry. Ticks (‘) are replaced with double ticks (‘’) in the command when the event is logged to prevent any SQL insert issues.
Errors
In the event the command generates an exception, a message will be displayed to the user, along with an Event Log entry.
Error
Want it?
The Bit-Wizards SQL Exception Module is very easy to incorporate into any 6.0 / 7.0 site. To add it to your site, just place the module export file in the CMSSiteUtils / Import folder and import. Because the module is only available to Global Administrators, no additional configuration will be required. Just go to the CMS Site Manager / Development section to access the module.
You can download the module
here.
Tip
Be sure to check "Import Files" when importing the module into your site so the source files get copied!