Thursday, April 26, 2012

The most unappreciated feature in Sql Server 2008?

There is a feature in Sql Server 2008, that from my point of view has been hugely neglected. This is the Change Data Capture feature. Its main purpose is to keep historical data for all changes that happen in a given database.

How we did change data tracking so far?

Almost in all projects I have worked on so far, there have been some kind of custom implementation of this feature. Usually with history tables that are copy of the real one and database triggers, we are able to somewhat fulfil this need, but this approach has some big disadvantages. First, of all you should maintain this history tables and never forget to transfer the schema change that you make in the original table to the history one. Second, this inevitably adds a performance hit to you database operations, which in some cases could be significant. Last but not least, these history tables could end up being enormous part of your database. Because of this, on some projects we are not able to turn on the data tracking because the size of the database will hit the roof. 

How we can do it better using Change Data Capture?

The solution of all this problems is called Change Data Capture and is introduced in Sql Server 2008. You will need Enterprise, Developer or Evaluation edition to be able to use this feature and it has to been enabled for every database on your database that needs it. It is a great solution to all problems mentioned above: it automatically keep track for all new columns added, the changes in the history tables are applied asynchronously and you can set retention policy for how long your history records should be kept in the database. Probably, it doesn't make sense to keep you history data for more than a few days or a week most in your operational database. You can make (you should actually) periodically backups on your database that will keep all your historical data. What's more, you can turn on this feature only on the tables that you need and only on those columns that are interested to you. What's even more, all tables needed to support this feature are part of a custom scheme in the system tables. This way all history tables won't get on your way if your are using ORM tools or if your database has many tables. Finally, if you plan a bug data update and history tracking is not necessarily, you can easily turn it off and then switch on again, without loosing data. 

But is it really better?

That was what I asked myself, when first heard for this feature. I took the challenge and decided to make a little competition between our custom solution and the built in Change Data Capture feature. I took two copies of one of our databases and applied our history tables on the first one and turned on the CDC on the second one. The table in question has ~ 20 columns and ~ 200000 records. I also, prepared a big chunk of update queries in order to compare which will do a better job. See the results yourself:

Updated records Change Data Capture (in seconds) History triggers (in seconds)
2000 1 1
10000 2,5 17
20000 14 64
50000 18 403
100000 34 1475
150000 47 3334
200000 80 8500

That beats even my expectations. I ran every set of updates three times on each database - one with enabled CDC and the other with history triggers. These are the average times that every query took on my developer machine. You can only imagine what overhead is this for you production servers, especially if you frequently update or delete your data. Here is how this look graphically, I think you can imagine where this is going:

Of course, you probably don't update every day 200K records at once, but I'm sure that during the day you have at least 100K updated/deletes. This costs a lot of the server resources. This will also greatly reduce the chance of deadlocks occurring, especially when updating lots of records at once.

What is even better is that enabling this feature is a peace of cake. Check Introduction to Change Data Capture (CDC) in SQL Server 2008 from Pinal Dave, which was a great start from me. Just a heads up - be sure that your SQL Server Agent is enabled when turning on the CDC feature. And another good article in MSDN. Otherwise, your history tables will be empty and no changes will be tracked.

I plan to use this feature for the new projects that will come next (since the transition in existing project will not be very easy). I hope this helps you, feel free to use the comment form and share what solution you currently use.

Monday, April 23, 2012

The making of - Part 2, Customizing the front-end.

This is going to be a series of blog posts, that will review the process of making the latest version of DevReach.Com. For the this version we are going to use Sitefinity latest version and exploit all of its features as much as we can. We are currently working together with Sitefinity Team and producing a lot of feedback for them, part of the features that we need will probably be part of the next release of the product.

In the previous post, we created to new modules "Speakers" and "Lectures" with a relation between them. Now it's time to make use of this relation and show it to our public users. In order to do this, we need to extend the layout of the widget that Sitefinity has generated for us, once we created our custom module.

