Dapper: the microORM that will change your life

ORM or Stored Procedures? Code First or Database First? Ad-Hoc Queries? Impedance Mismatch? If you're a developer or you are a DBA working with developers you have heard all this terms at least once in your life…and usually in the middle of a strong discussion, debating about one or the other. Well, thanks to StackOverflow's Dapper, all these fights are finished. Dapper is a blazing fast microORM that allows developers to map SQL queries to classes automatically, leaving (and encouraging) the usage of stored procedures, parameterized statements and all the good stuff that SQL Server offers (JSON and TVP are supported too!) In this session I'll show how to use Dapper in your projects from the very basis to some more complex usages that will help you to create *really fast* applications without the burden of huge and complex ORMs. The days of Impedance Mismatch are finally over!

1. The micro-ORM that will change your life Davide Mauri, Director of Software Engineering, Sensoria Dapper .NET

2. 2

3. Davide Mauri Director of Software Engineering, Sensoria A DEVELOPER 20 years in development, started with C++, then VB & Delphi, then C# and now also Python. Active on GitHub A DATABASE GUY 15 years spent on High-Performance Database, Data Warehouse, Business Intelligence and Business Analytics projects. Data Platform MVP since 2007 AGILE & AUTOMATION FAN Fan of Agile Methodology and Automation, I try to apply them everywhere he can, to make sure that "people think, machines do". IoT is where I live and work now, making sure the above two worlds work well together / davidemauri @mauridb http://www.sensoriafitness.com/

4. Agenda The story so far: DEV, DBA, ORM, MicroORM Dapper.NET Basic Usage Advanced Stuff Extensions Conclusions

5. Let’s start with a definition fric·tion The resistance that data finds when it moves from the application to the database or vice-versa The resistance that one surface or object encounters when moving over another. Friction is bad It will slow your performance down It will make your DBA scream It will make your DEV scream It will increase solution costs © http://becauseracecar.net

6. Impedance Mismatch Friction exists because of the “Impedance Mismatch” Object Oriented Models and Relational Models are different Yet they have to co-exists since they are both really good at their job Friction also gets generated between teams (DBA vs DEV) Code First or Database First? Stored Procedure or SQL generated on the fly?

7. What’s your current status? Beginner Advanced I really encourage you to share this presentation with your developers.

8. The story so far… DEV usually don’t like to write (and in general deal with) databases, and SQL code especially It looks old…wait: it IS old! It’s not OO It’s complex/strange/illogic It’s really not part of my job So let’s have something to write the SQL code for us so we can just abstract from it It will even make application work with any DB! That’s a strong selling point to give to my boss!

9. The story so far… ORM (Object-Relational-Mapping) to the rescue DEVs don’t write the code DBAs still can’t put their hands in code but now is the ORM to blame Everyone is happy from a relationship perspective

10. The story so far… Performance still crappy. Really crappy. Let’s move to a NoSQL db then. It was cool in that presentation right? No more normalization stuff…. Ehy is the word “Normalization” that one I’m seeing in MongoDB docs? Nice: no joins! Well uhm …but now we have *a lot* of duplication in our data. But the fact that I can just put and get my class is soooooo cool and comfortable. The DBA will figure out later how the extract data from it and if the data has some logical meaning or not. Even in which property the data is stored. Damn! Someone created the InvoiceValue property but we already had the InvoceAmout to be used. Oh well, let’s add an if, no, better!, let’s create a solution that via IoC that allows us to configure where I have to look for Invoice amount. Great let’s do that for all the other fields too!

11. The story so far… ORM like EF or NHibernate wrote the SQL code on the fly No why to change it if at some point you discover it could have been written so much better It would be nice if one could just behave like if the SQL code doesn’t exists: the DBA will write the code for me and I just map it to my OO objects Which is boring anyway. There is a tool that, given a query in can automatically map the result into an OO object of mine?

12. The story so far… Devs recently come to realize that – an average - they need to use a DB to excel in their job DB is quite cool again. Lock-Free tables, columnar storage, cool uh? Still writing interacting with the db is boring (which means error prone) Create the connection Execute the query Map the query to OO objects Close the connection Oh wait, yeah, exception management

13. The story so far… DBA they want to be able to Improve, Fix, Cleanup, Tweak SQL code written by DEVs. Because if the db is slow it’s THEIR problem, even if they DIDN’T wrote that code. But actually they don’t really want to touch DEVs code….is so dirty! And it’s barely comprehensible. And look how he wrote this statement, oh my gosh, this prevents index to be used, geez! Look: my poor server is just out of CPU and I/O! Phone calls. It’s the customer care complaining that they cannot do their job, everything is slow

14. What a MicroORM is? Just do one simple thing, take data coming from a database query an use it to populate pre-existing or dynamic objects Nothing more and nothing less No frills approach: Not identity mapping, no lazy load SQL *MUST* be written manually (no LINQ or other intermediate language like HQL) Tries not to introduce friction when accessing and operating on data One of the most used, proven and well-known is Dapper .NET https://blogs.msdn.microsoft.com/dotnet/2016/11/09/net-core-data-access/

15. Why MicroORM ? You are interested in: GET GREAT PERFORMANCE REDUCE RESOURCE COSTS BE IN CONTROL

