
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 1 required SQL parameter and 4 optional parameters.
These parameters are given below.
| Parameter | Description |
|---|---|
| sql | SQL statement or stored procedure name to execute. It is required. |
| param | The parameters if needed by the sql statement or stored procedure. It’s optional. |
| transaction | The transaction to use. It’s optional. |
| commandTimeout | Number of seconds before command execution timeout. It’s optional. |
| commandType | Specify whether stored procedure or sql will be executed. Default is a sql query. It’s optional. |
The syntax of Query method is:
string query = "SELECT * FROM Inventory";
using (var connection = new SqlConnection(connectionString))
{
var result = 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,
[AddedOn] DATETIME NOT NULL DEFAULT (GETDATE()),
PRIMARY KEY CLUSTERED ([Id] ASC)
);Page Contents
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 returns 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; }
}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
The three matching records are mapped to Inventory objects by Query<Inventory>, and ToList() returns them as a List
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=@PriceWe 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.

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();
}
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; }
}
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 can execute queries in 2 ways: buffered and unbuffered.
Buffered : Dapper runs the SQL query and loads the entire result set into memory before returning it. This is the default.
Unbuffered : Dapper does not load the whole result set up front. It reads rows from the database one at a time, as you loop over the results.
Use the buffered way when the result set is small. Use the unbuffered way when the result set is very large and you want to keep memory use low.
string query = "SELECT Id, Name, Price, Quantity FROM Inventory";
using (var connection = new SqlConnection(myCS))
{
// buffered: false returns rows one at a time as you loop over them
foreach (var item in connection.Query<Inventory>(query, buffered: false))
{
// process each Inventory object here
Console.WriteLine($"{item.Id} - {item.Name}");
}
}Do not call ToList() on an unbuffered query. It loads every row into memory, which removes the benefit of unbuffered mode.
The connection must stay open while you loop over the results, because rows are read from the database as you go. Do not return an unbuffered result out of the using block and enumerate it later, as the connection will already be closed and you will get an error.
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:
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();
}The same idea applies to the bank transfer example above. Here two UPDATE statements, one to deduct the amount and one to add it, run inside the same TransactionScope:
using (var transaction = new TransactionScope())
{
using (var connection = new SqlConnection(myCS))
{
connection.Open();
connection.Execute("UPDATE Account SET Balance = Balance - @Amt WHERE Id = @From",
new { Amt = 100m, From = 1 });
connection.Execute("UPDATE Account SET Balance = Balance + @Amt WHERE Id = @To",
new { Amt = 100m, To = 2 });
}
transaction.Complete();
}If either Execute call throws an error, scope.Complete() is never reached. When the TransactionScope is disposed, both updates are rolled back, so money is never deducted without being added. Both statements use the same connection, so this stays a simple local transaction.
Download source codes:
In this tutorial, you learned how to use Dapper’s Query method to run raw SQL and stored procedures, and how to map the results to strongly typed C# objects. You also saw how QueryMultiple and QueryFirstOrDefault handle multiple result sets and single-row lookups. The examples covered parameterized queries, async calls, buffered and unbuffered reads, and transactions, so you can pick the right method for each situation.
The Query method runs a SQL statement or stored procedure and returns the rows as an IEnumerable. Without a type argument, it returns dynamic objects. With Query<T>, each row is mapped to an instance of your C# class.
Query blocks the calling thread until the database responds. QueryAsync returns a Task that you await, which frees the thread while the query runs. In ASP.NET Core applications, prefer QueryAsync.
Pass the stored procedure name as the SQL, supply its parameters as an anonymous object, and set commandType: CommandType.StoredProcedure:
var result = connection.Query<Inventory>(
"SelectInventory",
new { Price = 25 },
commandType: CommandType.StoredProcedure);Use an anonymous object or DynamicParameters and reference the values with @ placeholders in the SQL. Dapper sends them as real SQL parameters, which protects against SQL injection. Never build the SQL by concatenating user input.
Pass an array or list as a single parameter, and Dapper expands it for you:
var result = connection.Query<Inventory>(
"SELECT * FROM Inventory WHERE Id IN @Ids",
new { Ids = new[] { 1, 2, 3 } });SQL Server allows about 2,100 parameters per command, so use a table-valued parameter for very large lists.
Query returns all rows. QueryFirst returns the first row and throws if there are none. QueryFirstOrDefault returns the first row or a default value. QuerySingle throws unless exactly one row is returned. QuerySingleOrDefault returns a default for zero rows but throws for more than one. Use the Single variants when exactly one row is a business rule.
Use QueryMultiple when you need several result sets from one database round trip, for example fetching an order and its line items together. Read the result sets from the returned grid reader in the same order the SELECT statements appear.
A buffered query (the default) reads the entire result set into memory before returning. An unbuffered query (buffered: false) streams rows one at a time as you iterate. Use buffered for small or medium results and unbuffered for very large ones. Don’t call ToList() on an unbuffered query, because that loads everything into memory anyway.
Yes, if the connection is closed when you call a Dapper method, Dapper opens it for the call and closes it afterward. If you need several commands to share one connection or a transaction, open the connection yourself with Open() or OpenAsync().
Either begin an IDbTransaction and pass it through the transaction parameter, or wrap the calls in a TransactionScope and call Complete() on success. If you use await inside a TransactionScope, create it with TransactionScopeAsyncFlowOption.Enabled.
Yes. Alias the columns in your SQL (SELECT product_name AS Name) so they match your property names. You can also enable DefaultTypeMap.MatchNamesWithUnderscores = true to map snake_case columns to PascalCase properties.
Dapper is generally faster for read-heavy queries because it does little more than run SQL and map results, with no change tracking. EF Core offers more features, such as migrations, LINQ queries, and change tracking. Many projects use both, with EF Core for writes and Dapper for performance-critical reads.