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
SELECT TOP 5 OT.Id as Id, O.Name As Name, OT.StartTime As StartTime FROM Call OT
LEFT OUTER JOIN Outlet O ON OT.Outlet_SiteUid=O.SiteUid AND OT.OutletUid=O.Uid
WHERE OT.CreationDate BETWEEN '2011-10-01' AND '2012-10-31'
FOR XML PATH('Result'), ROOT('Results')
XML Formatted result
<Results>
<Result>
<Id>CARC00000607</Id>
<Name>CENTRAL (ZHONG HUAN CHA CAN TING) - 313 @SOMERSET</Name>
<StartTime>2011-11-28T09:22:28</StartTime>
</Result>
<Result>
<Id>CARC00000608</Id>
<Name>CENTRAL (ZHONG HUAN CHA CAN TING) - 313 @SOMERSET</Name>
<StartTime>2011-11-28T10:16:12</StartTime>
</Result>
</Results>
For ease of explanation purpose I have copied the XML result to a XML file called "SQLResult1.xml".
DynamicXmlConverter helper class
public class DynamicXmlConverter
{
/// <summary>
/// http://msdn.microsoft.com/en-us/library/system.dynamic.expandoobject.aspx
/// http://msdn.microsoft.com/en-us/library/vstudio/dd264741.aspx
/// </summary>
/// <param name="descendantName"></param>
/// <param name="filePath"></param>
/// <returns>List<dynamic></returns>
public static List<dynamic> GenerateDatasource(string descendantName, string filePath)
{
//intialize list of dynamic objects
var dataList = new List<dynamic>();
//xml-file loading
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)
{
//creating the 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())
{
//assigning of element name as propertyName
var 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
public class HomeController : Controller
{
public ActionResult LoadGrid()
{
var dataList = DynamicXmlConverter.GenerateDatasource("Result", Server.MapPath("SQLResult1.xml"));
return View(dataList);
}
}
View
@{
ViewBag.Title = "Home Page";
}
@model System.Collections.IEnumerable
@{
var grid = new WebGrid((IEnumerable<dynamic>)Model, canPage: true, rowsPerPage: 5);
}
@grid.GetHtml()
Output
