Views: 14538
Number of votes: 4
Average rating:

Creating a Custom EPiServer Community Module - Part Two

The next step is to build factory methods for persisting Movie objects. In order to do that we’ll need to have a data source to store the entities in. While it’s possible to use just about any data source, using any method we like,  this article is about building a module the EPiServer Community way so we’ll use the same SQL Server database as the framework uses and communicate with it using stored procedures.

This article is one in a series of articles on this subject, the rest will follow within shortly.

Other articles in this series

» Creating a Custom EPiSever Community Module - Part One

» Creating a Custom EPiSever Community Module - Part Three

» Creating a Custom EPiSever Community Module - Part Four

Get the Source Code

» Download the full source code for this article

Setting Up the Database

Creating the Table

The Movie class has three properties, plus one, ID, inherited from FrameworkEntityBase, that should be saved to the database. We’ll create a table named “tblMovie” with four columns, “intID”, “strTitle”, “intRuntime” and “intAuthorID”. We’ll make “intID” the primary key column.

A script for creating the table, along with the stored procedures, is included in the downloadable source code.

The Stored Procedure for Getting a Movie

The stored procedure for retrieving a Movie objects data is very straight forward:

CREATE PROCEDURE spGetMovie
 @intID int
AS
BEGIN

   SELECT intID, strTitle, intRuntime, intAuthorID FROM tblMovie
 WHERE intID = @intID

END

The Stored Procedure for Adding a Movie

The stored procedure for adding a Movie to the database is also quite straight forward. Note though that we select the ID of the newly inserted movie after inserting it. We do this as the factory method that adds the Movie should return the newly persisted object and therefore needs to know it’s ID.

CREATE PROCEDURE spAddMovie
 @strTitle nvarchar(255),
 @intRuntime int = null,
 @intAuthorID int
AS
BEGIN

    INSERT INTO tblMovie
           (strTitle,
            intRuntime,
            intAuthorID)
     VALUES
           (@strTitle,
            @intRuntime,
            @intAuthorID)

 SELECT SCOPE_IDENTITY()

END

The Stored Procedure for Updating a Movie

This stored procedure is also quite simple.

CREATE PROCEDURE spUpdateMovie
 @intID int,
 @strTitle nvarchar(255),
 @intRuntime int = null,
 @intAuthorID int
AS
BEGIN

 UPDATE tblMovie SET
   strTitle = @strTitle,
        intRuntime = @intRuntime,
        intAuthorID = @intAuthorID
 WHERE intID = @intID

END

The Stored Procedure for Deleting a Movie

The last of the CRUD stored procedures, and as you might have guessed, this is also quite simple.

CREATE PROCEDURE spRemoveMovie
 @intID int
AS
BEGIN

    DELETE FROM tblMovie WHERE intID = @intID

END

Creating the Factory

With the database set up we’ll create the CRUD methods in the factory. Let’s begin by creating the Factory class. The class should inherit “FrameworkFactoryBase” as this will give us access to the “UpdateEntity” and “RemoveEntity” methods. By invoking these methods when a Movie object is updated or removed the base class will help us with updating attributes, adding a record to the activity log (if the activity log is configured to log events for the type) and a lot of other things related to the features that we got for free when we decided to make the Movie class inherit from FrameworkEntityBase. It will also remove the object from the cache.

using System;
using System.Data.Common;
using EPiServer.Common.Data;
using EPiServer.Common.Security;

namespace ExampleProjects.Movie.Data
{
    public class MovieFactory : FrameworkFactoryBase
    {
    }
}

The GetMovie and ConstructMovie Methods

The GetMovie method should retrieve an instance of Movie from the database with a specified ID, given that such a record exists in the database. Otherwise it should return null. The implementation is straight forward. We call the “spGetMovie” stored procedure with the specified ID as a single parameter. The GetReader method in the DatabaseHandler makes this very easy. If the returned reader contains a record we load a Movie object based on it by invoking the ConstructMovie method which is explained below.

internal static Movie GetMovie(int id)
{
    Movie movie = null;
   
    using (DbDataReader reader =
        DatabaseHandler.GetReader("spGetMovie", id))
    {
        if (reader.Read())
            movie = ConstructMovie(reader);
    }

    return movie;
}

The ConstructMovie method locates the entity provider for the Movie type and invokes the entity providers GetEntityInstance method with the reader. We’ll create this method later when we create the MovieEntityProvider class. Note though that although the method that we invoke here doesn’t exist yet the class still compiles. This is because the MovieFactory has no direct coupling to the MovieEntityProvider class at design time. This is one of the reasons why the Community framework can provide a lot of generic functionality such as the rating system, the tagging system etc. A lengthier discussion about the benefits of this is beyond the scope of this article, but I recommend you to read up on the ABSTRACT FACTORY pattern if you are curious and not yet familiar with it.

