10 Dapper Interview Questions and Answers

Dapper is a popular micro-ORM (Object-Relational Mapper) for .NET, known for its simplicity and high performance. It provides a lightweight framework for mapping database queries to .NET objects, making it an excellent choice for developers who need efficient data access without the overhead of a full-fledged ORM like Entity Framework. Dapper’s ease of use and speed make it a valuable tool for handling data operations in a variety of applications.

This article offers a curated selection of interview questions focused on Dapper, designed to help you demonstrate your proficiency with this micro-ORM. By reviewing these questions and their answers, you can better prepare for technical interviews and showcase your ability to effectively utilize Dapper in real-world scenarios.

Dapper Interview Questions and Answers

1. Explain the basic concept of Dapper and its primary use case.

Dapper is a micro ORM for .NET that efficiently maps database queries to .NET objects. It is favored for its performance and simplicity, especially when executing raw SQL queries and mapping results to strongly-typed objects without the overhead of a full-fledged ORM like Entity Framework.

Dapper extends the IDbConnection interface, allowing execution of SQL queries and mapping results to custom objects. It is particularly useful when performance is a priority, and you need precise control over SQL queries.

Example:

using System.Data.SqlClient;
using Dapper;

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class UserRepository
{
    private readonly string _connectionString;

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEnumerable<User> GetAllUsers()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            return connection.Query<User>("SELECT Id, Name FROM Users");
        }
    }
}

In this example, Dapper executes a SQL query to retrieve all users and maps the results to a list of User objects. The Query<T> method simplifies executing the query and mapping the results.

2. How do you handle parameterized queries? Provide an example.

Parameterized queries are essential to prevent SQL injection attacks and ensure safe handling of user inputs. Dapper supports parameterized queries by allowing you to pass an anonymous object or a dictionary of parameters to the query method, ensuring proper escaping of parameters.

Example:

using System.Data.SqlClient;
using Dapper;

public class UserRepository
{
    private readonly string _connectionString;

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public User GetUserById(int userId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            string query = "SELECT * FROM Users WHERE Id = @Id";
            return connection.QuerySingleOrDefault<User>(query, new { Id = userId });
        }
    }
}

In this example, the GetUserById method uses a parameterized query to safely retrieve a user by their ID. The @Id parameter is replaced with the value from the anonymous object { Id = userId }.

3. Explain how to implement transaction management.

Transaction management in Dapper ensures that a series of database operations are executed as a single unit of work, either all succeeding or all failing. This is achieved using transactions, which group multiple operations.

In Dapper, transaction management is implemented using the IDbTransaction interface. You start a transaction, execute operations, and then commit or roll back the transaction based on success or failure.

Example:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            var sql1 = "INSERT INTO Table1 (Column1) VALUES (@Value1)";
            var sql2 = "INSERT INTO Table2 (Column2) VALUES (@Value2)";
            
            connection.Execute(sql1, new { Value1 = "Value1" }, transaction);
            connection.Execute(sql2, new { Value2 = "Value2" }, transaction);
            
            transaction.Commit();
        }
        catch (Exception)
        {
            transaction.Rollback();
            throw;
        }
    }
}

In this example, a transaction is started using BeginTransaction(). Two SQL insert operations are executed within the transaction. If both succeed, the transaction is committed; otherwise, it is rolled back.

4. How do you handle asynchronous operations? Provide an example.

Asynchronous operations in Dapper are handled using the async and await keywords with Dapper’s asynchronous methods, allowing for non-blocking database operations.

Dapper provides asynchronous methods such as QueryAsync and ExecuteAsync for performing database operations without blocking the main thread.

Example:

using System.Data.SqlClient;
using Dapper;
using System.Threading.Tasks;

public async Task<IEnumerable<User>> GetUsersAsync(string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        var users = await connection.QueryAsync<User>("SELECT * FROM Users");
        return users;
    }
}

In this example, the GetUsersAsync method opens a connection asynchronously and uses QueryAsync to retrieve data from the Users table.

5. Describe how to use a custom type handler.

In Dapper, a custom type handler maps a database type to a .NET type that Dapper does not handle by default. This is useful for complex types or custom data structures.

To create a custom type handler, implement the SqlMapper.ITypeHandler interface, defining methods for setting and parsing parameter values. Register the custom type handler with Dapper.

Example:

public class CustomDateTimeHandler : SqlMapper.ITypeHandler
{
    public void SetValue(IDbDataParameter parameter, object value)
    {
        parameter.Value = ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
    }

