Real-world Apps for SharePoint 2013 - Kudos (Part 2)

Several months ago I wrote a post about building real-world apps for SharePoint 2013.  In that post, I walked through the creation of an employee recognition app for SharePoint 2013 called Kudos.  The original Kudos app leveraged SharePoint lists for storage and wasn’t very configurable.  In this post, we’ll evolve the Kudos app to address several of these limitations, add some additional capabilities, and discuss the tenancy considerations for autohosted apps.

The Solution

The new Kudos app will leverage SQL Azure for data storage, which is much more efficient, scalable, and easier to query in our solution.  The new app will also differentiate between app visitors and app administrators by allowing administrators to configurable app-specific settings.  Gamification features will be added to allow configurable “badges” to be included in Kudos submissions.  Finally, the app will be updated to handle direct navigation where context tokens are null.  All of these features are illustrated in the following video:


Autohosted App Tenancy

Before jumping into the intricacies of the new app, I want to discuss the tenancy considerations of autohosted apps.  When an autohosted app is installed into a SharePoint site, all the app components (web/database/workflow) are auto-provisioned in a Microsoft owned Azure account (  Not only is provisioning automatic, high-availability and disaster recovery is automatic (hence the name “autohosted”).  This happens each time the app is installed in a SharePoint site (even within the same Office 365 tenancy).  For example, if I installed my autohosted app into 8 team sites, it would result in 8 separate web sites and databases in Azure.  The diagram below illustrates the tenancy of autohosted apps.  If the isolated tenancy of autohosted apps doesn’t work for you, provider-hosted apps offers more flexibility.  However, with flexibility comes complexity as things are less “automatic”.

Storage with SQL Azure

When I first learned about the ability to provision databases on-demand for autohosted apps, it sounded like some sort of voodoo magic.  How would a newly provisioned app know what connection string to use?  It turns out that Microsoft came up with a very elegant solution to the problem.  In fact, managed CSOM provides APIs to retrieve either a SqlConnection object or the raw connection string using the client context.  Before I jump into these APIs, let me explain the setup to leverage databases in autohosted app solutions.

The new Kudos app will leverage the ADO.NET Entity Framework to talk with SQL Azure. The Entity Framework supports two models…entities first or database first.  Although it is a matter of preference, I tend to use the database first approach except for in MVC apps.  To do this, we’ll start with a database that we will later use to generate our entity model.  Visual Studio 2012 includes LocalDB which is essentially an improved on-demand version of SQL Express.  I like to create isolated instances in LocalDB for my apps, which can be done with the SqlLocalDB.exe command line:

Next, we need to add a database project to our app solution.  Right-click the solution and select Add > New Project.  In the Add New Project dialog, find the SQL Server Database Project template under Other Languages > SQL Server.  Give the project a name and add it to the solution:

We need to make the app project aware of this new database project (almost like adding a project reference).  To create this reference, set the SQL Database property on the app project to our new database project.  When we set this property, Visual Studio will warn us that the target for the database project will be changed to SQL Azure:

Next, we can add all the appropriate tables and procedures to the database project.  For Kudos, I added tables for Kudos and AppSettings.  I also added a GetKudosHistory stored procedure for pulling historical statistics on a user.  A stored procedure will be more efficient than directly querying the entity model with numerous where clauses.  Finally, I added a post-deployment script to seed the AppSettings table with some default settings:

DECLARE @SettingsCount INTSELECT @SettingsCount = COUNT(*) FROM dbo.AppSettings

--Seed the AppSettings table with default settings if emptyIF (@SettingsCount < 1)BEGIN    INSERT INTO dbo.AppSettings (UseQuota, Quota, QuotaWindow, PostToNewsfeed, EmailRecipient,         EmailSubmitter, EmailRecipientManager, EmailSubmitterManager)    VALUES (1, 4, 2, 1, 1, 1, 1, 1)END


Before we can generate an entity model, we need to publish the database to the LocalDB instance we created earlier.  Remember, this is automatic once we deploy the autohosted app, but here we need to publish manually so we can generate the initial entity model.  The Publish Database wizard can be launch be right-clicking the database project and selecting publish:

With the database published in LocalDB we can turn our attention to the entity model, which will be added to the web project.  The ADO.NET Entity Data Model selection can be found under Visual C# > Data in the Add New Item dialog:

Using the Entity Data Model Wizard We, we will select “Generate from Database”, provide the connection to our database on LocalDB, and select all the database objects we want represented in our entity model:

Side note: I prefer my entity model to contain a constructor that accepts a connection string.  This constructor will get generated automatically if I change the “Code Generation Strategy” property on the entity model from None to Default.  However, I also need to delete the two .tt files that are nested under the model in solution explorer.  I’ve done it this way, but it is all a matter of preference:

As mentioned earlier, CSOM includes new APIs for working with SQL Azure databases in autohosted apps.  AppInstance.TryGetAppDatabaseConnectionDirect can be used to retrieve a SqlConnection object for the database in SQL Azure.  With this approach, you should configure the connection string in your web.config with the name LocalDBInstanceForDebugging and the API will dynamically use this connection when debugging:

var contextToken = TokenHelper.GetContextTokenFromRequest(Page.Request);var hostWeb = Page.Request["SPHostUrl"];using (var clientContext = TokenHelper.GetClientContextWithContextToken(hostWeb, contextToken, Request.Url.Authority)){    SqlConnection conn = new SqlConnection();    bool isReadOnly;    AppInstance.TryGetAppDatabaseConnectionDirect(clientContext, out conn, out isReadOnly);    clientContext.ExecuteQuery();

    //Start using SqlConnection


Although powerful for many development scenarios, a SqlConnection object is not the most appropriate for use with the Entity Framework.  Instead, we will leverage AppInstance.RetrieveAppDatabaseConnectionString to retrieve the raw connection string in the Kudos app.  This API will NOT return the raw connection string when debugging, so the code will check for a null connection string and directly leverage the LocalDBInstanceForDebugging connection if appropriate:

var contextToken = TokenHelper.GetContextTokenFromRequest(Page.Request);var hostWeb = Page.Request["SPHostUrl"];using (var clientContext = TokenHelper.GetClientContextWithContextToken(hostWeb, contextToken, Request.Url.Authority)){    ClientResult<string> connStringResult = AppInstance.RetrieveAppDatabaseConnectionString(clientContext);    clientContext.ExecuteQuery();    string connString = connStringResult.Value;

    //connection string will be empty if in debug mode    if (String.IsNullOrEmpty(connString))        connString = ConfigurationManager.ConnectionStrings["LocalDBInstanceForDebugging"].ConnectionString;

    //start using connection string (ex: Entity Framework)


Autohosted app deployment automatically handles all the connection string wire-up magic with SQL Azure.  However, it won’t generate an Entity Framework connection string, which requires additional metadata.  Instead, we can manually convert the connection string using the EntityConnectionStringBuilder class.  I did this in a ConnectionUtil class as seen below:

using Microsoft.SharePoint.Client;using System;using System.Collections.Generic;using System.Data.EntityClient;using System.Linq;using System.Web;using System.Web.Configuration;

namespace SharePointKudosSQLWeb.Util{    public class ConnectionUtil    {        public static string GetEntityConnectionString(ClientContext clientContext)        {            //try to get the connection string from the clientContext            ClientResult<string> result = AppInstance.RetrieveAppDatabaseConnectionString(clientContext);            clientContext.ExecuteQuery();            string connString = result.Value;

            //if the connection string is empty, then this is debug mode            if (String.IsNullOrEmpty(connString))                connString = WebConfigurationManager.ConnectionStrings["LocalDBInstanceForDebugging"].ConnectionString;

            //build an Entity Framework connection string            EntityConnectionStringBuilder connBuilder = new EntityConnectionStringBuilder();            connBuilder.Provider = "System.Data.SqlClient";            connBuilder.ProviderConnectionString = connString;            connBuilder.Metadata = "res://*/KudosModel.csdl|res://*/KudosModel.ssdl|res://*/KudosModel.msl";

            //return the formatted connection string            return connBuilder.ConnectionString;        }    }}



The new employee recognition apps includes the ability to send badges to the recipient of a Kudos.  A document library in the app web is the perfect storage location for these badges.  This will allow an administrator to add/remove badges as is necessary.  Most of the work in developing the badge funcationality is in the BadgePicker control for selecting a badge in the Kudos form:

We also want to include the badge in the social post to the newsfeed:

if (settings.PostToNewsfeed){    SocialFeedManager socialMgr = new SocialFeedManager(clientContext);    var post = new SocialPostCreationData();    post.ContentText = "Sent @{0} a Kudo for " + hdnSelectedBadgeTitle.Value + ":\n'" + txtMessage.Text + "'";    post.ContentItems = new[]    {        new SocialDataItem        {            ItemType = SocialDataItemType.User,            AccountName = recipient.LoginName        }    };    post.Attachment = new SocialAttachment()     {         AttachmentKind = SocialAttachmentKind.Image,         Uri = hdnSelectedBadgeUrl.Value     };    ClientResult<SocialThread> resultThread = socialMgr.CreatePost(null, post);    clientContext.ExecuteQuery();}


Distinguishing App Roles

As I started to develop real-world solutions using the app model, I quickly identified the need to distinguish an app administrator from a normal visitor.  For Kudos, the administrator needs the ability to configure quotas, quota durations, notification settings, and badges for the application. 

I see two distinct approaches to application roles…inherit the permissions from the app web or build a complete custom permission model in the apps storage (ex: SQL Azure).  A custom model might be necessary for complex permission needs.  For Kudos, I just need to know if a user is an administrator and the app web can easily provide that.  You might ask why I’m going to the app web and not the host web for checking permissions.  Checking permissions on the host web would require my app to have full control on the host web, which it would otherwise not require.  It is bad practice for your app to request more permissions than it needs.  This is especially true in our case, since the app is free to check permissions on its own app web (which are inherited from the host web).

I decided to surface my admin screens through the settings menu in the chrome control.  This posed another challenge, since this menu is rendered using client-side script.  I found that checking the effective permissions of the user took too many API calls to do client-side (one call to get the current user and one to get effective permissions for the user).  Instead, I decided to check effective permissions in managed code and conditionally output the appropriate script.  I delivered this through a base page class that all of my pages inherited from:

namespace SharePointKudosSQLWeb.Pages{    public class KudosBasePage : System.Web.UI.Page    {        protected override void OnLoad(EventArgs e)        {            bool hasManageWebPerms = false;

            //get SharePoint context            var spContext = Util.ContextUtil.Current;            using (var clientContext = TokenHelper.GetClientContextWithContextToken(spContext.AppWebUrl, spContext.ContextTokenString, Request.Url.Authority))            {                //check if the user has ManageWeb permissions from app web                BasePermissions perms = new BasePermissions();                perms.Set(PermissionKind.ManageWeb);                ClientResult<bool> result = clientContext.Web.DoesUserHavePermissions(perms);                clientContext.ExecuteQuery();                hasManageWebPerms = result.Value;            }

            //define initial script            string script = @"            function chromeLoaded() {                $('body').show();            }            //function callback to render chrome after SP.UI.Controls.js loads            function renderSPChrome() {                //Get the host site logo url from the SPHostLogoUrl parameter                var hostlogourl = decodeURIComponent(getQueryStringParameter('SPHostLogoUrl'));

                var links = [{                    'linkUrl': '',                    'displayName': 'Contact us'                }];                ";

            //add link to settings if the current user has ManageWeb permissions            if (hasManageWebPerms)            {                script += @"links.push({                        'linkUrl': 'Settings.aspx?" + Request.QueryString + @"',                        'displayName': 'App Settings'                    });";                script += @"links.push({                        'linkUrl': '" + String.Format("{0}/KudosBadges", spContext.AppWebUrl) + @"',                        'displayName': 'Badge Mgmt'                    });";            }                            //add remainder of script            script += @"                //Set the chrome options for launching Help, Account, and Contact pages                var options = {                    'appIconUrl': hostlogourl,                    'appTitle': document.title,                    'settingsLinks': links,                    'onCssLoaded': 'chromeLoaded()'                };

                //Load the Chrome Control in the divSPChrome element of the page                var chromeNavigation = new SP.UI.Controls.Navigation('divSPChrome', options);                chromeNavigation.setVisible(true);


            //register script in page            Page.ClientScript.RegisterClientScriptBlock(typeof(KudosBasePage), "KudosBasePage", script, true);

            //call base onload            base.OnLoad(e);        }    }}


Deciding on client-side or managed CSOM is an important consideration to weigh for all API calls.  Each will likely take the same number of calls, but managed code should have less latency when multiple API calls are required.  This is especially true for autohosted apps, where the app and the SharePoint Online farm might be hosted from the same Microsoft data center.  At worst, the app and the farm would communicate over Microsoft’s massive communication backbone between data centers.

Direct Navigation

It possible (and likely) that some users will try navigating directly to the app instead of through SharePoint.  This “direct navigation” is a special use case to develop around, especially when the app leverages OAuth for communication back to SharePoint (which will be 100% of the time in Office 365).  When a user launches an app from SharePoint, SharePoint posts a context token to the app.  Through this context token, the app can talk to Azure ACS to get an access token it can use for calling SharePoint APIs.  Without a context token, the app will be denied access to the SharePoint APIs.  Luckily, SharePoint 2013 provides an app redirect page specifically to refresh context tokens (/_layouts/15/appredirect.aspx).  TokenHelper.cs includes built-in functions to build the correct redirect url.  All apps for SharePoint should be written to take advantage of this.  In my case, I built a ContextUtil class to handle the app redirect and cache my tokens for an appropriate length of time.

public static ContextUtil Current{    get    {        ContextUtil spContext = HttpContext.Current.Session["SharePointContext"] as ContextUtil;        if (spContext == null || !spContext.IsValid)            spContext = new ContextUtil(HttpContext.Current.Request);

        if (spContext.IsValid)            return spContext;        else        {            HttpContext.Current.Response.Redirect(GetRedirectUrl());            return null;        }    }}

private static string GetRedirectUrl(){    string hostWebUrl = HttpContext.Current.Request["SPHostUrl"];    return TokenHelper.GetAppContextTokenRequestUrl(hostWebUrl,         HttpContext.Current.Server.UrlEncode(HttpContext.Current.Request.Url.ToString()));}


Style "Toggles"

In Part 1, I developed a reusable script to add the chrome control or host web styles based on a DisplayType url parameter.  I still think this is a great practice, as it allows the same app pages to be displayed in full-screen, app parts, and dialogs.  However, I’ve grown tired of the delayed style "toggle" that occurs when the chrome control or host styles are finally loaded into the app page (ugly page…wait for it…YAY, pretty page).  During preview, this sometimes took 4-8 seconds.  In the new Kudos app, I’ve defaulted the body display style to none on all pages and used callbacks to display the body once the chrome control or host styles have completely loaded.  This provides a much better user experience for the user:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="SharePointKudosSQLWeb.Pages.Default" %><%@ Register Src="~/UserControls/KudosStatsControl.ascx" TagPrefix="uc1" TagName="KudosStatsControl" %><%@ Register Src="~/UserControls/KudosControl.ascx" TagPrefix="uc1" TagName="KudosControl" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""><html xmlns=""><head runat="server">    <title>Kudos Employee Recognition</title>    <script type="text/javascript" src="../Scripts/MicrosoftAjax.js"></script>    <script type="text/javascript" src="../Scripts/jquery-1.7.1.min.js"></script>    <script type="text/javascript" src="../Scripts/KudosScript.js"></script></head><body style="display: none;">    <form id="form1" runat="server" autocomplete="off">


Final Thoughts

The new Kudos employee recognition app is a great reference for autohosted apps.  It makes good use of new SharePoint APIs, maximizes different UX delivery methods, and follows several patterns that I would consider best practices for developing apps for SharePoint.  Best of all, I’m providing 100% of the code to the community.  So pull down the code, take note of the patterns/intricacies, and go build some great apps for SharePoint! 

Kudos Part 2 Code: