locked
Best Way To Store Menu Items RRS feed

  • Question

  • User490419145 posted

    I have multiple websites, where the menu is built by connecting to SQL, pulling some results, iterating through them, and printing HTML.  I normally do this on Page_Load of the MasterPage.

    My question is, is there a better way to do this?  I still want the menu to be updated whenever SQL is updated, but am concerned a connection to the database on every single page load is not the best option for scalability.  How do you dynamically updates item that are on the MasterPage like navigation, etc.

    Thursday, June 21, 2012 11:26 AM

Answers

  • User870198346 posted

    Hi Incognito.C0der,

    If you need changes from your SQL-database 'real-time' then I guess there's no other option (for instance; you edit the menu on a page that displays the menu and want to see it change at once).

    When however a 'near real-time' solution is sufficient then you could use 'SqlCacheDependency' to accomplish this (see http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx and http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx).

    To make this happen you need to:

    • build your menu from the database (a treeview of menu-items)
    • get cachedependencies for all related tables
    • add it to cache with these cachedependencies
    • next time, check cache first, if empty then rebuild your menu

    When you render your menu you don't use your database directly but render it from the treeview in cache. Here's some pseudo-code:

    public void RenderMenu(Placeholder menuPlaceholder)

    {

      var menu = GetMenuFromCache();

      //Render menu here

    }

    private IEnumerable<MenuItem> GetMenuFromCache()

    {

      var menu = Cache.Get("Menu") as IEnumerable<MenuItem>;

      if(menu != null)

      {

        return menu;

      }

      var sqlCommand = GetMenuSqlCommand();

      menu = GetMenuFromDatabase(sqlCommand);

      var sqlDependency = GetSqlDependency(sqlCommand)

      Cache.Add("Menu", menu, sqlDependency, DateTime.Now.AddHours(1), Cache.NoSlidingExpiration);

      return menu;

    }

    The implementation of "GetSqlDependency" depends on the version of SQLServer and the version of the .NET framework that you're using.

    For more details see:

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 21, 2012 3:42 PM

All replies