    public object Parse(Type destinationType, object value)
    {
        return DateTime.Parse((string)value);
    }
}

// Register the custom type handler
SqlMapper.AddTypeHandler(typeof(DateTime), new CustomDateTimeHandler());

In this example, the CustomDateTimeHandler class handles conversion between DateTime objects and their string representations in the database.

6. Write a code snippet to handle dynamic queries where the columns returned are not known at compile time.

To handle dynamic queries in Dapper where columns are not known at compile time, use the Query method with a dynamic type. This allows flexible handling of results without a specific model class.

Example:

using System;
using System.Data.SqlClient;
using Dapper;

class Program
{
    static void Main()
    {
        using (var connection = new SqlConnection("YourConnectionString"))
        {
            connection.Open();
            var query = "SELECT * FROM YourTable WHERE SomeCondition = @Condition";
            var result = connection.Query(query, new { Condition = "SomeValue" });

            foreach (var row in result)
            {
                foreach (var column in row)
                {
                    Console.WriteLine($"{column.Key}: {column.Value}");
                }
            }
        }
    }
}

7. How would you map a query result to a complex object with nested properties?

Dapper’s multi-mapping feature allows mapping query results to complex objects with nested properties, useful for relational databases.

To map a query result to a complex object with nested properties, use the Query method with a custom mapping function.

Example:

public class Order
{
    public int OrderId { get; set; }
    public string OrderNumber { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
}

using (var connection = new SqlConnection(connectionString))
{
    string sql = @"
        SELECT o.OrderId, o.OrderNumber, c.CustomerId, c.Name
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.CustomerId
        WHERE o.OrderId = @OrderId";

    var order = connection.Query<Order, Customer, Order>(
        sql,
        (order, customer) => 
        {
            order.Customer = customer;
            return order;
        },
        new { OrderId = 1 },
        splitOn: "CustomerId"
    ).FirstOrDefault();
}

In this example, the Query method executes a SQL query joining Orders and Customers tables. The custom mapping function maps the result to an Order object with a nested Customer object.

8. How do you map results from multiple tables to a single object?

To map results from multiple tables to a single object, use the Query method with a multi-mapping function. This allows joining multiple tables in your SQL query and mapping results to a single object.

Example:

public class Order
{
    public int OrderId { get; set; }
    public string OrderNumber { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
}

using (var connection = new SqlConnection(connectionString))
{
    string sql = @"
        SELECT o.OrderId, o.OrderNumber, c.CustomerId, c.Name
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.CustomerId
        WHERE o.OrderId = @OrderId";

    var order = connection.Query<Order, Customer, Order>(
        sql,
        (order, customer) => 
        {
            order.Customer = customer;
            return order;
        },
        new { OrderId = 1 },
        splitOn: "CustomerId"
    ).FirstOrDefault();
}

In this example, the Query method executes a SQL query joining Orders and Customers tables. The multi-mapping function specifies how results should be mapped to the Order object, including its Customer property.

9. Describe how to handle multiple result sets in a single query.

To handle multiple result sets in a single query, use the QueryMultiple method. This allows executing a query that returns multiple result sets and reading each sequentially.

Example:

using (var connection = new SqlConnection(connectionString))
{
    var sql = "SELECT * FROM Table1; SELECT * FROM Table2;";
    using (var multi = connection.QueryMultiple(sql))
    {
        var result1 = multi.Read<Table1>().ToList();
        var result2 = multi.Read<Table2>().ToList();
    }
}

In this example, the QueryMultiple method executes a SQL query returning two result sets. The Read method reads each result set into a list of the appropriate type.

10. What are some strategies to prevent SQL injection attacks when using Dapper?

To prevent SQL injection attacks when using Dapper, use parameterized queries. This ensures user input is treated as data rather than executable code. Additionally, using stored procedures can enhance security by encapsulating SQL logic within the database.

Example of a parameterized query using Dapper:

using (var connection = new SqlConnection(connectionString))
{
    string sql = "SELECT * FROM Users WHERE Username = @Username AND Password = @Password";
    var parameters = new { Username = "user1", Password = "password123" };
    var user = connection.QueryFirstOrDefault<User>(sql, parameters);
}

In this example, the @Username and @Password placeholders safely pass user input into the SQL query. Dapper handles parameterization, ensuring input is properly escaped and treated as data.