New SQL addon for Episerver CMS

Episerver CMS is a content management system, and often it's part of a customer centric digital experience platform. Sounds great!

If we skip all the buzz words, I would say that the most important part of a CMS is providing a graphical user interface for the database. The database stores all your data, all your pages and blocks. It would probably be best to prevent your average editor from poking around directly in the database, as the CMS provides both some kind of safety net and improved usability. But you, as a developer, could maybe benefit from some extra powers? And maybe, just maybe, you don't need that safety net.

The database is pretty well abstracted away from both users and developers, but to really understand how Episerver CMS works - you should know the database too.

That's why I've created a SQL Studio addon for Episerver CMS.

Getting started

1. Install the NuGet package.

2. Create a group SqlAdmin and add yourself to that group.

A new «SQL» menu item will now show up in your global menu.

Executing queries

Type your query and click the «Execute query» button, or hit F5 like you would do in Microsoft SQL Server Management Studio.

Datatables is used for presenting the results, so you can toggle what columns to show, export to various file formats and search within the search results.

Check the button «Hide empty columns» to remove columns that are empty for all rows in the result set.

Auto complete aka Intellisense

CodeMirror is used for the editor window and enables auto complete on all tables in your Episerver database, both those default tables and any custom tables you might have.

Auto complete is triggered automatically, and you may trigger it manually with Ctrl+Space, like in Visual Studio.

Every time you enter (or auto complete) the name of a table, that table will become the «default table» and the column names of that specific table will be added to the auto complete list.

Saving your favorite queries

Constructing the ultimate SQL query can take some time, and you might need it again in the future, so you should be able to save it. Where to save it, if not in the database?

Simply create a new table, like this:

CREATE TABLE SqlQueries(
   Name varchar(256) NOT NULL,
   Category varchar(256) NOT NULL,
   Query varchar(2048) NOT NULL,
 CONSTRAINT PK_SqlQueries PRIMARY KEY CLUSTERED (Name, Category))

And add your favorite queries to this table. How to add them? Using SQL and this very addon, of course!

Example of four queries with the same Category.

INSERT INTO SqlQueries VALUES('Active jobs', 'Scheduled jobs', 'SELECT Name, IsRunning, LastExec, LastText, NextExec, TypeName, LastStatus FROM tblScheduledItem WHERE NextExec IS NOT NULL')

INSERT INTO SqlQueries VALUES('Clear running state', 'Scheduled jobs', 'UPDATE tblScheduledItem SET IsRunning = 0 WHERE IsRunning = 1')

INSERT INTO SqlQueries VALUES('Failed jobs', 'Scheduled jobs', 'SELECT Name, IsRunning, LastExec, LastText, NextExec, TypeName, LastStatus FROM tblScheduledItem WHERE LastStatus = 2')

INSERT INTO SqlQueries VALUES('Running jobs', 'Scheduled jobs', 'SELECT Name, IsRunning, LastExec, LastText, NextExec, TypeName, LastStatus FROM tblScheduledItem WHERE IsRunning = 1')

All queries will be displayed in the same dropdown list as other queries with the same category. Selecting a query from the list, will insert the actual query in the editor window.

If you add SQL queries with another category, another dropdown list will be shown.

Access control

The addon is only available for users in the group SqlAdmin. Other users will be blocked, and will not be able to see the addon's menu item or access it in any other way. The addon can also be completely disabled for specific environments by adding the following to your appsettings. If disabled by appsettings, the addon will not be available for users in the group SqlAdmin either.

<add key="Gulla.Episerver.SqlStudio:Enabled" value="false" />

A sefety net

You can control what queries are allowed by providing a regular expression that will be valdated (ignore case) against the query prior to execution. Provide a message that is shown if validation fails. Example below.

<add key="Gulla.Episerver.SqlStudio:AllowPattern" value="^\s*SELECT.*" />
<add key="Gulla.Episerver.SqlStudio:AllowMessage" value="Nothing but SELECTs please!" />

In the same way, you can also control what queries are denied by providing a regular expressions. Example below.

<add key="Gulla.Episerver.SqlStudio:DenyPattern" value="^.*DROP.*" />
<add key="Gulla.Episerver.SqlStudio:DenyMessage" value="No DROPing allowed!" />

Warning

With great powers comes great responsibility! This addon will indeed provide great powers. Delegate and use them wisely, and with caution. The addon should not be enabled for users you would not trust with full access to your database, and it is probably not wise to enable it in production. There is literally no limits to what you can do with this addon - unless you correctly configure those limits as described above.

If you are deleting or updating, I would strongly recommend you have a backup ready - and that you know how to restore it.