Monday, April 4, 2011

Search engine friendly URLs in ASP.NET MVC using dynamic routing and a SQL Server database

Search engine friendly URLs, also known as rewritten URLs, is a feature where a more logical and readable URL is exposed to the user. An example of such an URL is

Today ASP.NET MVC supports friendly URLs out of the box through its routing feature, but the default implementation only supports hard coding of routes and pre-population of routes (using your own data source) during application startup. This is often not an ideal solution, especially when there are thousands of routes. In this post I will go through how we can change the default implementation into a dynamic one that reads the routing information from a database.

1) Let's begin by creating a Page table in our database:

Some sample data:

This table will contain a list of all the pages on our website, together with the friendly URL and the controller it maps to. For instance when a user request the URL the controller Article would be loaded.

2) Next we need an entity class that will represent the page row:

public class PageItem
 public int PageId { get; set; }
 public string Title { get; set; }
 public string FriendlyUrl { get; set; }
 public string ControllerName { get; set; }

3) Then we need a new class called PageManager that will be responsible for querying the page table in the database for a row that matches the given friendly URL, and for mapping the row to the PageItem type:

public class PageManager
 public static PageItem GetPageByFriendlyUrl(string friendlyUrl)
  PageItem page = null;

  using (var cmd = new SqlCommand())
   cmd.Connection = new SqlConnection("Server=(local);Database=MyWebsite;Trusted_Connection=True;");
   cmd.CommandText = "select * from Page where FriendlyUrl = @FriendlyUrl";
   cmd.Parameters.Add("@FriendlyUrl", System.Data.SqlDbType.NVarChar).Value = friendlyUrl;
   using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    if (reader.Read())
     page = new PageItem();
     page.PageId = (int) reader["PageId"];
     page.Title = (string) reader["Title"];
     page.ControllerName = (string) reader["ControllerName"];
     page.FriendlyUrl = (string) reader["FriendlyUrl"];

   return page;

4) The next thing we need to do is to create a custom route handler. This class will be triggered by the ASP.NET MVC framework when a new request comes in to the server. This happens before the Controller has been created, so it gives us the chance to tell ASP.NET MVC that we want to load another controller than it would originally have. So create a new class named FriendlyUrlRouteHandler:

public class FriendlyUrlRouteHandler : System.Web.Mvc.MvcRouteHandler
 protected override IHttpHandler GetHttpHandler(System.Web.Routing.RequestContext requestContext)
  var friendlyUrl = (string) requestContext.RouteData.Values["FriendlyUrl"];

  PageItem page = null;

  if (!string.IsNullOrEmpty(friendlyUrl))
   page = PageManager.GetPageByFriendlyUrl(friendlyUrl);

  if (page == null)
   page = PageManager.GetPageByFriendlyUrl("home");

  requestContext.RouteData.Values["controller"] = page.ControllerName;
  requestContext.RouteData.Values["action"] = "index";
  requestContext.RouteData.Values["id"] = page.PageId;

  return base.GetHttpHandler(requestContext);

In this route handler we will take the URL. If it is not set, we will set it to the default which is home. Then we will query the Page table for a row that matches the friendly URL. Further we will change the RouteData.Values["controller"] to the controller we want to load. After ASP.NET MVC has executed the code it will load the controller that we defined in the RouteData.Values["controller"].

5) And the last thing we need to do is to tell AS.NET MVC that we want to use our own route handler. So modify the RegisterRoutes method in the Global.asax to the following:

public static void RegisterRoutes(RouteCollection routes)
 routes.RouteExistingFiles = false;

 ).RouteHandler = new FriendlyUrlRouteHandler();

That's it. Now our website has full support for friendly URLs, both single level URLs like "about" but also multi-level URLs like "about-us/employees/eric".