Binding WebGrid to Dynamic Collection (XML, SQL Server 2008 R2, dynamic, ExpandoObject, MVC 3, WebGrid)

Recently I have experienced with some of the new features (I guess these components are rarely used) of C# 4.0 such the dynamic type, ExpandoObject. My initial requirement was to build a dynamically created object collection out of XML formatted SQL result. Then it has to be used as a data-source for my grid.

Note: The select statement was dynamically generated and the result were vary from one query to another. My intent was to generate the data-source (list of objects) without using a well-structured mapping-class. With the use of ExpandoObject I was able to generate an object in run-time with dynamic properties.

SQL Query which return a XML formatted result

sql-query

XML Formatted result

query-result

For ease of explanation purpose I have copied the XML result to a XML file called “SQLResult1.xml”.

Helper Class – DynamicXmlConverter

public class XmlDynamicParser
{
    /// <summary>
    /// returns a List<dynamic> generated by xml datasource
    /// </summary>
    /// <param name="descendantName"></param>
    /// <param name="filePath"></param>
    /// <returns>List<dynamic></returns>
    public static List<dynamic> GenerateGridDataSource(string descendantName, string filePath)
    {
        //initialize list of dynamic objects
        var dataList = new List<dynamic>();

        //xml-string
        var doc = XDocument.Load(filePath);

        //selecting all the Descendats of Ex: "Result"
        var descendants = doc.Descendants(descendantName);

        //loop through each Descendats
        foreach (var descendant in descendants)
        {
            //creatingthe ExpandoObject
            dynamic expandoObject = new ExpandoObject();

            //casting it to a dictionary object
            var dictionaryExpandoObject = (IDictionary<string, object>)expandoObject;

            //loop through each elements of descendant
            foreach (var element in descendant.Elements())
            {
                //assiging of element name as propertyName
                string propertyName = element.Name.LocalName;

                //adding the property name and value to the dictionary
                dictionaryExpandoObject.Add(propertyName, element.Value);
            }

            //finally add each ExpandoObject to list
            dataList.Add(dictionaryExpandoObject);

        }

        return dataList;
    }
}

Controller – HomeController

[Authorize]
public class HomeController : Controller
{
    public ActionResult Index()
    {
        return View();
    }

    public ActionResult LoadGrid()
    {
        var dataList = XmlDynamicParser.GenerateGridDataSource("Result", Server.MapPath("SQLResult1.xml"));
        return View(dataList);
    }
}

View – Load Grid

@{
    ViewBag.Title = "Load Grid";
}
@using System.Collections
@model IEnumerable
@{
    var grid = new WebGrid((IEnumerable<dynamic>)Model, canPage: true, rowsPerPage: 5);
}
@grid.GetHtml()

Output

grid