Using LINQ to SQL for clean and quick Database Access/Interactions.

Update: October 26, 2016 - I would now recomend using this replacement guide using the built in PetaPoco instead of this method as it is much cleaner and requires significantly less DBA code and produced much smaller binaries.  There are also T4 Templates that will generate models form your database so you can design in code and generate database constructs automatically, or create tables and then auto-generate code form those, whichever way you prefer.  I still use the Columns features below for when creating forms.

This post is a demonstration/instructions for very easily and cleanly working with SQL data in a .NET application using LINQ to SQL.  There are lots of resources out there about this technology but my goal here is to present a very clean and thin interface to utilizing LING to SQL.  Read on for explanations and samples.

Read more »

Load Umbraco nodes by Guid and print Guid's in Razor

Umbraco internals provide methods for accessing objects by Guid instead of ID.  This is necessary when using multiple environments and moving files around with Courier.  The reason being that ID's are not unique across instances and as items are moved around they will get assigned new ID's.  The following for example will not work as expected when deployed with Courier.

@Model.NodeById("Some static pageId").myVariable

To do this properly the Guid is needed instead. The problem though is that the Razor DynamicNode object doesn't expose or let you load nodes by the Guid.  The included class can be added to your App_Code directory to allow the following code samples to work in Razor.

using JSP;
// Get a object by the Guid
@Model.NodeByGuid("someGuid").myVariable
// Print a object's Guid
@Model.UniqueId()

GuidExtensions.cs

using System;
using umbraco;
using umbraco.MacroEngines;
using umbraco.cms.businesslogic;

namespace JSP
{
    /// <summary>
    /// Extensions to the umbraco framework.
    /// </summary>
    public static class GuidExtensions
    {

        /// <summary>
        /// Get a node by the unique Guid.  This is needed for sites where courier is in use.
        /// </summary>
        /// <param name="self">Current Page</param>
        /// <param name="guid">Guid to load.</param>
        /// <returns>DynamicNode</returns>
        public static DynamicNode NodeByGuid(this DynamicNode self, string guid)
        {
            return self.NodeByGuid(new Guid(guid));
        }

        /// <summary>
        /// Get a node by the unique Guid.  This is needed for sites where courier is in use.
        /// </summary>
        /// <param name="self">Current Page</param>
        /// <param name="guid">Guid to load.</param>
        /// <returns>DynamicNode</returns>
        public static DynamicNode NodeByGuid(this DynamicNode self, Guid guid)
        {
            return self.NodeById(new CMSNode(guid).Id);
        }

        /// <summary>
        /// Get the unique ID for a selected node.
        /// </summary>
        /// <param name="self">Node</param>
        /// <returns>guid</returns>
        public static string UniqueId(this DynamicNode self)
        {
            return new CMSNode(self.Id).UniqueId.ToString();
        }
    }
}

Edit Macros that are Cached in Umbraco

One of the really annoying problems in Umbraco is that is when editing Macro Scripting Files for Macros that are cached your changes will not appear until the cache expires and is rebuilt.  To get around this you have to go and turn off caching on the macro that you are working on and then remember to turn it back on when done.

A much nicer solution to this problem is simply to clear out the cached versions of the macro whenever its script file is saved.  In this way the new version of the macro will always be displayed when refreshing the page after any changes.  This code can be added to the App_Code directory of an umbraco install to do jus that.

ClearMacroCache.cs

using System.Web;
using umbraco.BusinessLogic;
using umbraco.IO;
using System.IO;
using System.Web.Hosting;
using ClientDependency.Core.Config;
using System.Text.RegularExpressions;
using System.Linq;
using umbraco.cms.businesslogic.macro;
using umbraco.cms.businesslogic.cache;

namespace JSP
{
    public class ClearMacroCache : ApplicationBase
    {
        public ClearMacroCache()
        {
            // Get the macro folder.
            var macroPath = HttpContext.Current.Server.MapPath(SystemDirectories.MacroScripts);

            // Watch for file changes ot any macros.
            HttpContext.Current.Application.Add("macroWatcher", new FileSystemWatcher(macroPath));
            var macroFsw = (FileSystemWatcher)HttpContext.Current.Application["macroWatcher"];
            macroFsw.EnableRaisingEvents = true;
            macroFsw.IncludeSubdirectories = true;

            // Trigger an event on any change.
            macroFsw.Changed += new FileSystemEventHandler(expireClientDependency);
            macroFsw.Created += new FileSystemEventHandler(expireClientDependency);
            macroFsw.Deleted += new FileSystemEventHandler(expireClientDependency);
        }

        /// <summary>
        /// This method is called each time a macro file is saved/updated and will clear out and cached versions 
        ///     of the macro to eliminate cacheing problems when developing macros.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void expireClientDependency(object sender, FileSystemEventArgs e)
        {
            // If this is a real macro file.
            Regex reg = new Regex(@"\d+_");
            if(!reg.Match(e.Name).Success)
            {
                var macro = Macro.GetAll().SingleOrDefault(i => i.ScriptingFile.ToLower() == e.Name.ToLower());

                if (macro != null)
                    Cache.ClearCacheByKeySearch("macroHtml_" + macro.Alias);
            }
        }
    }
}

LINQ OrderBy sorting without a Strong Typed parameter expression.

When setting up a simple ASPX page with a DataGrid to quickly display a LINQ to SQL result without having to write a bunch of paging and sorting code I ran into a bit of an issue. The loading and paging worked perfectly with the following sample but I could not get sorting to work and a clean and concise manner.

<asp:DataGrid ID="Data" runat="server" AllowPaging="True" AllowSorting="True" 
    onpageindexchanged="Data_PageIndexChanged" onsortcommand="Data_SortCommand">
</asp:DataGrid>

FormLandingPageRepository repo = new FormLandingPageRepository();

protected void Page_Load(object sender, EventArgs e)
{
    Data.DataSource = repo.Find().OrderByDescending(i => i.dateAdded);
    Data.DataBind();
}

protected void Data_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
    Data.CurrentPageIndex = e.NewPageIndex;
    
    Data.DataSource = repo.Find().OrderByDescending(i => i.dateAdded);
    Data.DataBind();
}

protected void Data_SortCommand(object source, DataGridSortCommandEventArgs e)
{     
    repo.Find().OrderBy(e.SortExpression);
    Data.DataBind();
}

The problem turned out to be that the OrderBy operations in LINQ require strong types clauses in Expressions but my DataGrid is returning a simple string as the order by expression.  I could get around this by creating a switch statment and converting to a Strong Typed expression but that would require a lot of hard coded logic for every table.

Instead I found this Extensions after much searching and testing that adds the ability to do OrderBy statements in LINQ with a string value instead.  Thus after adding the Extension code the above sort by works as exacted. (Note that the sort expression will need to be stored in the ViewState and sorting logic applied in PageIndexChanged as well for Paging+Sorting to work.)

SortExstensions.cs

using System;
using System.Text.RegularExpressions;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace JSP
{
    public static class SortExtensions
    {
        /// <summary>
        /// Sort Directions.
        /// </summary>
        enum SortMode
        {
            OrderBy,
            OrderByDescending,
            ThenBy,
            ThenByDescending
        }

        /// <summary>
        /// Sorts the elements of a sequence in ascending order according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property)
        {
            // If this is a sort list then OrderBy the first element and then ThenBy the rest.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] {','}, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.OrderBy).ThenBy(parts[1]);
            }
                
            return ApplyOrder<T>(source, property, SortMode.OrderBy);
        }

        /// <summary>
        /// Sorts the elements of a sequence in descending order according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string property)
        {
            // If this is a sort list then OrderBy the first element and then ThenBy the rest.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] { ',' }, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.OrderByDescending).ThenByDescending(parts[1]);
            }

            return ApplyOrder<T>(source, property, SortMode.OrderByDescending);
        }

        /// <summary>
        ///  Performs a subsequent ordering of the elements in a sequence in ascending order according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> ThenBy<T>(this IOrderedQueryable<T> source, string property)
        {
            // If ther are multiple sort items then sort by each in order.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] { ',' }, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.ThenBy).ThenBy(parts[1]);
            }

            return ApplyOrder<T>(source, property, SortMode.ThenBy);
        }

        /// <summary>
        ///  Performs a subsequent ordering of the elements in a sequence in descending order, according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> ThenByDescending<T>(this IOrderedQueryable<T> source, string property)
        {
            // If ther are multiple sort items then sort by each in order.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] { ',' }, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.ThenByDescending).ThenByDescending(parts[1]);
            }

            return ApplyOrder<T>(source, property, SortMode.ThenByDescending);
        }

        /// <summary>
        /// Apply a custom Order By statment to an IQueryable.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <param name="methodName">Sort method to use.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, SortMode methodName)
        {
            string[] props = property.Split('.');

            Type type = typeof(T);
            ParameterExpression arg = Expression.Parameter(type, "x");
            Expression expr = arg;

            foreach (string prop in props)
            {
                PropertyInfo pi = type.GetProperty(prop);
                expr = Expression.Property(expr, pi);
                type = pi.PropertyType;
            }

            Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
            LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

            object result = typeof(Queryable).GetMethods().Single(
                    method => method.Name == methodName.ToString("g")
                            && method.IsGenericMethodDefinition
                            && method.GetGenericArguments().Length == 2
                            && method.GetParameters().Length == 2)
                    .MakeGenericMethod(typeof(T), type)
                    .Invoke(null, new object[] { source, lambda });
            return (IOrderedQueryable<T>)result;
        }
    }
}

