Dapper

[Costas]

Is a simple object mapper for .NET and owns the title of King of Micro ORM in terms of speed and is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, which is responsible for mapping between database and programming language.

Dapper extends the IDbConnection by providing useful extension methods to query your database.

home
https://github.com/DapperLib/Dapper
https://nuget.org/packages/Dapper/

example
https://dev.to/byme8/making-dapper-and-json-friends-5afc

byme8.Dapper.Json - Add JSON support for Dapper
https://github.com/byme8/Dapper.Json

Is the era of reflection-heavy C# libraries at an end?
https://blog.marcgravell.com/2021/05/is-era-of-reflection-heavy-c-libraries.html

Create barebones code for Blazor Dapper CRUD for a database table
Dapper - Table to Entity Converter
Dapper Helper
Dapper.Contrib

Model Generators :
MSSQL - Generate POCO classes by dbase
MYSQL - Generate POCO class by table
POCO Generator
PocoClassGenerator

FAQ :
https://github.com/StackExchange/Dapper
https://stackoverflow.com/questions/tagged/dapper

 

[Costas]

How to map a one to many tsql to class (How to map a join to class) with Dapper

public async Task<IEnumerable<SalesOrder>> SalesOrdersOverMinAmountDue(int minimumAmount)
{
    var sql = @"SELECT
                    soh.SalesOrderID, soh.OrderDate, soh.TotalDue,
                    sd.SalesOrderDetailID, sd.ProductID, sd.OrderQty, sd.LineTotal
                FROM SalesLT.SalesOrderHeader soh
                JOIN SalesLT.SalesOrderDetail sd ON soh.SalesOrderID = sd.SalesOrderID
                WHERE soh.TotalDue > @minAmount";

    var orders = (IEnumerable<SalesOrder>) await connection.QueryAsync<SalesOrder, SalesOrderDetail, SalesOrder>(
        sql,
        map: (order, detail) =>
        {
            order.OrderDetails = order.OrderDetails ?? new List<SalesOrderDetail>();
            order.OrderDetails.Add(detail);
            return order;
        },
        param: new { minAmount = minimumAmount },
        splitOn: "SalesOrderItemID"
    );

    return orders;
}

How to many to many with Dapper

public async Task<IEnumerable<Product>> ProductsWithCategories()
{
    using (var connection = new SqlConnection(Config.ConnectionString))
    {
        await connection.OpenAsync();
        var sql = @"
        SELECT
            p.ProductID, p.Name, p.ProductNumber,
            c.CategoryID, c.Name AS CategoryName
        FROM SalesLT.Product p
        JOIN SalesLT.ProductCategoryLink pcl ON p.ProductID = pcl.ProductID
        JOIN SalesLT.Category c ON pcl.CategoryID = c.CategoryID
        ORDER BY p.ProductID";

        var productDict = new Dictionary<int, Product>();

        var products = await connection.QueryAsync<Product, Category, Product>(
            sql,
            (product, category) =>
            {
                if (!productDict.TryGetValue(product.ProductId, out var existingProduct))
                {
                    existingProduct = product;
                    existingProduct.Categories = new List<Category>();
                    productDict.Add(existingProduct.ProductId, existingProduct);
                }

                existingProduct.Categories.Add(category);
                return existingProduct;
            },
            splitOn: "CategoryID"
        );
        return productDict.Values;
    }
}

source - https://github.com/Dometrain/from-zero-to-hero-dapper-in-dotnet/tree/main/Working with Relationships
ref - https://www.learndapper.com/relationships

 

[Costas]

• where IN
• Dynamic Parameters

private async void button1_Click(object sender, EventArgs e)
{
    var d = await GetRecords();
    foreach (var item in d)
        System.Diagnostics.Debug.WriteLine(item.Fullname);
}

private Task<IEnumerable<Customer>> GetRecords()
{
    IDbConnection db = new SqlConnection("your_connection_string");

    string query = @"SELECT * from Customers IN @ids";
    var ids = new int[] { 1, 2, 3 };
    return await db.QueryAsync<Customer>(query, new
    {
        ids = ids
    });
}
//OR
private Task<IEnumerable<Customer>> GetRecords2()
{
    IDbConnection db = new SqlConnection("your_connection_string");

    string query = @"SELECT * from Customers IN (@id1,@id2)";
    return await db.QueryAsync<Customer>(query, new
    {
        id1 = 1,
        id2 = 2
    });
}
//OR
private Task<IEnumerable<Customer>> GetRecords3()
{
    IDbConnection db = new SqlConnection("your_connection_string");

    string query = @"SELECT * from Customers IN (@id1,@id2)";
    var parameters = new DynamicParameters();
    parameters.Add("id1", 1);
    parameters.Add("id2", 2);
    return await db.QueryAsync<Customer>(query, parameters);
}

A Look at Dapper.NET

---

Execute a procedure without timeout

//##before
//using var db = _connectionFactory.CreateConnection();
//await db.ExecuteAsync("proc_name", commandType: CommandType.StoredProcedure);

//##after
using var db = _connectionFactory.CreateConnection();
var commandDefinition = new CommandDefinition(
    "proc_name",
    null,
    commandType: CommandType.StoredProcedure,
    commandTimeout: 0
);
await db.ExecuteAsync(commandDefinition);

 

[Costas]

How C# Strings Silently Kill Your SQL Server Indexes in Dapper

const string sql = "SELECT * FROM Products WHERE ProductCode = @productCode";
var result = await connection.QueryFirstOrDefaultAsync<Product>(sql, new { productCode });

Clean. Simple. And if ProductCode is a varchar column in your database, it’s silently destroying your query performance.

When you pass a C# string through an anonymous object, Dapper maps it to nvarchar(4000).

The Fix - apply varchar parameter

const string sql = "SELECT * FROM Products WHERE ProductCode = @productCode";

var parameters = new DynamicParameters();
parameters.Add("productCode", productCode, DbType.AnsiString, size: 100);

var result = await connection.QueryFirstOrDefaultAsync<Product>(sql, parameters);

//OR

var result = await connection.QueryFirstOrDefaultAsync<Product>(sql,
    new { productCode = new DbString { Value = productCode, IsAnsi = true, Length = 100 } });

src - https://consultwithgriff.com/dapper-nvarchar-implicit-conversion-performance-trap

 

[Costas]

Dapper - where in (...) with more than 2100 parameters

Error : Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

-- require SQL Server 2008 or later - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql

CREATE TYPE dbo.IdList AS TABLE (Id BIGINT NOT NULL);

public async Task<IEnumerable<(long CarId, int AccessoryID)>> GetAccessoryIds(long[] carsIds)
{
    var query = @"
                SELECT bs.ID AS CarId, am.AccessoryId
                FROM tbl1 am WITH(NOLOCK)
                INNER JOIN tbl2 bs WITH(NOLOCK) ON bs.Car_ID = am.id
                WHERE bs.Car_Type = 'TURBO' AND ISNULL(am.AccessoryId,0) > 0
                AND bs.ID IN (SELECT Id FROM @Ids)";
         
    // build DataTable for TVP
    var table = new System.Data.DataTable();
    table.Columns.Add("Id", typeof(long));
    foreach (var id in carsIds)
        table.Rows.Add(id);
 
    using var db = _connectionFactory.CreateConnection();
 
    var dp = new Dapper.DynamicParameters();
    dp.Add("@Ids", table.AsTableValuedParameter("dbo.IdList"));
 
    var result = await db.QueryAsync<(long CarId, int AccessoryID)>(query, dp);
    return result;
}

behind the scene Dapper doing this :

-- Step 1: Declare a variable of type dbo.IdList
DECLARE @Ids dbo.IdList;

-- Step 2: Insert data into the TVP
INSERT INTO @Ids (Id)
VALUES (1), (2), (3); -- Replace with your actual IDs

ref - https://dappertutorial.net/parameter-table-valued-parameter

#AsTableValuedParameter