Simple access to custom queries in the EPiServer CMS database
If you ever find yourself adding custom tables to the EPiServer database, or even just wanting to perform custom queries or running stored procedures on the standard tables, here’s one way of making your life a bit easier.
Create your data access class
Create a class inheriting from the abstract EPiServer.DataAccess.DataAccessBase class. That class contains helpers for most tasks you might want to perform, and better yet, it handles the connection to the EPiServer database so you won’t have to think about it.
Create your data access methods
Using the generic Execute method different tasks can be performed. The type argument of the Execute method specifies the return type, and takes a delegate performing your task and returning this return type. This is best illustrated by an example:
1:// This method executes the stored procedure customGetKeywordCount
2:// which gets the number of instances of a certain keyword in the
16:// Use helper method in DataAccessBase to create a data adapter and fill the dataset using it
It is also possible to call the Execute method without a type parameter, in which case it’s prepared to run ExecuteNonQuery like in the first example, but not using a return parameter (for running sproc:s that do create or update for example).
Do perform further abstraction of your data, don’t just pass datasets around you application.
Implement caching for your data access (these example methods should be protected and have a public method wrapping them and taking care of caching, etc.).
As always: Think about what data you pass to your methods from a security perspective. Use sproc.s/parameters but don’t rely simply on that but validate input data long before it can reach a database call.