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
3: // EPiServer search index
4: public int CountDB(string keyword, string languageBranch)
5: {
6: // Call the execute method with a type argument specifying the
7: // return type, and a delegate which is executed to get the
8: // return value
9: return base.Execute<int>(delegate
10: {
11: // Use helpers in DataAcccessBase to create a command and add
12: //the in and out parameters
13: IDbCommand cmd = this.CreateCommand("customGetKeywordCount");
14: cmd.Parameters.Add(base.CreateParameter("keyword", keyword));
15: cmd.Parameters.Add(base.CreateParameter("languageID", languageBranch));
16: cmd.Parameters.Add(this.CreateReturnParameter());
17:
18: // Execute the sproc and return the return value (an integer)
19: cmd.ExecuteNonQuery();
20: return this.GetReturnValue(cmd);
21: });
22: }
Here’s an example of a different method/sproc which gets more data than a simple row count:
1: // Executes the customGetTopKeywords stored procedure returning the top x
2: // keywords (by occurrence count) starting with a certain prefix
3: public DataSet ListDB(string prefix, string languageBranch, int count)
4: {
5: // Call the Execute method indicating with the type parameter
6: // that we will be returning a dataset and then pass a delegate
7: // producing that dataset
8: return base.Execute<DataSet>(delegate
9: {
10: DataSet dataSet = new DataSet();
11: // Create command and in-parameters
12: IDbCommand cmd = base.CreateCommand("customGetTopKeywords");
13: cmd.Parameters.Add(base.CreateParameter("prefix", prefix));
14: cmd.Parameters.Add(base.CreateParameter("languageID", languageBranch));
15: cmd.Parameters.Add(base.CreateParameter("count", count));
16: // Use helper method in DataAccessBase to create a data adapter and fill the dataset using it
17: base.CreateDataAdapter(cmd).Fill(dataSet);
18: return dataSet;
19: });
20: }
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).
Final thoughts
- 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.
Just a quick word of caution: don't decide to make changes to the database or perform direct database queries hastily! :)
Personally I never make any changes to the EPiServer DB, and I refrain from accessing data in any way but through the EPiServer API. A lot of problems can be avoided by sticking to the intended data access principles.
Ted, I absolutely agree with you. Stick to the API as long as possible. With DDS there are new possibilities to store custom objects without adding tables. Nevertheless, I know it is (or was?) a common practice to stick custom tables in the database.
I agree, with DDS and Page Objects you can probably overcome most situations where you would otherwise want to create custom tables in the EPiServer DB.
Nevertheless, in my experience, most of the time (albeit not every time) you see custom tables, or custom queries against EPiServer-native tables, it's because features in the EPiServer API have been overlooked or misunderstood.
But from a strict ADO point of view you have some really valid points in the post - and those could come in handy if you DO need to become "creative"! :)
Agreed. Let's hope the DDS and Page Objects won't be misused in the same way. That different approaches are not selected by tradition (direct SQL) or by virtue of being novel (DDS), but because they are the pragmatic choice in the given context :)
Great post! I had experimented with my own user control that made database calls, so this post came in handy to make my control a little bit more EPi.
Just one though though. I was thinking about caching my results and was wondering if the DataAccessBase has any methods or something similar for caching? Or is the traditional Cache object you use here as well?
/ Tobias Nilsson
I use the CacheManager.RuntimeCache* methods for caching. When applicable I include cache dependencies to pages using DataFactoryCache.CreateDependency. It works very well.
The CacheManager.RuntimeCache only works for the current machine. I think it would in most cases be better to use the CacheManagers static methods Add and Remove so the remote events are send correctly to other machines in the web farm.
/ Mattias Lövström
Thanks for the correction!