Let's start with preparing the Lectures page. For this page we want to have a list with all sessions for the current edition of DevReach, with option to filter by Track, Level and Technology. We also want to show, which speakers will take part in every single lecture, when it starts and ends, the title and the description. Eventually, we will need paging if the sessions are too many.

First, we will take care of the sessions' presentation or in order words with the master template. The best option would be to use the built-in template designer or even better the Sitefinity Thunder extension and modify the default master template. Unfortunately, we need some backend code in order to retrieve the speakers attached to the session, so we will have to use an external template.

We created a new User Control with code behind (also known as external template in Sitefinity), this control should have RadListView with ID="dynamicContentListView" and also a Pager control with ID="pager" from the built-in Sitefinity controls. These are the controls that the Sitefinity engine will try to bind the retrieved data. Finally, our ascx looks similar to this:

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="OpenAccessDataProvider,a931ecf5cdbc454c87f646d86204ad7e.ascx.cs" Inherits="DevReach2012.Com.UserControls.Public.LecturesList" %>
<%@ Register TagPrefix="sf" Namespace="Telerik.Sitefinity.Web.UI.PublicControls.BrowseAndEdit" Assembly="Telerik.Sitefinity" %>
<%@ Register TagPrefix="sf" Namespace="Telerik.Sitefinity.Web.UI.ContentUI" Assembly="Telerik.Sitefinity" %>
<%@ Register TagPrefix="sf" Namespace="Telerik.Sitefinity.Web.UI.Comments" Assembly="Telerik.Sitefinity" %>
<%@ Register TagPrefix="sf" Namespace="Telerik.Sitefinity.Web.UI.Fields" Assembly="Telerik.Sitefinity" %>
<%@ Register TagPrefix="sf" Namespace="Telerik.Sitefinity.Web.UI" Assembly="Telerik.Sitefinity" %>
<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>

<telerik:RadListView ID="dynamicContentListView" runat="server">
        <span id="spnTrack" runat="server" />
        <sf:FlatTaxonField runat="server"  ID="ftfLevels" DisplayMode="Read" WebServiceUrl="~/Sitefinity/Services/Taxonomies/FlatTaxon.svc" 
        AllowMultipleSelection="true" TaxonomyId="6D4775B4-771E-42A7-B810-AB539ADD421A"
        TaxonomyMetafieldName="sessionlevels" Expanded="false" ExpandText="ClickToAddTags" BindOnServer="true" />         
        <%#Eval("StartTime") %>
        <%#Eval("EndTime") %>
        <h3><%#Eval("Title") %></h3>
            <%#Eval("Description") %>
        <sf:FlatTaxonField runat="server"  ID="ftfTechnologies" DisplayMode="Read" WebServiceUrl="~/Sitefinity/Services/Taxonomies/FlatTaxon.svc" 
        AllowMultipleSelection="true" TaxonomyId="AF2BC11B-CB47-4B2C-8336-FF076552C27B"
        TaxonomyMetafieldName="technologies" Expanded="false" ExpandText="ClickToAddTags" BindOnServer="true" />   
        Presented By:
        <div id="dvSpeakers" runat="server">
<sf:Pager id="pager" runat="server"></sf:Pager>

We used the FlatTaxonField control in order to get the custom taxons applied to our sessions. This control has a lot of properties and you need to choose the right combination of them in order to display the taxonomies applied. You need the TaxonomyId, which you can get from the 'sf_taxonomies' table in your database. The WebServiceUrl is also required and is the same for all flat taxonomies. The last thing is the TaxonomyMetafieldName property which should be the same as the property name in your dynamic module. It is by default, very similar to the taxonomy name.

