Retrieve a Single Row with Dapper in C#

C# Retrieve a Single Row with Dapper

This method works with a SELECT query combined with a WHERE clause, and you can pass in parameters or a model type as usual.

using Dapper;

public Book GetBook(int bookId)
{
    using (var connection = new SqlConnection(connectionString))
    {
        return connection.QuerySingleOrDefault<Book>("SELECT * FROM Books WHERE BookId = @bookId", 
            param: new { bookId });
    }
}

In this example, the SQL query fetches a single row from the Books table where the BookId matches the provided parameter. Dapper automatically maps the result to a Book object. If no match is found, it will return null because of the QuerySingleOrDefault() method.

QuerySingle() vs. QueryFirst() (and their Default Variants)

Dapper provides several specialized methods when you are expecting just one row from a query.

All of these methods work similarly: you provide a SQL query, parameters, and a model type.

Select a Single Value

If you need to retrieve a single value (like a count or a specific column value), it’s best to use ExecuteScalar().

using Dapper;

public decimal GetTotalPrice()
{
    using (var connection = new SqlConnection(connectionString))
    {
        return connection.ExecuteScalar<decimal>("SELECT SUM(Price) FROM Products");
    }
}

In this case, the SQL query calculates the total price of all products by summing the Price column. ExecuteScalar() returns the scalar value directly (in this case, a decimal).

While QuerySingle() or QueryFirst() could also be used in some scenarios, ExecuteScalar() is designed specifically for this purpose and tends to be faster up to 25% faster than using QuerySingle() in some tests.

C# Dapper insert return value

In C# using Dapper, when performing an insert operation, you can return the inserted value (such as an auto-generated ID) by specifying an output parameter in your SQL query or using the QuerySingle or ExecuteScalar methods.

For example, Using ExecuteScalar to Return the Inserted ID

If you're inserting a record with an auto-generated ID, you can return that ID using ExecuteScalar:

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

    var query = "INSERT INTO Users (Name, Age) OUTPUT INSERTED.Id VALUES (@Name, @Age);";

    var parameters = new { Name = "John Doe", Age = 30 };

    // Insert the record and get the inserted Id
    int insertedId = connection.ExecuteScalar<int>(query, parameters);

    Console.WriteLine("Inserted ID: " + insertedId);
}

In this example:

• OUTPUT INSERTED.Id is used to return the generated ID after the insert.
• ExecuteScalar<int> returns the inserted ID as an int.

For example, Using QuerySingle to Return Inserted Entity

If you prefer to return the entire inserted entity, you can use QuerySingle to execute the insert and map the result to your entity class:

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

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

class Program
{
    static void Main()
    {
        using (var connection = new SqlConnection("your_connection_string"))
        {
            connection.Open();

            var query = "INSERT INTO Users (Name, Age) OUTPUT INSERTED.Id, INSERTED.Name, INSERTED.Age VALUES (@Name, @Age);";

            var parameters = new { Name = "Jane Doe", Age = 25 };

            // Insert and return the full inserted object
            var insertedUser = connection.QuerySingle<User>(query, parameters);

            Console.WriteLine($"Inserted User - ID: {insertedUser.Id}, Name: {insertedUser.Name}, Age: {insertedUser.Age}");
        }
    }
}

In this example, The QuerySingle<User> method returns a User object that represents the inserted record, including the generated Id.