Using Micro ORMs like Dapper with .NET to simplify your life if you’re terrible at writing SQL queries
Imagine an ideal scenario where you have an MS SQL server running on your system, and you get a GET request to your .NET end-point to query some movie information based on a UUID.
You have the following project structure workflow
Incoming Request → Controller →Repository → SQL Database
This is what your controller probably looks like at a fetus stage:
The controller further connected to the repository file which talks to the database.
This is what our MovieData entity model looks like:
If you haven’t been introduced to an ORM framework like Dapper, the chances are your code to query the repository is going to look something like this:
^ This entire code can be avoided, as the more logic we have at the repository stage, the more difficult it becomes to test and debug.
There is a much smarter way to pull this off.
Using Dapper and DapperExtensions
If you have kept up with my argument till now, and want to try this out, you’ll have to create another Mapper class that enables us to set a few properties and also allows us to custom map entity objects names with SQL column names.
Here’s a snippet of a simple mapper class which lets us specify the table name and also gives us a lot of control over the selection and mapping of column names in case they are different:
Once you have your mapper ready, your repository should look something like this:
Line 7: Let’s you specify which mapper you would like Dapper to use
Line 8: Your connection string, you should use it via appsettings.json (it’s just good practice)
Line 10 onwards is where dapper comes into play. The predicate you create helps you specify constraints which the dapper ORM needs to impose while querying your database, you then pass this predicate into your GetList<>() function which automatically maps the queried result to a new MovieData object.
This was you have completely eliminated the use of any SQL query which accessing the database, hence reducing any logical errors which might arise. Let Dapper do all the hard work.
Hopefully, the above approach helped :) Thanks for reading!