Now, let's take a look at the codebehind file. In order to show the speakers assigned to this session with have subscribed for the ItemDataBound event of the RadListView and retrieved the speakers using the Sitefinity API ( you can take a look at the code reference that is automatically generated for you in the back end on the Module Builder page).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using DevReach2012.Com.CmsManagers;
using DevReach2012.Com.CmsManagers.PipesNFilters;
using DevReach2012.Com.CmsManagers.Taxonomies;
using Telerik.OpenAccess;
using Telerik.Sitefinity.DynamicModules.Model;
using Telerik.Sitefinity.Taxonomies.Model;
using Telerik.Web.UI;

    public partial class LecturesList : UserControl
        protected void Page_Load(object sender, EventArgs e)
            dynamicContentListView.ItemDataBound += new EventHandler<Telerik.Web.UI.RadListViewItemEventArgs>(dynamicContentListView_ItemDataBound);

        void dynamicContentListView_ItemDataBound(object sender, Telerik.Web.UI.RadListViewItemEventArgs e)
            if ((e.Item.ItemType == RadListViewItemType.DataItem) || (e.Item.ItemType == RadListViewItemType.AlternatingItem))
                SpeakersManager speakersManager = new SpeakersManager();
                DynamicContent rawSession = (e.Item as RadListViewDataItem).DataItem as DynamicContent;
                HtmlGenericControl dvSpeakers = e.Item.FindControl("dvSpeakers") as HtmlGenericControl;
                Guid[] speakerIds = rawSession.FieldValue<Guid[]>("Speakers");
                IList<DynamicContent> speakers = speakersManager.GetByIds(speakerIds);
                foreach (DynamicContent speaker in speakers)
                    HtmlAnchor linkSpeaker = new HtmlAnchor();
                    linkSpeaker.InnerText = speaker.FieldValue<string>("LastName") + ", ";
                    linkSpeaker.Attributes.Add("href", speakersManager.GetDefaultUrl(speaker));
SpeakersManager is a class that we defined in order to wrap all the work with the Sitefinity API regarding Speakers module in common place. We have similar manager for all other dynamic modules and all of them derive from a common base class.

    public abstract class BaseDynamicContentManager
        public BaseDynamicContentManager(string dynamicTypeName)
                this.dynamicTypeName = dynamicTypeName;

        public IQueryable<dynamiccontent&rt; GetAllPublished()
            DynamicModuleManager dynamicModuleManager = DynamicModuleManager.GetManager();
            Type speakerType = TypeResolutionService.ResolveType(dynamicTypeName);

            IQueryable<dynamiccontent&rt; speakers = dynamicModuleManager
            .Where(s => s.Status == Telerik.Sitefinity.GenericContent.Model.ContentLifecycleStatus.Live && s.Visible);

            return speakers;

        protected readonly string dynamicTypeName;

    public class SpeakersManager : BaseDynamicContentManager
        public SpeakersManager() : base("Telerik.Sitefinity.DynamicTypes.Model.Speakers.Speaker")

        internal IList<dynamiccontent&;rt; GetByIds(Guid[] speakerIds)
            DynamicModuleManager dynamicModuleManager = DynamicModuleManager.GetManager();
            Type speakerType = TypeResolutionService.ResolveType(dynamicTypeName);
            IList<dynamiccontent&rt; speakers = new List<dynamiccontent&rt;();

            foreach (Guid speakerGuid in speakerIds)
                speakers.Add(dynamicModuleManager.GetItem(speakerType, speakerGuid) as DynamicContent);

            return speakers;

        internal string GetDefaultUrl(DynamicContent speaker)
            foreach (DynamicContentUrlData url in speaker.Urls)
                if (url.IsDefault)
                    return url.Url;

            return speaker.Urls.FirstOrDefault().Url;
We try to have as little as possible hard-coded strings like types, etc. That's why we keep this strings as properties of the managers and use them wherever we need them. So far, we have a nice list with all the sessions and the properties we need. The beauty of this approach is that Sitefinity will take care for all the data retrieving, paging and filtering and all we do is to define the layout ( and pullout the Speakers which will Sitefinity cannot handle).

