Magnus Rahl
Jul 27, 2010
  4922
(0 votes)

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.
Jul 27, 2010

Comments

Ted
Ted Sep 21, 2010 10:33 AM

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.

Magnus Rahl
Magnus Rahl Sep 21, 2010 10:33 AM

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.

Ted
Ted Sep 21, 2010 10:33 AM

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"! :)

Magnus Rahl
Magnus Rahl Sep 21, 2010 10:33 AM

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 :)

Sep 21, 2010 10:33 AM

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

Magnus Rahl
Magnus Rahl Sep 21, 2010 10:33 AM

I use the CacheManager.RuntimeCache* methods for caching. When applicable I include cache dependencies to pages using DataFactoryCache.CreateDependency. It works very well.

Sep 21, 2010 10:33 AM

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

Magnus Rahl
Magnus Rahl Sep 21, 2010 10:33 AM

Thanks for the correction!

Please login to comment.
Latest blogs
Plug-in manager is back in CMS 12

Plug-in manager is back in the UI, what is it and how can i use it?

Luc Gosso (MVP) | Oct 6, 2022 | Syndicated blog

Display Child Pages in Content Delivery API Response

The below example will implement an instance of IContentConverterProvider to customise the serialisation of PageData and output child pages in the...

Minesh Shah (Netcel) | Oct 4, 2022

Bring the Report Center back in Optimizely CMS 12

The Report Center has been a part of Optimizely CMS since its first debut in version 5R2 in 2008, but in CMS 12, it's removed! Don't despair! Make...

Tomas Hensrud Gulla | Oct 4, 2022 | Syndicated blog

Customizing Property Lists in Optimizely CMS

Generic property lists is a cool editorial feature that has gained a lot of popularity - in spite of still being unsupported (officially). But if y...

Allan Thraen | Oct 2, 2022 | Syndicated blog

Optimizely names Luminary Senior Developer, Ynze Nunnink, OMVP

Luminary Senior Developer and Optimizely Lead, Ynze Nunnink has secured the coveted position of Optimizely MVP. Earning a Platinum badge for his...

Ynze | Oct 2, 2022 | Syndicated blog

Content Delivery API – The Case of the Duplicate API Refresh Token

Creating a custom refresh provider to resolve the issues with duplicate tokens in the DXC The post Content Delivery API – The Case of the Duplicate...

David Lewis | Sep 29, 2022 | Syndicated blog