Tuesday, 18 February 2014

Traversing paged results using Massive data access wrapper

Massive, a library created by brilliant developer Rob Conery and Jon Skeet's frequent humorous side-kick, has got a lot of nice features. Its best features are ease of use and dynamic appeal. It is also fast and lightweight. After testing Massive, I wanted to create an extension method to traverse paged results returned from Massive. The code below is tested against the AdventureWorks2012 database. First off, install Massive. I have created a simple Console Project to test this out and then use the Library Package Manager to install Massive:

Install-Package Massive -Version 1.1.0 

Remember to add the -Version switch above. Then install the AdventureWorks 2012 database here: http://msftdbprodsamples.codeplex.com/releases/view/55330 Add a connection string against the Adventure Works 2012 database after installing this on your developer PC. Use Data Explorer or Server Explorer in Visual Studio. Rename the connection string in your app.config or web.config to "adventureworks2012". Define a model entity for the Production.Product table next:

    public class Products : DynamicModel
    {
        public Products()
            : base("adventureworks2012", "Production.Product", "productid")
        {

        }
    }

To add a table entity in Massive, specify the connection string name, then the name of the table and then the primary keys. If you omit the name of the connection string, the first connection string found is used. Next, the code for the extension method to work against this DynamicModel inherited class and other inherited classes from DynamicModel:

     public static class MassiveExtensions
    {

        public static void MassivePagedAction(this DynamicModel massiveObject, int pageSize = 20, int startPageIndex = 1, 
            int endPageIndex = -1, Action<int, dynamic> pageAction = null, Action<dynamic> itemAction = null)
        {

            var pages = new Dictionary<int, dynamic>(); 
            var firstPage = massiveObject.Paged(currentPage: startPageIndex, pageSize: pageSize);
            pages.Add(startPageIndex, firstPage);
            int endIndex = endPageIndex == -1 ? firstPage.TotalPages : Math.Min(firstPage.TotalPages, endPageIndex); 
            
            for (int currentPageIndex = startPageIndex + 1; currentPageIndex <= endIndex; currentPageIndex++)
            {
                var currentPage = massiveObject.Paged(currentPage: currentPageIndex, pageSize: pageSize);
                pages.Add(currentPageIndex, currentPage); 
            }

            foreach (var keyValuePair in pages)
            {
                if (pageAction != null)
                    pageAction(keyValuePair.Key, keyValuePair.Value);
                foreach (var item in keyValuePair.Value.Items)
                {
                    if (itemAction != null)
                        itemAction(item); 
                }
            }

        }

    }


The extension method puts the paged results in a dictionary, where the keys are the page index and the value is the dynamic object that Massive returns, containing the paged data. There are numerous optional parameters to this extension method. The page size is default set to 20, if not specified. The start page index is 1, which is actually the default value. Rob Conery should perhaps have chosen the page index to be 0-based, as this is the standard in C#, but querying for page index 0 will give empty results. The end page index is default set to -1. This means that all pages will be returned, until there are not more pages. If you set the endPageIndex to a value less than firstPage.TotalPages inside the extension method, i.e. the actual total pages in the database, only pages up to the endPageIndex is returned. To get a single page, pass in a start and end index that differs with 1 (where end index has a value of one larger than start index). It is also possible to pass in page actions and item actions here. They default to null, but obviously at least an item action is desired to be set. A variant of the method above to allow a Func to be passed in to for example return results to the caller is interesting. Using the extension method above is shown next:

//GET PAGES RESULTS OF THE PRODUCTS TABLE USING A PAGE SIZE OF FIVE. GET ENTIRE TABLE.

            DynamicModel table = new Products();
          
            table.MassivePagedAction(pageSize:5,pageAction: (indx, page) => 
             Console.WriteLine("\nProducts in page # {0}:\n", indx), 
             itemAction: item => Console.WriteLine(item.Name)); 


//SAMPLE OUTPUT:


------ Test started: Assembly: TestMassive.exe ------


Products in page # 1:

Adjustable Race
Bearing Ball
BB Ball Bearing
Headset Ball Bearings
Blade

Products in page # 2:

LL Crankarm
ML Crankarm
HL Crankarm
Chainring Bolts
Chainring Nut

Products in page # 3:

..

//This resulted in 101 pages - There are 504 products in the AdventureWorks 2012 database (primarily mountain bikes and clothes+equipment)

Products in page # 101:

HL Bottom Bracket
Road-750 Black, 44
Road-750 Black, 48
Road-750 Black, 52

//Final page contains only four items as expected

