Creating an MVC 4 site on Windows Azure with Azure SQL Server

27 August 2012

Summary:
In this post, I discuss how to extend a JSON-based MVC 4 web site to support both static JSON and SQL Server data sources, with the source being configured by a "toggle switch" in web.config. I outline how to setup a SQL Database in Windows Azure, and then publish the site to Azure from within Visual Studio 2012.

Introduction

In a previous post I describe how I created an ASP.NET MVC 4 web site, where the data source is a static JSON text file. I talk about how to host that site as a Windows Azure Web Site. In this post, I want to expand on the original site by introducing support for SQL Server and the Microsoft Entity Framework (MEF). However, I want loose-coupling between the data-related code and the rest of the app. The goal being to change a single property in web.config, and for that to switch between static JSON and SQL Server data sources. The reason I want to take this approach is so that I can try out Azure SQL Database, but without being committed to a potentially expensive resource that the site's data requirements don't really merit.

Architecture

In the original static JSON-based site, a Post model class encapsulates the data source (a list of blog posts). It includes a static method that gets the data using an HttpWebRequest and exposes it as a List<Post>. A controller class creates an instance of the model and calls the model's static data method as required.

The problem with this approach is that the controller knows too much about the data source. In order to have flexibility in the relationship between the controller and the model's data source (repository), we need to introduce an interface for the repository, and get the controller to work through that:

Here are the IPost and IPostRepository interfaces (note that I'm using the [DataMember] attribute in IPost as it aids the parsing and mapping of JSON data):

IPost:

namespace rarcher.azurewebsites.net.Models
{
    public interface IPost
    {
        [DataMember]
        string Id { get; set; }

        [DataMember]
        string Url { get; set; }

        [DataMember]
        string Publish { get; set; }

        [DataMember]
        string Title { get; set; }

        [DataMember]
        string Description { get; set; }

        [DataMember]
        string Tags { get; set; }
    }
}

IPostRepository:

namespace rarcher.azurewebsites.net.Models
{
    public interface IPostRepository
    {
        List<Post> Posts { get; }
        Post Post(int id);
        void Create();
        void Delete();
        void Update();
        void SaveChanges();
    }
}

And here's the implementation of Post, PostDbRepository and PostJsonRepository:

Post:

namespace rarcher.azurewebsites.net.Models
{
    [DataContract]
    public class Post : IPost
    {
        [DataMember]
        public string Id { get; set; }

        [DataMember]
        public string Url { get; set; }

        [DataMember]
        public string Publish { get; set; }

        [DataMember]
        public string Title { get; set; }

        [DataMember]
        public string Description { get; set; }

        [DataMember]
        public string Tags { get; set; }
    }
}

PostDbRepository:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Caching;

namespace rarcher.azurewebsites.net.Models
{
    public class PostDbRepository : DbContext, IPostRepository, IPost
    {
        public DbSet<Post> DbPosts { get; set; }

        private List<Post> _posts;
        public List<Post> Posts
        {
            get
            {
                // We need the post list - is it in the cache? 
                if (HttpRuntime.Cache["PostList"] != null)
                    _posts = HttpRuntime.Cache["PostList"] as List<Post>;

                if (_posts == null)
                {
                    // It wasn't in the cache - request it from the Entity Framework...
                    try
                    {
                        _posts = DbPosts.ToList();  
                        _posts.Sort(_comparePosts);
                        
                        // Cache the post list for subsequent use
                        if (_posts != null)
                            HttpRuntime.Cache.Insert(
                                "PostList", _posts, null, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(10));
                    }
                    catch
                    {
                        _posts = null;
                    }
                }

                return _posts;
            }
        }

        // DB columns
        public string Id { get; set; }
        public string Url { get; set; }
        public string Publish { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string Tags { get; set; }

        private readonly PostComparer _comparePosts;

        public PostDbRepository() : base("PostDbRepositoryConnection")  // Name of connection string passed to DbContext
        {
            _comparePosts = new PostComparer();
        }

        public Post Post(int id)
        {
            throw new NotImplementedException();
        }

        public void Create()
        {
            throw new NotImplementedException();
        }

        public void Delete()
        {
            throw new NotImplementedException();
        }

        public void Update()
        {
            throw new NotImplementedException();
        }

        public new void SaveChanges()
        {
            throw new NotImplementedException();
        }

    }

    public class PostComparer : IComparer<Post>
    {
        :
        :
    }
}

PostJsonRepository:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Net;
using System.Web;
using System.Web.Caching;
using Newtonsoft.Json;

namespace rarcher.azurewebsites.net.Models
{
    public class PostJsonRepository : IPostRepository, IPost
    {
        private List<Post> _posts;
        public List<Post> Posts { get { return GetAllPosts(); }}

        public string Id
        {
            get { return Posts[Posts.Count - 1].Id; }
            set { Posts[Posts.Count - 1].Id = value; }
        }

        public string Url
        {
            get { return Posts[Posts.Count - 1].Url; }
            set { Posts[Posts.Count - 1].Url = value; }
        }

        public string Publish
        {
            get { return Posts[Posts.Count - 1].Publish; }
            set { Posts[Posts.Count - 1].Publish = value; }
        }

        public string Title
        {
            get { return Posts[Posts.Count - 1].Title; }
            set { Posts[Posts.Count - 1].Title = value; }
        }

        public string Description
        {
            get { return Posts[Posts.Count - 1].Description; }
            set { Posts[Posts.Count - 1].Description = value; }
        }

        public string Tags
        {
            get { return Posts[Posts.Count - 1].Tags; }
            set { Posts[Posts.Count - 1].Tags = value; }
        }

        private List<Post> GetAllPosts()
        {
            if (_posts == null)
            {
                // We need the post list - is it in the cache? 
                // (notice we're using HttpRuntime.Cache instead of HttpContext.Cache)
                if (HttpRuntime.Cache["PostList"] != null)
                    _posts = HttpRuntime.Cache["PostList"] as List<Post>;

                if (_posts == null)
                {
                    // It wasn't in the cache - request it as a JSON array...
                    try
                    {
                        // Get the Url of the Json from app settings in web.config - it'll be different for debug and release
                        string url = ConfigurationManager.AppSettings["postListJsonFilePath"];

                        #if (DEBUG)
                        {  
                            // This enables unit tests to work correctly
                            if(string.IsNullOrEmpty(url))
                                url = "http://localhost/rarcher.azurewebsites.net/Data/postsJson.txt";
                        }
                        #endif

                        if (!string.IsNullOrEmpty(url)) 
                            _posts = GetJsonData(url);

                        // Cache the post list for subsequent use
                        if (_posts != null)
                            HttpRuntime.Cache.Insert(
                                "PostList", _posts, null, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(10));
                    }
                    catch
                    {
                        _posts = null;
                    }
                }
            }

            return _posts;
        }

        private List<Post> GetJsonData(string path)
        {
            List<Post> tmpPostList = null;

            try
            {
                var uri = new Uri(path);
                var request = HttpWebRequest.Create(uri) as HttpWebRequest;

                HttpWebResponse response = null;

                if (request != null)
                {
                    request.Method = "GET";
                    request.ContentType = "application/json";
                    response = request.GetResponse() as HttpWebResponse;
                }

                if (response != null && response.StatusCode == HttpStatusCode.OK)
                {
                    string data;
                    StreamReader reader;

                    using (reader = new StreamReader(response.GetResponseStream()))
                    {
                        data = reader.ReadToEnd();  // Read the entire json content as a single string
                    }

                    // Use Json.NET to deserialize the json array...
                    if (!string.IsNullOrEmpty(data))
                        tmpPostList = JsonConvert.DeserializeObject<List<Post>>(data);
                }
            }
            catch
            {
                tmpPostList = null;
            }

            return tmpPostList;            
        }

        public Post Post(int id)
        {
            Post post = null;

            if (_posts == null)
                GetAllPosts();

            try
            {
                if (_posts != null)
                    post = _posts[id];
            }
            catch
            {
                post = null;
            }

            return post;
        }

        public void Create()
        {
            throw new NotSupportedException("PostJsonRepository does not support programmatic creation of posts");
        }

        public void Delete()
        {
            throw new NotSupportedException("PostJsonRepository does not support programmatic deletion of posts");
        }

        public void Update()
        {
            throw new NotSupportedException("PostJsonRepository does not support programmatic updating of posts");
        }

        public void SaveChanges()
        {
            // The implementation of SaveChanges() in PostJsonRepository does nothing
        }
    }
}

In the controller, we assume for the moment that "something" (actually, a dependency injection framework) will pass the concrete type of repository to use into the constructor:

namespace rarcher.azurewebsites.net.Controllers
{
    public class HomeController : Controller
    {
        private readonly IPostRepository _postRepository;

        private List<Post> Posts 
        {
            get { return _postRepository.Posts; }
        }

        public HomeController(IPostRepository postRepository)
        {
            // The concrete type of repository to use will be passed to us
            _postRepository = postRepository;
        }

        public ActionResult Index()
        {
            return View(Posts);
        }
        :
        :
    }
}

As you can see from the controller code above, it doesn't matter to the controller what type of repository we use. All we need to do now is add the "switch" to web.config and setup the dependency injection framework.

Configuring the Repository through web.config

It's a simple matter to add a key/value pair to the appSettings section of web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  :
  :
  <appSettings>
    <add key="Repository" value="PostJsonRepository" />
    <!-- Configure the repository required (PostJsonRepository or PostDbRepository) -->
  </appSettings>

We can then pick up the required value in code like so:

// Determine the type of repository (JSON or DB-based) we need to inject into the Home 
// and Post controllers. Look for the web.config <add key="Repository" ... /> setting
var repositoryTypeName = ConfigurationManager.AppSettings["Repository"];

// Get the actual .NET type of the repository we need
var repositoryType = Type.GetType("rarcher.azurewebsites.net.Models." + repositoryTypeName);

Setting up an IoC Container and a Custom Controller Factory

In order to inject the required repository type into the controller's constructor, we need to create a controller factory that will work together with the chosen dependency injection framework. For this example, I'm using Microsoft's Unity dependency injection/inversion of control (DI/IoC) container. I wrote about this process in detail previously (see MVC 4 TDD, IoC and Mocking). However, in overview, the process works as follows:

First, we need to use Visual Studio's NuGet package manager to add Unity to our project - just search for Unity and install it:

We now need to setup the Unity container and hook into the MVC framework's controller creation process by adding some code to the Global.asax.cs Application_Start() method. Remember that this method is called once when ASP.NET creates our application.

public class MvcApplication : HttpApplication
{
    protected void Application_Start()
    {
        AreaRegistration.RegisterAllAreas();

        FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
        RouteConfig.RegisterRoutes(RouteTable.Routes);
        BundleConfig.RegisterBundles(BundleTable.Bundles);

        // Setup the Unity IoC container
        IoCBootstrapper.Initialise();

        // Use our custom controller factory to create the appropriate controller by injecting
        // the relevant type of repository
        ControllerBuilder.Current.SetControllerFactory(new CustomControllerFactory());
    }
}

We need to create a custom controller factory because the default factory assumes controllers all have parameterless constructors, and some of ours require the type of repository being used. To create the custom controller factory, we need a class that derives from DefaultControllerFactory:

using System.Web.Mvc;
using System.Web.Routing;
using Microsoft.Practices.Unity;
using rarcher.azurewebsites.net.Controllers;

namespace rarcher.azurewebsites.net.IoC
{
    public class CustomControllerFactory : DefaultControllerFactory
    {
        public override IController CreateController(RequestContext requestContext, string controllerName)
        {
            try
            {
                if (!controllerName.Equals("Home") && !controllerName.Equals("Post"))
                {
                    // Get the default factory to create it
                    return base.CreateController(requestContext, controllerName);
                }

                // Create the required controller and auto-inject the required repository into its constructor...
                if (controllerName.Equals("Home"))
                    return IoCBootstrapper.UnityContainer.Resolve<HomeController>();

                if (controllerName.Equals("Post"))
                    return IoCBootstrapper.UnityContainer.Resolve<PostController>();

                return null;
            }
            catch
            {
                return null;
            }
        }
    }
}

The call to Resolve<T> causes the IoC container to look at what needs to be passed to either the HomeController or PostController constructor. It will see that a type that implements IPostRepository is required, and it'll look at what mappings it has (which we haven't setup yet) between IPostRepository and a concrete type. The IoC container then creates the controller and injects (passes) an instance of the required repository type. This all happens auto-magically, all we need to do is setup the type mapping in the IoC container's BuildUnityContainer() method:

namespace rarcher.azurewebsites.net.IoC
{
    public static class IoCBootstrapper
    {
        private static IUnityContainer _container;
        public  static IUnityContainer UnityContainer { get { return _container; } }

        public static void Initialise()
        {
            _container = BuildUnityContainer();

            DependencyResolver.SetResolver(new UnityDependencyResolver(_container));
        }

        private static IUnityContainer BuildUnityContainer()
        {
            _container = new UnityContainer();     

            // Determine the type of repository (JSON or DB-based) we need to inject into the Home 
            // and Post controllers. Look for the web.config <add key="Repository" ... /> setting
            var repositoryTypeName = ConfigurationManager.AppSettings["Repository"];

            if (!string.IsNullOrEmpty(repositoryTypeName))
            {
                // Get the actual .NET type of the repository we need
                var repositoryType = Type.GetType("rarcher.azurewebsites.net.Models." + repositoryTypeName);

                // Setup a mapping between IPostRepository and the required concrete repository type
                _container.RegisterType(typeof(IPostRepository), repositoryType);
            }

            return _container;
        }
    }
}

We now have all the pieces in place, so that simply changing the value of the "Repository" appSetting in web.config to be either "PostJsonRepository" or "PostDbRepository" causes the IoC container and custom controller factory to automatically create a controller with the correct repository type.

Setting up the Entity Framework and Code Migrations

There are, of course, numerous ways of using SQL Server with ASP.NET MVC. However, one of the most straightforward is by using the Microsoft Entity Framework (MEF). The package for this should be added by default to your project. To check the version installed, right-click the project in Solution Explorer and select Manage NuGet Packages:

A great feature of MEF is the ability to use code-first database migrations. This means that you can use C# code to control the various states of the database, including initial database and table creation, inserting/updating data, etc. What's neat is that all these migration states (normally referred to as just migrations) are kept as part of your project (and thus part of version control, if required). So you have a complete record of what you've done to the database, and can "re-play" or "undo" the migrations at a later point. This can be very useful when moving from (say) development to production environments, or even from one development machine to another. I think this is a wonderful feature of MEF!

The first thing to do is setup your project to work with database migrations. This is done from the Package Manager Console window. First, type Get-Help EntityFramework (this shows everything's installed and working correctly):

Now, type: Enable-Migrations

You should see that a Migrations folder has been added to your project. This is where the code migrations are stored. The folder should contain a file name Configuration.cs. This file contains the code necessary for inserting/updating your database. Here I've added some code to add several rows to the "Posts" table:

using rarcher.azurewebsites.net.Models;

namespace rarcher.azurewebsites.net.Migrations
{
    using System.Data.Entity.Migrations;

    internal sealed class Configuration : DbMigrationsConfiguration<PostDbRepository>
    {
        public Configuration()
        {
            //  IMPORTANT: Make sure this is set to true to enable migrations (it's set to false by default)
            AutomaticMigrationsEnabled = true;
        }

        protected override void Seed(PostDbRepository context)
        {
            //  This method will be called after migrating to the latest version

            context.DbPosts.AddOrUpdate(
                d => d.Title,
                new Post
                {
                    Id = "0",
                    Url = "_WcfSimpleService",
                    Publish = "01 May 11",
                    Title = "Intro to WCF - A Simple Service",
                    Description = "WCF Simple Service",
                    Tags = ""
                },

                new Post
                {
                    Id = "1",
                    Url = "_WcfSelfHosting",
                    Publish = "02 May 11",
                    Title = "Intro to WCF - Self-Hosting",
                    Description = "WCF Self-Hosting",
                    Tags = ""
                },
                :
                :
                new Post
                {
                    Id = "21",
                    Url = "_WinAzureSql",
                    Publish = "25 Aug 12",
                    Title = "Create an MVC 4 site on Windows Azure with Azure SQL Server",
                    Description = "How to setup an ASP.NET MVC 4 app hosted on Windows Azure with Azure SQL Server",
                    Tags = ""
                });

        }
    }
}

To setup the initial state of the database type: Add-Migration InitialState

You'll see a file named {timestamp}_InitialState.cs has been created. This is the code responsible for creating/deleting the "Posts" table:

namespace rarcher.azurewebsites.net.Migrations
{
    using System.Data.Entity.Migrations;
    
    public partial class InitialState : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Posts",
                c => new
                    {
                        Id = c.String(nullable: false, maxLength: 128),
                        Url = c.String(),
                        Publish = c.String(),
                        Title = c.String(),
                        Description = c.String(),
                        Tags = c.String(),
                    })
                .PrimaryKey(t => t.Id);
            
        }
        
        public override void Down()
        {
            DropTable("dbo.Posts");
        }
    }
}