16. Dapper .NET First public release on Apr, 2011 Supports .NET “Classic” and .NET Core Supports any RDBMS supported by .NET ADO providers: SQL Server, SQL Lite, PostgreSQL, Oracle, MySQL, Firebird, … Works as an extensions to IDBConnection interface Installation via NuGet or dotnet add package Current version is 1.50.2

17. Dapper .NET High performance Micro-ORM: YOU Create POCO classes YOU Write SQL Map SQL results to POCO classes Automatically done  Very Fast! https://github.com/StackExchange/Dapper

18. Where does performances come from? DynamicMethod Inject MSIL directly in the body of existing code Somehow like the “asm” keyword of C/C++ Allows mapping to POCO properties without Reflection Almost no performance impact No more boring GetInt32() / GetString() / Get…() code 

19. Dapper .NET Full source code available on GitHub https://github.com/StackExchange/dapper-dot-net Created and used by Stack Exchange Battle Tested!  Born here: How I learned to stop worrying and write my own ORM Support available on GitHub and Stack Overflow: http://stackoverflow.com/questions/tagged/dapper

20. The Basics Dapper .NET

21. Dapper .NET Extends the IDBConnection interface Provides three main methods Query Execute ExecuteScalar

22. Dapper .NET The three main methods are implemented in various ways To support Async/Await To support Single/First/FirstOrDefault To support multiple results set To support DataReaders Results can be mapped to (Enumerable of) POCO classes (Enumerable of) dynamic objects

23. Parametric Queries All methods support parametric queries. Parameters are identified using the “@” symbol: Parameters can be Anonymous Objects DynamicParameters Objects List (to support then IN clause) SELECT [Id], [FirstName], [LastName] FROM dbo.[Users] WHERE Id = @Id

24. Stored Procedures Stored procedures can be executed easily Set CommandType to StoredProcedure Do not specify EXEC[UTE] Pass the parameters as shown before Return value and Output Parameters can be obtained via DynamicParameters Result can be mapped to (Enumerable of) POCOs or Dynamic Objects just as an ad-hoc query

25. The Basics Demo

26. Advanced Features Dapper .NET

27. Multiple Execution Use a IEnumerable parameter to execute the statement for each item. Only “Execute” Method supports this feature var paramList = new List<DynamicParameters>(); paramList.Add(p1) paramList.Add(p2) paramList.Add(p3) var affectedRows = conn.Execute(“<SQL>”, paramList) This is *not the same* as a BULK INSERT!

28. Multiple Results Mapping multiple result set to different object is also supported “Read” is just like the “Query” method and supports all overloads and specialized methods (eg: ReadFirst , ReadSingle , AsyncRead …) using (var qm = conn.QueryMultiple(“SELECT…;SELECT…”)) { var users = qm.Read<User>(); var companies = qm.Read<Company>(); }

29. Multiple Mapping It’s also possible to take data from one row and split it in multiple objects the returned object

30. Table-Valued Parameters Any IEnumerable or DataTable can be used. (As expected) Use the extension method . AsTableValuedParameter Your DataTable or IEnumerable

31. Special SQL Server Data Types All ”special” SQL Server Data Types are supported: Geometry Geography HierarchyID Just use them as usual Reference and Use Microsoft.SqlServer.Types Use them with Dapper as any other object

32. JSON JSON in SQL Server is “just” text Dapper deals with it as such, so Serialization and Deserialization must be done manually But Wait! There’s a better solution for this!

33. Customizing Interaction with database There are two way to customize how Dapper will map data to and from database: Mapping: Which property is mapped to column and vice-versa Handling: How property’s value is loaded into to database column and vice-versa

34. Custom Data Mapping It is possible to replace default “same name” convention mappings Define mapping between model and database by creating a CustomPropertyTypeMap Make it active via SqlMapper.SetTypeMap

35. Custom Type Handling Explicitly states how handling data between model and database should happen Opens up a world of possibilities, to handle even the most complex scenarios SqlMapper is the object that behind the scenes does the mapping Can be used explicitly: SqlMapper.AddTypeHandler(<TypeHandler>) Create your own type handler Create a class that derives from SqlMapper.TypeHandler Implement methods Parse and SetValue

36. Transactions Transaction are supported via the usual: BeginTransaction TransactionScope

37. Buffered & Unbuffered results By default the entire result set will be loaded in memory and then returned to you The aim is to stay connected to the database for the smallest amount of time possible Of course this means memory usage In order to have the results usable as soon as they are streamed from the database the “unbuffered” option exists conn.Query("SELECT …;", buffered: false);

38. Advanced Features Demo

39. Extensions Dapper .NET

40. Well-Known Extensions A Growing Ecosystem! Extend Dapper features. Two main families: mainly adding CRUD support (by generating SQL on-the-fly) customize mapping db to class A complete list is here: http://dapper-tutorial.net/dapper-contrib-third-party-library

41. Dapper Extensions Demo

42. ORM and micro-ORM Now the question is: Is a MicroORM good for me? If you need performance, know SQL and want/need to be in control of everything: MicroORM is the way to go And, btw, for me , a (Backend) Developer *must* know SQL! If you prefer more support with compile-time, object tracking, intellisense, etc. and don’t want/need to write your own SQL: ORM is ok A mix of the two is also possible if needed.

43. Conclusions & Alternatives Other MicroORM you may want to take a look at ORMLite PetaPoco Massive Simple.Data

44. Learn more from Speaker Name [email protected] @mauridb