ASP.NET MVC: Store Routes in the Database

• Posted by Ian Suttle on March 12, 2008
• Filed under .NET 3.5 | .Net Framework | ASP.Net | ASP.Net MVC

Download the sample MVC project for this postNOTE:  You should have ASP.NET MVC Preview 3 or later installed to run the provided download.
So far I’m a huge fan of the ASP.NET 3.5 MVC framework which is currently released as Preview 3.  Having worked with primarily Microsoft web frameworks MVC provides a refreshing perspective for what I consider an improved tier separation.

An immediate beef I have with ASP.NET MVC is defining the routes in the Global.asax.cs… I can’t change routes without recompiling and deploying code.  I thought it better to configure routes in a more dynamic environment, specifically the database, which is what this post will provide an example for.
I’ve broken the implementation into three main pieces:

1. Database schema for storing routes and route default parameters
2. LINQ to SQL model for accessing the routes
3. Class for handling route routines
   

Database Schema – “Mvc”

MvcRoutes Table

routeID – the primary key for a route.
routeName – simply for identification purposes.  This isn’t used in the app.
routePattern – the URL structure of the route.
routeIsActive – turn your route on or off.
routeOrder – routes are executed on a first match basis.  It may be important to order your routes so a more specific pattern is caught first before a more general pattern catches it… similar logic to exception handling.

MvcRoutes Sample Data

MvcRouteParams Table

paramID – the primary key and identifier for the route parameter.
routeID – foreign key to the MvcRoutes.RouteID value.
paramKey – route defaults are stored as a Dictionary.  This is the key for a default.
paramValue – the default value for the paramKey.
paramConstraint – a regular expression value specifying allowed values for the parameter
paramDataType – I’m actually not using this right now but it feels right to analyze the expected data type in a more complete sample… or somethingJ.

MvcRouteParams Sample Data

LINQ to SQL Model

The model is super simple… connect to your MVC routing database and drag the MvcRoutes and MvcRouteParams tables to a new LINQ to SQL class.

Now we can do some simple queries.

Route Helper Class – Routing.cs

The Routing.cs class provides a handful of methods for working with the RouteTable and interaction with the database.  In my project I included a new controller to handle resetting and displaying the routes which utilizes some of these additional methods.
 
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using System.Web.Routing;
 
using MvcAltRouting.Models;
 
namespace MvcAltRouting
{
    internal class Routing
    {
        public static RouteCollection Routes
        {
            get { return RouteTable.Routes; }
        }
 
        /// <summary>
        /// Gets the configured routes.
        /// </summary>
        /// <returns></returns>
        public static List<MvcRoute> GetConfiguredRoutes()
        {
            MvcRoutesDataContext db = new MvcRoutesDataContext();
            List<MvcRoute> dbRoutes = (from b in db.MvcRoutes
                                       where b.routeIsActive == true
                                       orderby b.routeOrder
                                       select b).ToList();
            return dbRoutes;
        }
 
        /// <summary>
        /// Clears current and readds application routes.
        /// </summary>
        public static void ResetAppRoutes()
        {
            Routes.Clear();
            SetAppRoutes();
        }
 
        /// <summary>
        /// Sets the application routes.
        /// </summary>
        public static void SetAppRoutes()
        {
            List<MvcRoute> configuredRoutes = GetConfiguredRoutes();
            SetAppRoutes(configuredRoutes);
        }
 
        /// <summary>
        /// Sets the application routes.
        /// </summary>
        /// <param name="configuredRoutes">Collection of routes to add to the application's routing table.</param>
        public static void SetAppRoutes(List<MvcRoute> configuredRoutes)
        {
            //add the routes to the RouteCollection
            foreach (MvcRoute route in configuredRoutes)
            {
                //create RouteValueDictionary objects to add the default values and contraints to the route
                RouteValueDictionary constraints = new RouteValueDictionary();
                RouteValueDictionary routeVals = new RouteValueDictionary();
 
                foreach (MvcRouteParam param in route.MvcRouteParams)
                {
                    routeVals.Add(param.paramKey, param.paramValue);
                    if (!string.IsNullOrEmpty(param.paramConstraint))
                    {
                        constraints.Add(param.paramKey, param.paramConstraint);
                    }
                }
 
                //create the route using the defaults set above
                RouteTable.Routes.Add(new Route(route.routePattern, new MvcRouteHandler())
                {
                    Defaults = routeVals,
                    Constraints = constraints
                });
            }
        }
    }
}

Global.asax.cs

Lastly change the Application_Start method to use the new Routing class to populate the application routes, removing the need for the routes defined within the Global.asax.cs.
using System;
 
namespace MvcAltRouting
{
    public class GlobalApplication : System.Web.HttpApplication
    {
        protected void Application_Start(object sender, EventArgs e)
        {
            Routing.SetAppRoutes();
        }
    }
}

That’s It!

Start up the provided project and navigate to the “Display Routes” link.  You can see the routes have been set from the database values we saw from above!
Have you been working with the ASP.NET MVC framework?  If so have you moved the route configuration out of the Global.asax.cs or are you happy with the default configuration for editing routes?

Download the sample MVC project for this postNOTE:  You should have ASP.NET MVC Preview 3 or later installed to run the provided download.