Dapper In .NET Core – Part 2 – Dapper Query Basics

This article is a series on using Dapper in .NET Core. Feel free to jump around, but we highly recommend you start in Part 1 and work your way through!

Part 1 – The What/Why/Who
Part 2 – Dapper Query Basics
Part 3 – Updates/Inserts/Deletes
Part 4 – Dapper Contrib


Installing Dapper

Dapper is a nuget package that does nothing more than “augment” the existing SQLConnection object in .NET Core….. Reading that line back I’m not sure it makes sense but hopefully it will at the end. In simple terms you can install the Dapper nuget package by running the following from your Package Manager Console.

In the following examples I’m just running them from a simple console application, but you can install Dapper into any type of .NET Full Framework/.NET Core application.

Follow Along Data

For much of this tutorial, I’m going to be using some data that I created for a simple “Event” database. There really isn’t much to it, but if you want to follow along at home you can grab the script to create the database/tables/data from this Github Gist here : https://gist.github.com/mindingdata/00c2d608c4a0b4ea22e76e95c1d1417e

It’s not required and for the most part everything we do here will be super simple. But if you want to write the queries in your own console application as we go, that’s cool too!

Creating A Database Connection

As you might have seen earlier, when using Dapper we have to create our own SQL Connection to the database. Dapper doesn’t do this for us. But.. It’s simple enough. For example in a console application that I’ve already installed Dapper from Nuget in, all it takes is :

That’s it! We now have a connection to our database that is “Dapper” ready. You’ll also notice that at this point, the only “Using” statement we would actually be using is using System.Data.SqlClient; . That’s because the SqlConnection object is the regular one from the .NET library, so far we haven’t even used Dapper yet!

Query A Single Row

Let’s add some magic and actually get the above SQL Connection actually doing something. In my example database, I have a table called “Event” that has a list of events in it. I want to know specifically what the name of the event is with the Id of 1.

… That’s it. We can of course write our initial SQL in something like SQL Management Studio, and then copy it across. We have complete control over the query and it’s going to run *exactly* what we told it to.

The actual magic part of this is the “QueryFirst” method on our connection. If we jump into the source code of Dapper we can see this is just an extention method on the SqlConnection object.

That’s part of the beauty of Dapper. It’s using really simple concepts that we already know like SqlConnection, and extending them just a little bit more to be useful.

Query A Table Into An Object

So far we are just returning a single property, a string. That’s not that amazing really. But what if we wanted the full Event object instead. Well… Dapper handles that out of the box too.

First we create a class that maps to our SQL Table :

Then we modify our statement a bit to instead be a SELECT * and instead use the return type of Event instead of string. So it looks like so :

If you’ve ever used raw ADO statements before in .NET, you’ll know the pain of manually mapping each property from the database to our C# class. But Dapper does that all for us. It takes the results from the statement, and checks to see if there is a property with the same name as a column, if there is, great map it! If not, don’t worry, just throw it away for now.

Query Statement Into A DTO

So the above example maps an entire database table to a C# class, but the annoying part of that is maybe we don’t need everything. Infact a very common SQL performance issue is overselecting. In our above example we actually only use the Id and EventName fields, so let’s first create a DTO with only those fields.

Then we can modify our query a bit to only select the columns we need, and make Dapper map it to our DTO :

Too easy!

Using Parameterized Queries

We haven’t really been taking user input at this point. But let’s pretend instead of always getting the Event with the ID of 1, we allowed the user to specify the Id. How can we pass that to our Dapper query while still following best practice (aka parameterizing the query).

Well again, Dapper takes care of this for us :

Pretend that eventId actually came from a GET param or any other user input. All we do is substitute an @VariableName in our SQL statement, then we add a new parameter of an anonymous object where the “Key” is the same as the @VariableName and the value is what you want it to be.

But why couldn’t I just change the line to the following?

Both look like they do substitutions, but the first actually uses proper parameters when executing the SQL statement. If you use a SQL Profiler, you will see the following for the first query :

Notice how the parameter is specified after the query, and yet for the second example where we just use string substitution :

Uh Oh, no parameters here. This opens us up for SQL Injection a plenty. But again, Dapper handles this for us with proper parameterization!

Query Multiple Rows

Up until now, we’ve been querying just a single row at a time. If we want more than one, that’s just as easy :

We just change our “QueryFirst” to a “Query” and away we go! We are instead returned a list of mapped objects which we can cycle through. Pretty easy stuff!

What’s Next

With the basics of querying using Dapper out of the way, obviously reading and writing is up next! You can check that out here!

ENJOY THIS POST?
Join over 3.000 subscribers who are receiving our weekly post digest, a roundup of this weeks blog posts.
We hate spam. Your email address will not be sold or shared with anyone else.

Leave a Reply

Your email address will not be published. Required fields are marked *