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.
Comments