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