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

Table of Contents

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.

Install-Package Dapper

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 :

static void Main(string[] args)
{
    var connectionString = "Data Source=(local);Initial Catalog=DapperExample;Integrated Security=SSPI";

    using (SqlConnection connection  = new SqlConnection(connectionString))
    {
        //Do some magic here
    }
}

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.

using (SqlConnection connection  = new SqlConnection(connectionString))
{
    var eventName = connection.QueryFirst<string>("SELECT TOP 1 EventName FROM Event WHERE Id = 1");
    Console.WriteLine(eventName);
    Console.ReadLine();
}

… 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.

public static T QueryFirst<T>(this IDbConnection cnn, CommandDefinition command);

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 :

class Event
{
    public int Id { get; set; }
    public int EventLocationId { get; set; }
    public string EventName { get; set; }
    public DateTime EventDate { get; set; }
}

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 :

using (SqlConnection connection  = new SqlConnection(connectionString))
{
    var myEvent = connection.QueryFirst<Event>("SELECT * FROM Event WHERE Id = 1");
    Console.WriteLine(myEvent.Id + " : " + myEvent.EventName);
    Console.ReadLine();
}

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.

class EventDto
{
    public int Id { get; set; }
    public string EventName { get; set; }
}

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

using (SqlConnection connection  = new SqlConnection(connectionString))
{
    var myEvent = connection.QueryFirst<EventDto>("SELECT Id, EventName FROM Event WHERE Id = 1");
    Console.WriteLine(myEvent.Id + " : " +myEvent.EventName);
    Console.ReadLine();
}

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 :

using (SqlConnection connection  = new SqlConnection(connectionString))
{
    int eventId = 1;
    var myEvent = connection.QueryFirst<EventDto>("SELECT Id, EventName FROM Event WHERE Id = @Id", new { Id =  eventId});
    Console.WriteLine(myEvent.Id + " : " +myEvent.EventName);
    Console.ReadLine();
}

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?

var myEvent = connection.QueryFirst<EventDto>($"SELECT Id, EventName FROM Event WHERE Id = {eventId}");

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 :

exec sp_executesql N'SELECT Id, EventName FROM Event WHERE Id = @Id',N'@Id int',@Id=1

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

SELECT Id, EventName FROM Event WHERE Id = 1

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 :

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var allEvents = connection.Query<EventDto>("SELECT Id, EventName FROM Event");
    foreach (var myEvent in allEvents)
    {
        Console.WriteLine(myEvent.Id + " : " + myEvent.EventName);
    }
    Console.ReadLine();
}

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!