Dapper and JSON:  performance and code simplicity.   You just may like this Micro-ORM.

If you are writing JavaScript apps that are data intensive you might want to take a look at Dapper.   Dapper is a super lightweight (single-file) object-mapper for .NET that’s been getting a lot of respect.  Per Wikipedia:  “Dapper was started by Sam Saffron because of the N+1 and other performance issues with Entity framework”

Wikipedia: http://en.wikipedia.org/wiki/Dapper_ORM  

GItHub:   https://github.com/SamSaffron/dapper-dot-net. 

stackoverflow: http://stackoverflow.com/tags/dapper/info

You can even now download Dapper via NuGet from VS2012.

I’ll tell you why my team is so excited about Dapper.  Many people have heard about Dapper’s performance.  Sam Saffron designed Dapper to be FAST.  It is fast.   However, that’s not the biggest win for us.  I’ll explain.

First, a quick note about the team and the project.  We’re a team of 5 working on a multi-year rewrite of a legacy healthcare app.  The app is data-intensive with a large schema, extremely detailed UI, and complex business rules.  We’re using ExtJS4.2, MVC4, MSSQL2012.   (Sidenote: we were using KendoUI, Knockout, MVC3 and have recently gotten religious about Sencha, the makers of ExtJS, Sencha Touch, etc.  But that’s another story, this blog is not about Sencha).

a)      Dapper has made communications with MSSQL much easier for us by making the always-fun ‘parameter boundary’ a bit easier to cross.   Take a look at these few lines of C# code in a data-layer:

Here, our server-side controller for processing an Product Master ‘Save’ receives one input ‘object’ parameter (passed to it as JSON by ExtJS code).  The stored-proc checks for potential save conflicts, and then returns another ‘object’ parameter from a single-row select executed by the stored-proc, in place of a ‘RETURN’ or ‘OUTPUT’ variable (in this particular optimistic-locking example, the biz-rule calls for returning the ‘current’ state of the row to the user if an intervening user manages to Update the same row before the ‘saving’ user commits their data.

  public JsonResult saveProduct(Product model)

{…

var  returnObj  = (IDictionary<string,object>)conn.Query("saveProduct", model, commandType: CommandType.StoredProcedure).First();

conn.Close();

return Json(returnObj  , JsonRequestBehavior.AllowGet);

…}

There’s a lot going on here.  Dapper is able to make complex AJAX-based Javascript<>SqlServer communications much easier for applications with .NET backend layers.

1)      The first parameter passed to conn.Query() is the stored procedure name, nothing special here.  However:

2)      Note that the second parameter passed to conn.Query is ‘model’.    Using this strongly-typed model (here a ‘Product’ model passed into the saveProduct method), Dapper auto-generates all parameters for the SQL stored-procedure call.  In this case, we’re saved 22 lines of ‘DynamicParameter’ declaration).

3)      Dapper also provides for ‘Multi’ (easy return of multiple result sets) and ‘Execute’ methods, but here, we use ‘Query’…  because our T-SQL Stored Procedure has been tasked with returning a result-set of ‘before’ values when it detects a multiuser collision.    When our SP detects a collision (using a table-resident DateTime column as a version stamp), it calls a JSON-generating UDF* to create a javascript object result-set that Dapper can easily cast back into a key-hash for easy server-side processing of a return-‘model’.

Here’s a fragment of the stored procedure that checks for collisions, and returns the ‘before’ state for processing client-side:

       IF @dtLastUpdated <> @tblDtLastUpdated  AND @tblDtLastUpdated IS NOT NULL

       BEGIN

              SET @lcSql='SELECT P.*,U.name AS userNameLastUpdated FROM Products P

            LEFT OUTER JOIN Users U on P.userlastUpdated=U.id where P.id =' +cast(@id as varchar(10))

              EXEC GetJSON @lcSql

              RETURN

       END

               

You can see that we did not have to declare a bunch of INPUT parameters to pass data to T-SQL from the C# layer.  Nor did we have declare a bunch of OUTPUT parameters to return a complex object back from SQL . 

Dapper is making things sooooooo much easier..

Namaste,

bob davis

*check back later for more info about JSON-generating UDFs.   I’m using one now created by an enterprising Bangalore tech.  I will be weighing in more on it and others as we continue to check it’s performance and accuracy , but so far, with the singleton-row result-set we need it for, performance is more than adequate.