The last thing we need to do is to tell Sitefinity to use this user control as a template instead of the built-in one. Now, the things get little hacky here. Drag and drop the widget that the module builder has created for you, in our case it's called sessions and click 'Edit' and get the DefaultMasterTemplateId which is basically a Guid. Then, rename you user control in the following manner OpenAccessDataProvider,.ascx You might need to change the OpenAccessDataProvider to another one if you not using it as a page provider, but I doubt this is the case. The last thing is to move this control (with the codebehind) to a folder under the root of you site called 'SfCtrlPresentation'. If you have read careful, the user control that I showed you follows this convention. This is pretty inconvenient approach and I guess that this will be greatly improved in some of the following releases - probably by allowing us to set the user control with a template path or even better - by using Sitefinity Thunder.

Now it's time to deal with the filtering. We will use the standard Tags control, that is in the Classification category. By default, it shows the Tags taxonomy that we do not use currently. We need to set some properties to it. Drag and drop the control, click Edit and set the TaxonomyId property ( you can get it from the database table). Set the FieldName property to the name of the property from the dynamic module. This way we get a list with all taxons from this taxonomy and we can filter the sessions list with a single link on a taxon. This works by passing filtering parameters through the url. We can drag and drop similar widget for all taxonomies that we need in set it up in the same manner. In our case we have on control for filtering by technologies, by session level and by track. If we want this to look even better we can add some jquery and transform this links to dropdowns. Here is an example how to do this.

The speakers page is even easier as we need only a part of the things we already did with the sessions. So far, we managed to create our custom data modules and present them in convenient way to our users. What's next is to setup our eCommerce module and use it to sell passes for our conference. I hope this is useful to you, stay tuned and don't miss the next series.

Monday, April 16, 2012

Some tips for getting started with Telerik OpenAccess Fluent Mapping

If you are just starting using Telerik OpenAccess Fluent Mapping with Sql Server as database, you will probably need to set some settings in order to benefit most from this great ORM tool.
First of all, when defining your FluentMetadataSource, be sure to override the CreateModel() method and set the following properties:
public class DataMetadataSource : FluentMetadataSource
        protected override Telerik.OpenAccess.Metadata.MetadataContainer CreateModel()
            var model = base.CreateModel();
            model.DefaultMapping.NullForeignKey = true;
            model.NameGenerator.UseModelNames = true;
            return model;
The first one "NullForeignKey" will generate for you foreign keys on every column that is part of an association, keeping your data consistent. The next one "UseModelNames" will generate the name of the tables and the columns in your database to be the same as your .NET types. This will save you lots of dummy writing and copy pasting.

Another tip that will probably proof useful for you, is to save your model in the DataContext as private static variable and pass it to the constructor. This will boost the performance of your DataAccess layer.
public class DataContext : OpenAccessContext
        static readonly MetadataContainer metadataContainer = new DataMetadataSource().GetModel();

        static BackendConfiguration backendConfiguration = new BackendConfiguration()
            Backend = "mssql"

        private const string DbConnection = "dbConnectionString";

        public DataContext()
            : base(DbConnection, backendConfiguration, metadataContainer)

Finally, if your are just starting using this product, you will probably want to add the "Telerik.OpenAccess.Metadata.Fluent.Advanced" namespace in the using section where your mapping is defined. Since some of the features (like adding database indexes) are defined as extension methods in this namespace, for newbies (like me), that are not very familiar yet with the functionality that the product gives, this could be a great time saver.

Many thanks to Yosif Yosifov and Serge Ovanesyan for sharing this tips. If you have found any other interesting options, feel free to share them in the comments.

Monday, April 2, 2012

The making of - Part 1, Defining the content types.

This is going to be a series of blog posts, that will review the process of making the latest version of DevReach.Com. For the this version we are going to use Sitefinity latest version and exploit all of its features as much as we can. We are currently working together with Sitefinity Team and producing a lot of feedback for them, part of the features that we need will probably be part of the next release of the product.

