Dapper Query Method – Execute SQL and Stored Procedure

Dapper Query method executes a SQL query or Stored Procedure and returns the result as a dynamic object. This dynamic object can then be mapped to C# strongly typed objects. The Query method takes a required SQL parameter and 3 optional parameters.

Learn Dapper from the complete series”

1. Dapper Execute Method – Execute SQL and Stored Procedure
2. Dapper Query Method – Execute SQL and Stored Procedure
3. Dapper One-to-One Relationship
4. Dapper One-to-Many Relationship
5. How to Seed Database with Dapper
6. How to perform CRUD Operations in Dapper

These parameters are given below.

The syntax of Read method is:

string query = "SELECT * FROM Inventory";

using (var connection = new SqlConnection(connectionString))
{
    var result = await connection.Query(query);
}

Here variable result will contain the returned result in the form of a dynamic object.

The Inventory table is:

CREATE TABLE [dbo].[Inventory] (
    [Id]       INT          IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR (50) NOT NULL,
    [Price]    MONEY        NOT NULL,
    [Quantity] INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Dapper Query Raw SQL

Raw SQL Query can be executed with the Query method. This is shown by the below code where a SQL Select command is used.

// connection string
string myCS = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DapperStart;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

string query = "SELECT * FROM Inventory";

using (var connection = new SqlConnection(myCS))
{
    var result = connection.Query(query);
}

The Query method return the result which is in the form of a C# dynamic object. We can convert it to strongly typed “Inventory” class object as shown by the below code.

string query = "SELECT * FROM Inventory";

using (var connection = new SqlConnection(myCS))
{
    var result = connection.Query<Inventory>(query).ToList();
}

The Inventory.cs class is:

public class Inventory
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
    public DateTime AddedOn { get; set; }
}

Query method with Parameters

We can aslo pass parameters to the Dapper Query method. A classic example is in the case of SQL IN command. Suppose we want to fetch records with Id’s 1, 2 & 3 from a table then we can execute an SQL IN Command and provide it with 1,2,3 in a parametes value.

The below code executes an SQL IN Command with Query method:

string query = "SELECT * FROM Inventory where id in @Ids";
var ids = new[] { 1, 2, 3 };

using (var connection = new SqlConnection(myCS))
{
    var result = connection.Query<Inventory>(query, new { Ids = ids }).ToList();
}

An anonymous ids variable contains values 1,2,3 and it is passed to Query method’s 2nd parameter as .Query(query, new { Ids = ids }).

The result which is the 3 records are mapped to a strongly typed list using ToList() method.

Dapper Query with Stored Procedure

We can also execute stored procedure with Query method to get the result returned by it. We have a stored procedure SelectInventory. It takes @Price parameter of type Money and returns all the Inventory records whose price is equal to this @Price parameters. The SP is given below:

CREATE PROCEDURE [dbo].[SelectInventory]
    @Price Money
AS
    Select * from Inventory Where Price=@Price

We can execute this stored procedure by Dapper Query method and can also provide @Price parameter value as shown by the below code.

using (var connection = new SqlConnection(myCS))
{
    var result = connection.Query<Inventory>("SelectInventory", new { Price = 25 }, commandType: CommandType.StoredProcedure).ToList();
} 

Notice we passed the stored procedure name “SelectInventory”, @Price parameter value as new { Price = 25 } and commandType: CommandType.StoredProcedure to the Query method.

So, we will get the Inventory recods whose price is equal to 25. The below image shows 4 recods returned.

Asynchronous Dapper Query Method

The QueryAsync is an Asynchronous version of Query method. An example of this method is given below:

public async Task<IActionResult> QueryParameterA()
{
    //connection string
    string myCS = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DapperStart;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    
    string query = "SELECT * FROM Inventory WHERE Id = @Id";

    using (var connection = new SqlConnection(myCS))
    {
        var result = await connection.QueryAsync(query, new { Id = 1 });
    }

    return View();
}

QueryMultiple method

The QueryMultiple executes multiple queries with the same command. The results are then mapped to strongly typed class objects.

The below code executes 2 SQL Select Queries with QueryMultiple at the same time.

string query = "SELECT * FROM Inventory where Id=@Id; Select * From Employee where Name=@Name";

using (var connection = new SqlConnection(myCS))
{
    using (var multi = connection.QueryMultiple(query, new { Id = 1, Name = "Yogi" }))
    {
        var inventory = multi.Read<Inventory>().First();
        var employee = multi.Read<Employee>().ToList();
    }
}

There are 2 select queries containing 2 parameters @Id and @Name.

SELECT * FROM Inventory where Id=@Id
Select * From Employee where Name=@Name

In the QueryMultiple method we passed the SQL query and the values of 2 parameters in anonymous types.

connection.QueryMultiple(query, new { Id = 1, Name = "Yogi" })

The result is then mapped to 2 strongly typed classes objects – Inventory and Employee.

var inventory = multi.Read<Inventory>().First();
var employee = multi.Read<Employee>().ToList();

The “Employee.cs” and “Inventory.cs” classes are given below.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int Salary { get; set; }
    public List<Address> Address { get; set; }
}

public class Inventory
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
    public DateTime AddedOn { get; set; }
}

QueryFirstOrDefault method

The QueryFirstOrDefault method returns the “first row of the result set” which the sql query returns on execution. If the result set is empty then the default value is returned.

Example of QueryFirstOrDefault is given below.

string query = "SELECT * FROM Inventory;";

using (var connection = new SqlConnection(myCS))
{
    var result = connection.QueryFirstOrDefault<Inventory>(query);
}

It should be noted that we can get the same result by using FirstOrDefault LINQ method over the result returned by the Query method. See below code:

var result = connection.Query<Inventory>(query).FirstOrDefault();

Dapper Buffered and Unbuffered Query

There are 2 ways in which Dapper execute queries – Buffered & Un-Buffered way.

Buffered – in this dapper executes the sql query and then buffer the entire result-set in memory before returning. This is the default way.

Un-Buffered – The un-buffered way is totally different where dapper loads objects on demand.

When the result set is small use buffered way while use un-buffered when result set is very large.

var result = connection.Query<Inventory>(query, buffered: false).ToList();

Dapper Transaction

In mission critical jobs we want all the works to either finish successfully or fail completely. For example in a bank we always want that if some money is transferred then both the following things to happen successfully or fail completely:

1. Amount is deducted from one account.
2. Same amount is added to another account.

We never want the amount is deducted but not added. In such cases we apply Dapper Transaction.

For creating transaction in Dapper we use TransactionScope class. It marks a block of code as participating in a transaction.

In the below code we are executing a stored procedure “CriticalWorkSP”. It does lot of works like adding records on one table, updating and deleting record in other table, etc. We want it to either complete successfully or fail completely.

The full code runs in a TransactionScope and at the end we are doing – transaction.Complete.

If Stored Procedure ends up giving an error the transaction.Complete will not run and instead the transaction is rolled back.

//myCS is connection string

using (var transaction = new TransactionScope())
{
        var sql = "CriticalWorkSP";

        using (var connection = new SqlConnection(myCS))
        {
                connection.Open();

                var affectedRows = connection.Execute(sql, commandType: CommandType.StoredProcedure);
        }

        transaction.Complete();
}

Download source codes:

Download

Here we learned Dapper Query method and it’s related QueryMultiple and QueryFirstOrDefault methods. We also saw Query method to execute Stored Procedures and took lots of examples that will help you to understand how to use which method under different conditions.