private static Movie ConstructMovie(DbDataReader reader)
{
    IEntityProvider movieEntityProvider =
        EntityProviderHandler.GetEntityProvider(typeof (Movie));

    return (Movie)
        movieEntityProvider.GetEntityInstance(
            typeof(Movie), reader);
}

The AddMovie Method

The AddMovie method accepts a movie object as a parameter:

internal static Movie AddMovie(Movie movie)
{
    bool wasInTransaction = DatabaseHandler.InTransaction;
    if (!wasInTransaction)
        DatabaseHandler.BeginTransaction();

    int newId = -1;
    try
    {
        object[] spParameters = new object[3];
        spParameters[0] = movie.Title;
        spParameters[1] = movie.RuntimeMinutes;
        spParameters[2] = movie.Author.ID;

        newId = Convert.ToInt32(DatabaseHandler.GetScalar(
"spAddMovie", spParameters));
       
        UpdateEntity(movie, newId);
       
        if (!wasInTransaction)
            DatabaseHandler.Commit();
    }
    catch (Exception)
    {
        if (!wasInTransaction)
            DatabaseHandler.Rollback();
        throw;
    }

    return MovieHandler.GetMovie(newId);
}

The method checks if there is currently an open transaction in the current context (usually the HTTP context) with the help of the DatabaseHandler class’s static InTransaction property. If there is no active transaction it opens one.

As we are only going to save a single object a transaction may seem unnecessary but remember that we made Movie inherit from FrameworkEntityBase and that quite a lot may happen in the background when we save a single movie. For instance it might be that we have categorized the movie and when that categorization is saved (using the UpdateEntity method) an exception is thrown. In that case we want to roll back the saving of the movie and handle the fact that we were unable to save it, probably by presenting an error message to the user. We don’t want to create a partially saved object.

Anyhow, when we have ensured that there is an open transaction we proceed to create an array of parameters that will be passed to the stored procedure. We then execute the “spAddMovie” stored procedure with the parameters using the DatabaseHandlers GetScalar method and save the ID of the newly saved Movie object to a variable. We then invoke the UpdateEntity method and after that we commit the transaction. Last but not least we fetch the newly saved Movie object by asking the MovieHandler (which we’ll soon create) for it.

The UpdateMovie Method

The UpdateMovie method is very similar to the AddMovie method with the exceptions that it executes a different stored procedure and that it doesn’t return anything.

internal static void UpdateMovie(Movie movie)
{
    bool wasInTransaction = DatabaseHandler.InTransaction;
    if (!wasInTransaction)
        DatabaseHandler.BeginTransaction();

    try
    {
        object[] spParameters = new object[4];
        spParameters[0] = movie.ID;
        spParameters[1] = movie.Title;
        spParameters[2] = movie.RuntimeMinutes;
        if (movie.Author != null)
            spParameters[3] = movie.Author.ID;
        else
            spParameters[3] = -1;

        DatabaseHandler.ExecuteNonQuery("spUpdateMovie", spParameters);
       
        UpdateEntity(movie);
       
        if (!wasInTransaction)
            DatabaseHandler.Commit();
    }
    catch (Exception)
    {
        if (!wasInTransaction)
            DatabaseHandler.Rollback();
        throw;
    }
}

Note that we check whether the movies author is null or not, and send -1 as the authors ID to the stored procedure if it is. This is to handle the special case where the author has been fully deleted (as opposed to soft deleted where it still exists in the database but is hidden programmatically).

The RemoveMovie Method

After having created the AddMovie and UpdateMovie methods the RemoveMovie method shouldn’t offer any surprises. We ensure that we are in a transaction and execute the “spRemoveMovie” stored procedure. Instead of invoking the UpdateEntity method we do however instead invoke the RemoveEntity method inherited from FrameworkFactoryBase.

internal static void RemoveMovie(Movie movie)
{
    bool wasInTransaction = DatabaseHandler.InTransaction;
    if (!wasInTransaction)
        DatabaseHandler.BeginTransaction();

    try
    {
        DatabaseHandler.ExecuteNonQuery("spRemoveMovie", movie.ID);
       
        RemoveEntity(movie);
        if (!wasInTransaction)
            DatabaseHandler.Commit();
    }
    catch (Exception)
    {
        if (!wasInTransaction)
            DatabaseHandler.Rollback();
        throw;
    }
}


Please login to comment.