Type: Update-Database -verbose

You should see the required table created, along with a "MigrationHistory" table (which holds details of changes to the database). If you open the table (depending on how you have things configured, most likely the database will have been created as a .mdf file in your project's App_Data folder) and browse the data, you should see that the Configuration.Seed() method has inserted the initial data set.

Note that:

  • Migrations can be added at any time using: Add-Migration migrationName
  • Migrations can be rolled-back/forward using: Update-Database -TargetMigration:migrationName
  • All migrations may be rolled-back using: Update-Database -TargetMigration:0

Setting up Azure SQL Database

We now have everything in place to be able to support both SQL- and JSON-based repositories. All that's left to do is setup an Azure SQL Database and configure the Visual Studio project to publish directly to Azure.

Assuming you have a Windows Azure account, login to the management portal and create a SQL Server:



Now go to the server's Dashboard page and copy the server name, admin login (which you created as part of the SQL Server creation process) and the management URL:

Select the server's Configure page and setup the server to allow direct connections from your development machine:

Go to the Dashboard tab for your Azure web site and click Download publish profile:

The publish profile is a text file that contains all the authentication and other details required to allow Visual Studio to connect to your site on Azure. To import the publish profile into Visual Studio, right-click the project in Solution Explorer and select Publish. Click Import and then navigate to the downloaded publish profile. You can now review the various connection settings:

On the Settings page of the publish profile, make sure to check Execute Code First Migrations, then click the [...] button next to the empty connection string textbox:

Enter the address of the Azure SQL Database server (which you noted earlier). This is shown is the form https://servername.domain.net on the Azure portal, but you should change the "https" to "tcp" in order for the connection to work from Visual Studio. Also enter your SQL login account details and the name of the database you want to be created:

When you click OK you'll be prompted if you want to create the database - select yes.

All the publishing details, including the SQL connection string are stored in the Web Deploy.pubxml file:

If you go back to the Azure portal you should see that the Posts database has been created:

And you ought to be able connect directly to the Azure SQL Database from Visual Studio - simply add a server to the SQL Server window:

Deploying to Azure

When you're ready to publish everything to Azure, right-click the project in Visual Studio and select Publish. Select the Preview tab. You can preview which files have changed and will be published by clicking the Start Preview button:

You can then publish the changed files, database migrations, etc., by clicking Publish:

Conclusion

In this post, we looked at how to extend a JSON-based MVC 4 web site to support both static JSON and SQL Server data sources, with the source being configured by a "toggle switch" in web.config. I outlined how to setup a SQL Database in Windows Azure, and then published the site to Azure from within Visual Studio 2012.