Let's Build a Dynamic Company Profile Page in Episerver CMS 9

First, let me give you some context around what we are building. On a recent project, I had to build a search that queried a database to find a certified company. The search results were listed in a table and the title of each matching company linked to a detail page with all of the companies contact info. The site was being built in the latest version of Episerver CMS, which, at the time of this article, was version 9.12.0. The data for each company lived in a custom table on the Episerver CMS database and was populated via a web service from a third party CRM. It didn't make sense to have a bunch of pages living in the tree structure and duplicate data in our database. Let's have one page living in the tree structure that gets dynamically populated with the company data. The link to the detail page will contain a query string parameter with a company id. We will use this id to grab the company details and return them to the view. This demo is built around the data living in a database, but the cool thing about this concept is that it will work for any type of data source. You just have to build the service that extracts the data.

So, what does this all look like in Episerver, you ask? I will quickly point out that I have a Commerce Sandbox project in Github here that I have added all this code and more to. You can reference everything I am talking about in this article in that repo. The setup is actually not bad at all. We will have an Episerver page type called DynamicCompanyDetailPageData. This is a simple page type that really doesn't need any properties. We will have a controller for our page type, a view model for our company details,  a service which interacts with out data source and an object to hold the data which gets passed to our last component, which is the view. Easy, right? You bet your fur! 

Let's take a look at some code, shall we? Here is our GetCompanyService:

 
using EpiCommerceSandbox.Models.ViewModels.Objects;
using System;
using System.Data;
using System.Data.SqlClient;

namespace EpiCommerceSandbox.Services
{
    public class GetCompanyService
    {
        private static string connString = System.Configuration.ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString;

        public static Manufacturer GetCompany(string id)
        {
            Manufacturer company = new Manufacturer();
            string query = "SELECT * FROM [dbo].[your_company_table] WHERE companyId =@0";
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                try
                {
                    using (SqlCommand cmd = new SqlCommand(query, con))
                    {  
                        cmd.Parameters.AddWithValue("@0", id);                      
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        cmd.ExecuteNonQuery();
                        con.Close();
                        if (ds.Tables[0].Rows.Count != 0)
                        {
                            foreach (DataTable table in ds.Tables)
                            {
                                foreach (DataRow dr in table.Rows)
                                {                                    
                                    company.CompanyName = dr["name"].ToString();
                                    company.CompanyId = dr["fcodIf"].ToString();
                                    company.ContactName = dr["contact"].ToString();
                                    company.Address1 = dr["address1"].ToString();
                                    company.Address1 = dr["address2"].ToString();
                                    company.City = dr["city"].ToString();
                                    company.State = dr["state"].ToString();
                                    company.Country = dr["country"].ToString();
                                    company.ZipCode = dr["zip"].ToString();
                                    company.Email = dr["email"].ToString();
                                    company.Phone = dr["phone"].ToString();
                                    company.Website = dr["website"].ToString();                                  
                                }
                            }
                        }

                        con.Close();
                    }
                }               
                catch (Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
            }
            return company;
        }
    }
}

As you can see, this service connects to our database and builds a sql query to match the company id. It then populates the data into a Manufacturer object, which you can see the code for here:

 
namespace EpiCommerceSandbox.Models.ViewModels.Objects
{
    public class Manufacturer
    {
        public string CompanyName { get; set; }

        public string CompanyId { get; set; }

        public string Address1 { get; set; }

        public string Address2 { get; set; }

        public string City { get; set; }

        public string State { get; set; }

        public string Country { get; set; }

        public string ZipCode { get; set; }

        public string ContactName { get; set; }

        public string Email { get; set; }

        public string Phone { get; set; }

        public string Website { get; set; }
    }
}

The next two pieces we will look at are the controller:

using EpiCommerceSandbox.Business;
using EpiCommerceSandbox.Models.Pages;
using EpiCommerceSandbox.Models.ViewModels;
using EpiCommerceSandbox.Services;
using EPiServer;
using EPiServer.ServiceLocation;
using EPiServer.Web.Routing;
using System.Web.Mvc;

namespace EpiCommerceSandbox.Controllers
{
    public class DynamicCompanyPageController : PageControllerBase
    {
        private readonly PageViewContextFactory _contextFactory;

        public DynamicCompanyPageController(PageViewContextFactory contextFactory)
        {
            _contextFactory = contextFactory;
        }

        public ViewResult Index(DynamicCompanyDetailPageData currentPage)
        {
            // Let's get the company ID query string value                    
            string id = Request.QueryString["id"];

            CompanyDetailViewModel model = new CompanyDetailViewModel(currentPage);

            var urlResolver = ServiceLocator.Current.GetInstance();
            var pageUrl = urlResolver.GetVirtualPath(currentPage.ContentLink);
            model.PageURL = pageUrl.VirtualPath;

            if (Request.QueryString["id"] != null)
            {
                if (id.Equals(""))
                {
                    model.CompanyExists = false;
                }
                else
                {
                    model.CompanyExists = true;
                    model.Company = GetCompanyService.GetCompany(id);
                }
            }
            else
            {
                model.CompanyExists = false;
            }

            return View(string.Format("Index", currentPage.GetOriginalType().Name), model);
        }
    }
}

and the view model:

using EpiCommerceSandbox.Models.Pages;
using EpiCommerceSandbox.Models.ViewModels.Objects;

namespace EpiCommerceSandbox.Models.ViewModels
{
    public class CompanyDetailViewModel : PageViewModel
    {
        public CompanyDetailViewModel() { }

        public CompanyDetailViewModel(DynamicCompanyDetailPageData currentPage)
            : base(currentPage) { }

        public Manufacturer Company { get; set; }
        public bool CompanyExists { get; set; }
    }
}

The code here is pretty self explanatory. In the controller, we create an instance of our view model and use our GetCompany service to grab the data and populate a Manufacturer object. The controller then populates the Company view model field with our object and then returns it with the view. If the company doesn't exist, it will set the bool value of CompanyExists in the view model to false, which we will check for when we render the data to the view, which you can see here.

There is one last piece that I wanted to go over, and that is how I build out the link to our dynamic page. Somewhere in our page tree, we have a DynamicCompanyDetailPage sitting that is just waiting for our sweet data to populate it and to proudly display it. The easiest way for me was to create a ContentReference  property in my SiteSettings block, and use that when building the link:

<a href="@Url.ContentUrl(Site.SiteSettings.CompanyDetailPage)?id=@hit.CompanyId" alt="@hit.CompanyName">@hit.CompanyName</a>

That's it! If you have any questions or want to know more about something, comment below. I'd love to hear your thoughts on my approach to this requirement. 

3 comment(s) in response to Let's Build a Dynamic Company Profile Page in Episerver CMS 9!

Your Boyeee 10 Aug 16 @ 02:59 AM
Arild Henrichsen Says:
Great post! We use this kind of approach in all of our clients' self-service portals (think "My page"), or other public pages where a db/CRM is the data master. It can be used for populating anything from organisation structures, to course lists, signup forms, and user history. Using a dynamically populated page saves a lot of clutter in Episerver, and helps maintain data integrity a whole lot better than duplicating it into the CMS.

Your Boyeee 10 Aug 16 @ 10:02 AM
John Says:
Hey guys! Really appreciate the comments, especially pointing out the sql injection vulnerabilities. I updated the service to use sql parameters, which should help avoid the problem. Thanks again!

Your Boyeee 24 Aug 16 @ 08:00 PM
Marcus Babajews Says:
I think that using EntityFramework would be better but in principle the parameters will help guard against that. Another improvement would be to make use of the MVC routing to provide a more SEO friendly and RESTful URL to the company page. See :http://world.episerver.com/documentation/Items/Developers-Guide/EPiServer-CMS/9/Routing/Routing/

Have a comment?