Updatting c# applicationSettings in a ASP.NET Web Application

I have a few .NET web applications, using MVC, that make use of applicationSettings in their configuration.  These settings are semi-constant but do need to be updated from time to time.  I was trying to make an edit screen in the web application for developers so they could edit the applicationSettings without having to get on the server and manually edit the Web.config file.  As expected the applicationSettings are read only when accesses directly in the application and can not be updated.  Also it's not possible to configure the settings as userSettings when running as a web application.   Though we could do this by manualy reading and writing the file I was looking for a simpler way to do it.

After some tinkering I found a fairly simple way of doing this.  Basically we can use a custom ConfigurationManager instance to read the Web.config independently of the application and update this instance of the configuration.  Then we just call the save method and the edited data is saved out.  Here is the code for a simple update call.

Note that this code is tailored for MVC and is looping tough the FormCollection values. You could also explicitly read the post variables as parameters of the post action, or use this outside of MVC entirely. Just keep in mind that however you do it you can't loop by clientSection.Settings as the requirement to remove/re-add each updated value prevents this.

using System.Configuration;

[AcceptVerbs(HttpVerbs.Post), Authorize(Roles = "Admin")]
public ActionResult SaveSettings(FormCollection collection)
{
/* This section of code uses a custom configuration manager to edit the Web.config application settings.
 * These settings are normally read only but web apps don't support user scoped settings.
 * This set of variables is used for system features, not runtime tracking so it it only updated when an 
 *  administrator logs in to reconfigure the system.
 * 
 * Author: Jeremy Pyne 
 * Licence: CC:BY/NC/SA  http://creativecommons.org/licenses/by-nc-sa/3.0/
 */

// Load the Web.config file for editing.  A custom mapping to the file is needed as the default to to match the application's exe filename witch we don't have.
System.Configuration.Configuration config = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap() {ExeConfigFilename = HttpContext.Server.MapPath("..\\Web.config") }, ConfigurationUserLevel.None);

// Find the applicationSettings group.
ConfigurationSectionGroup group = config.SectionGroups["applicationSettings"];
if (group == null)
    throw new AjaxException("Could not find application settings.");

// Find this applications section. Note: APP needs to be replaced with the namespace of your project.
ClientSettingsSection clientSection = group.Sections["APP.Properties.Settings"] as ClientSettingsSection;
if (clientSection == null)
        throw new AjaxException("Could not find Hines settings.");

// Loop through each value we are trying to update.
foreach (string key in collection.AllKeys)
{
    // Look for a setting in the config that has the same name as the current variable.
    SettingElement settingElement = clientSection.Settings.Get(key);

    // Only update values that are present in the config file.
    if (settingElement != null)
    {
        string value = collection[key];

        // Is this is an xml value then we need to do some conversion instead.  This currently only supports the StringCollection class.
        if (settingElement.SerializeAs == SettingsSerializeAs.Xml)
        {
            // Convert the form post (bob,apple,sam) to a StringCollection object.
            System.Collections.Specialized.StringCollection sc = new System.Collections.Specialized.StringCollection();
            sc.AddRange(value.Split(new char[] { ',' }));

            // Make an XML Serilization of the new StringCollection
            System.Xml.Serialization.XmlSerializer ser = new System.Xml.Serialization.XmlSerializer(typeof(System.Collections.Specialized.StringCollection));
            System.IO.StringWriter writer = new System.IO.StringWriter();
            ser.Serialize(writer, sc);

            // Get the xml code and trim the xml definition line from the top.
            value = writer.ToString().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "");
        }

        // This is a custom override for MVC checkboxes.  They post as 'false' when unchecked and 'true,false' when selected.
        if(value == "true,false")
            value = "True";
        if(value == "false")
            value = "False";

        // Replace the settings with a updated settings.  It is necessary to do it this way instead of 
        //  updating it in place so that the configuration manager recognize that the setting has changed.
        // Also we can't just look through clientSection.Settings and update that way because then we wouldn't
        //  to do this exact thing.
        clientSection.Settings.Remove(settingElement);
        settingElement.Value.ValueXml.InnerXml = value;
        clientSection.Settings.Add(settingElement);
    }
}

// Save any changes to the configuration file.  Don't set forceSaveAll or other parts of the Web.config will get overwritten and break.
config.Save(ConfigurationSaveMode.Full);
}