To get a paged result and work with this paged result, another extension method can be used:

  public static Dictionary<int,List<ExpandoObject>> MassivePagedRetrieval(this DynamicModel massiveObject, int pageSize = 20, int startPageIndex = 1,
           int endPageIndex = -1)
        {

            var pages = new Dictionary<int, dynamic>();
            var pagedResult = new Dictionary<int, List<ExpandoObject>>(); 
            var firstPage = massiveObject.Paged(currentPage: startPageIndex, pageSize: pageSize);
            pages.Add(startPageIndex, firstPage);
            int endIndex = endPageIndex == -1 ? firstPage.TotalPages : Math.Min(firstPage.TotalPages, endPageIndex);

            for (int currentPageIndex = startPageIndex + 1; currentPageIndex <= endIndex; currentPageIndex++)
            {
                var currentPage = massiveObject.Paged(currentPage: currentPageIndex, pageSize: pageSize);
                pages.Add(currentPageIndex, currentPage);
            }

            foreach (var keyValuePair in pages)
            {
                List<ExpandoObject> items = new List<ExpandoObject>(); 
                foreach (var item in keyValuePair.Value.Items)
                {
                    items.Add(item); 
                }
                pagedResult[keyValuePair.Key] = items;
            }

            return pagedResult;
        }

To use this extension method, use:

     Dictionary<int, List<ExpandoObject>> pagedResult = table.MassivePagedRetrieval(pageSize: 10); 

The key is the page index again and a list of ExpandoObject objects are returned for each page (or key) in the dictionary. ExpandoObject is a dynamic object. To watch the resulting data in Visual Studio, use the debugger and choose Dynamic View when inspecting the result through debugging brekpoints in your code. This article has focused on paged data, as Massive often can be used to performed paged data retrieval in for example ASP.NET MVC-based solutions, but also other solutions can use this data access wrapper. The requirement is .NET 4.0 as System.Dynamics is the library which is used. Massive supports most features as more complex Object Relational Wrappers, such as Entity Framework. Its better speed and ease of use should be tempting. Rob Conery has got additonal information here: Massive Github page There is a trade off with Massive too, its dynamic nature is also its Achilles heel. If you rename fields in the database, you most likely must also update your code and since it is dynamic, chances are that errors can occur and be detected in runtime, out in production. This is easier to avoid using strongly typing such that Entity Framework also. Sadly, Entity Framework and many other ORMs are also slow. The following code shows how it is possible to retrieve a result from two tables. As you can see, one has to pass in SQL to output data.

var productsWithCategories = 
table.Query("SELECT p.Name, pc.Name As CategoryName FROM Production.Product p INNER JOIN Production.ProductCategory pc ON p.ProductSubCategoryId = pc.ProductCategoryId");

foreach (var ppc in productsWithCategories)
{
 Console.WriteLine("Product Name: {0}, Product category name: {1}", ppc.Name, ppc.CategoryName);
}

//RESULTING OUTPUT: 

Product Name: Road-150 Red, 62, Product category name: Components
Product Name: Road-150 Red, 44, Product category name: Components
Product Name: Road-150 Red, 48, Product category name: Components
Product Name: Road-150 Red, 52, Product category name: Components

..

This results in a quick way to access the database, but also loosing Intellisense (auto completion), strongly static typing and compile type checking. When returning results from the database in a service, it is possible to return the data as ExpandoObjects as shown in the extension method MassivePagedRetrieval shown above. If one uses ASP.NET MVC, the Model can bind to this data. This results in fewer application layers and less coding, but at the same time a risk for those pesky runtime errors out in production. Massive is very quick and for web developers, this looks very promising. Using ElasticObject or similar techniques, the ExpandoObjects can be converted to either XML or better - JSON - and processed using jQuery and Javascript. If Massive catches on or some other, ligtweight ORM is preferred by developers remains to be seen. This is a moving field. What is sure, the days of heavy weight ORMs like Entity Framework will not be bright if not the performance catches up with these lighter ORM frameworks. At the same time, it is hard to say that it actually will result in shorter time to market (TTM) for developers, as they loose Intellisens and other good features such as Entity Framework supports. Test out Massive and see if it matches your needs. Use the extension methods above to get started with paged result retrieval, if they look interesting. Massive also supports of course inserts, updates and deletes. In addition, validations and callbacks can be put in the class that inherit from DynamicModel. Massive also has specialized functions such as the standard aggregation methods and convenience method such as Find that is defined in the DynamicModel. Lastly, Massive is heavily based on System.Dynamics assembly in .NET 4.0 and newer framework versions. DynamicModel inherits itself from System.Data.DynamicObject. Also bulk updates are supported in Massive.

2 comments:

  1. You should be able to use Massive against Sql Server, Sqlite, PostgreSQL and Orale. I guess MSAccess DB perhaps also is supported. I have often noted that Rob Conery is the creator of Massive. Yes, he is the initiator of the project, but there are also other contributors. Check out the Github page.

    ReplyDelete
  2. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you must watch this video
    right away...

    (VIDEO) Have your ex CRAWLING back to you...?

    ReplyDelete