DevReach is the premier developer conference for Microsoft technologies in Central and Eastern Europe. This is year is its sixth edition and Telerik as one of the organizers decided to remake the website from scratch using the latest products that the company offers.

In order to define the custom types we need, we are going to use the dynamic module builder introduced in Sitefinity 4.4. One of the most important modules that we are going to define is the "Speakers" module, which will manage the people that will take part in all the sessions. There is nothing too complicated here, most of the fields in this module are simple short text fields.

The one thing that worth mentioning is the conferenceissues field. This is a custom flat taxonomy that will created and it represents all the editions of DevReach. Once we have added a speaker to our database, if he is participating again next year, we only have to assign the respective conference issue taxon and the speaker will be shown on all the places where it's needed. This will also help us to easily show the speakers that attended previously and tease the audience about the next edition.

The next very important module that we had to create is the "Lectures" module. This one is a bit more complicated than the previous one. We have some additional custom taxonomies created:

  • Tracks - this shows which track the particular session is part of and help us to organize all the session in a more convenient schedule 
  • Halls - this one should be clear enough
  • Session Levels - defines the technical level assigned for this session according to the standard rating system. Users will be able to filter by this field in the public pages.
  • Technologies - stands for the topics that are going to be discussed in the session in question
  • Conference Issue - again, we assign in which conference edition this session took place or is going to take place. This way we will built a nice archive of all the lectures that have been part of the conference.
Some other fields that we need are of course: Start Time, End Time, Title and Description. We also need to boolean properties to mark the break sessions ( like coffee breaks, lunch breaks, etc.) and another to show the common session for all tracks ( like the keynotes and the closing session).

The trickiest part here is to make a relation between these to modules that we just created. We need to know, which speakers in which session are going to take part. To achieve this, we are going to use the new field type introduced in Sitefinity 5 - Array of GUIDs. Since, some session can be performed by more than one speakers we are using the Array of GUIDs, otherwise we would choose the GUID only. This field will keep the Speakers identifiers for every session, what we need to do manually though is to make a nice user experience for editing this field. So far, we did all of this only with working with the administration and without writing a single line of code, which is pretty awesome. We used the following blog post (Creating one to many relationships in dynamic modules) as a starting point for our field control for speakers and did some minor changes. In order to show the first and the last name of the speaker and search by them, we changed the RelatedItemsField.ascx file in the following manner:

<div id="selectorTag" style="display: none;" class="sfDesignerSelector sfFlatDialogSelector">
   <designers:ContentSelector ID="selector"  runat="server" 
          TitleText="Choose items" 
          SearchBoxTitleText="<%$Resources:Labels, NarrowByTypingTitleOrAuthorOrDate %>" 
          ListModeClientTemplate="<strong class='sfItemTitle'>{{FirstName}} {{LastName}}</strong>">

<ul id="selectedItemsList" data-bind="foreach: items">
         <span data-bind="text: FirstName"> </span> <span data-bind="text: LastName"> </span>
         <a href="#" class="remove">Remove</a>

Gotcha1: It seems that the data service used for retrieving the speakers (or what ever module you are using) returns the master records instead of the live one. Because of this, the wrong guids are saved in the database, this is already communicated to the product team and probably be fixed in one of the next versions.
Gotcha2: After adding this field control, you will no longer be able to edit the backend screens through the built-in interface (because of a "Specified method is not supported" exception). This will also be fixed, but until then, you'd better tweak your backend screens, before adding this Field Control.

In one of the next posts, we will try to reuse these field controls definitions, in some other modules. So far, we were dealing only with the backend and things are pretty simple and straightforward. All this sets the ground for a further customization and development. In the next post, I will show you how we customized the front end widgets that Sitefinity has generated for us in order to suit our needs.