Dapper, is a very powerful library that makes it very easy to work with ADO.NET.

I have done a series of posts on how to use Dapper.

Given that Dapper is layered on top of ADO.NET, it is important to remember that there are two ways to send commands to your database.

I will use a simple WebAPI to demonstrate this:

The first is as query text, like this:

app.MapGet("/SpiesByText", async (SqlConnection cn) =>
{
    var result = await cn.QueryAsync<Spy>("SELECT * FROM Spies", commandType: CommandType.Text);

    return result;
});

This is a wrapper around the way you would normally do it with direct ADO.NET.

app.MapGet("/SpiesByTextRaw", async (SqlConnection cn, CancellationToken token) =>
{
    var spies = new List<Spy>();
    // Open the connection
    await cn.OpenAsync(token);
    // Create  a command
    await using var cmd = cn.CreateCommand();
    // Set the command text
    cmd.CommandText = "SELECT * FROM Spies";
    // Set the command type
    cmd.CommandType = CommandType.Text;
    // Execute the command and get back a reader, specifying the connection to be closed
    // after the reader is closed
    var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection, token);
    // Scroll forwards through the results
    while (await reader.ReadAsync(token))
    {
        // Add to the collection
        spies.Add(new Spy
        {
            // Read the value from the column specified
            SpyID = reader.GetInt32(reader.GetOrdinal("SpyID")),
            Name = reader.GetString(reader.GetOrdinal("Name")),
            DateOfBirth = reader.GetDateTime(reader.GetOrdinal("DateOfBirth")),
        });
    }

    // Close the reader
    await reader.CloseAsync();

    return spies;
});

The other way to do it is with a stored procedure.

app.MapGet("/SpiesByProcedure", async (SqlConnection cn) =>
{
  var result = await cn.QueryAsync<Spy>("[Spies.GetAll]", commandType: CommandType.StoredProcedure);
  return result;
});

You can see how much code Dapper has saved us.

Of interest is the part where we tell Dapper whether we are passing a stored procedure name, or a query.

The procedure:

var result = await cn.QueryAsync<Spy>("[Spies.GetAll]", commandType: CommandType.StoredProcedure);

The query:

var result = await cn.QueryAsync<Spy>("SELECT * FROM Spies", commandType: CommandType.Text);

Dapper is smart enough to figure out which one to use.

So you do not need to specify the CommandType.

Your code can now look like this:

The procedure:

var result = await cn.QueryAsync<Spy>("[Spies.GetAll]");

The query:

var result = await cn.QueryAsync<Spy>("SELECT * FROM Spies");

It is, however, advisable to so that the intent is clear to whoever is reading the code.

TLDR

You do not need to specify the CommandType when executing queries with Dapper.

The code is in my GitHub.

Happy hacking!