Search  
Always will be ready notify the world about expectations as easy as possible: job change page
Articles / Dapper advanced features in .NET Core
May 8, 2023

Dapper advanced features in .NET Core

Print version
Author:
Waqas Ahmed
Source:
source
Views:
10871
#dotnet #csharp #database #orm #dapper #guide

Dapper ORM

Dapper is a lightweight ORM (Object-Relational Mapping) framework for .NET Core and is commonly used to query databases in .NET Core applications. Here are some of the advanced features of Dapper in .NET Core:

  1. Multi-Mapping: Dapper allows you to map multiple database tables to a single class. This is useful when you need to retrieve data from multiple tables and map them to a single object.
  2. Dynamic Parameters: Dapper allows you to pass parameters to SQL queries in a dynamic way. This means that you can pass a dictionary of parameters to your query, which makes it easier to write dynamic queries.
  3. Query Multiple: Dapper provides a method called QueryMultiple that allows you to execute multiple SQL queries in a single database round trip. This can be useful when you need to retrieve data from multiple tables and join them together.
  4. Bulk Insert: Dapper provides a method called BulkInsert that allows you to insert multiple records into a database table in a single database round trip. This can be useful when you need to insert a large amount of data into a database table.
  5. Stored Procedures: Dapper provides support for stored procedures. You can call a stored procedure using Dapper and map the results to an object.
  6. Transactions: Dapper provides support for transactions. You can use Dapper within a transaction scope to ensure that all database operations are either committed or rolled back as a single unit.
  7. Performance: Dapper is designed to be fast and efficient. It uses a lightweight mapper that translates between the SQL result set and the .NET objects. This means that Dapper can execute SQL queries and map the results to objects faster than other ORMs.
  8. Custom Mapping: Dapper allows you to customize the way the SQL result set is mapped to .NET objects. This is useful when you need to map a database column to a different property name or type in your .NET object.
  9. Query Caching: Dapper provides a caching mechanism that can cache the SQL queries and their results. This can improve the performance of your application, especially if you have queries that are executed frequently.
  10. Connection Management: Dapper allows you to manage the connections to the database in a flexible way. You can either create a new connection for each query or reuse an existing connection. This gives you control over how the connections to the database are managed.
  11. Lazy Loading: Dapper provides support for lazy loading. This means that Dapper can retrieve the data for a related object only when it is accessed. This can improve the performance of your application, especially when you have complex object graphs.
  12. Support for Multiple Databases: Dapper supports multiple databases, including SQL Server, MySQL, Oracle, and PostgreSQL. This means that you can use Dapper with different types of databases without having to learn a new ORM for each database.

1. Multi-Mapping

public class Order
{
    public int OrderId { get; set; }
    public string CustomerName { get; set; }
    public List<OrderDetail> OrderDetails { get; set; }
}

public class OrderDetail
{
    public int OrderDetailId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
}

var sql = "SELECT * FROM Orders o JOIN OrderDetails od ON o.OrderId = od.OrderId WHERE o.OrderId = @OrderId";
var result = connection.Query<Order, OrderDetail, Order>(sql, (order, orderDetail) =>
{
    order.OrderDetails.Add(orderDetail);
    return order;
}, new { OrderId = orderId }, splitOn: "OrderDetailId");

2. Dynamic Parameters

var parameters = new DynamicParameters();
parameters.Add("OrderId", orderId);
parameters.Add("FromDate", fromDate, DbType.DateTime);
parameters.Add("ToDate", toDate, DbType.DateTime);
var sql = "SELECT * FROM Orders WHERE OrderId = @OrderId AND OrderDate BETWEEN @FromDate AND @ToDate";
var result = connection.Query<Order>(sql, parameters);

3. Query Multiple

var sql = "SELECT * FROM Customers; SELECT * FROM Orders";
using (var multi = connection.QueryMultiple(sql))
{
    var customers = multi.Read<Customer>().ToList();
    var orders = multi.Read<Order>().ToList();
}

4. Bulk Insert

var orders = new List<Order>();
// Populate orders list with data
var sql = "INSERT INTO Orders (CustomerId, OrderDate, Total) VALUES (@CustomerId, @OrderDate, @Total)";
connection.Execute(sql, orders);

5. Stored Procedures

using (var transaction = connection.BeginTransaction())
{
    try
    {
        // Execute SQL statements
        transaction.Commit();
    }
    catch (Exception ex)
    {
        transaction.Rollback();
    }
}

6. Transactions

using (var transaction = connection.BeginTransaction())
{
    try
    {
        // Execute SQL statements
        transaction.Commit();
    }
    catch (Exception ex)
    {
        transaction.Rollback();
    }
}

7. Performance

var sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
var customer = connection.QueryFirstOrDefault<Customer>(sql, new { CustomerId = customerId });

8. Custom Mapping

public class Customer
{
    public int CustomerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }

    // Custom mapping for 'FullName' property
    public string FullName
    {
        get { return $"{FirstName} {LastName}"; }
    }
}

var sql = "SELECT CustomerId, FirstName, LastName, Email FROM Customers WHERE CustomerId = @CustomerId";
var customer = connection.QueryFirstOrDefault<Customer>(sql, new { CustomerId = customerId });

9. Query Caching

var sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
var customer = connection.QueryFirstOrDefault<Customer>(sql, new { CustomerId = customerId });

// The next time the same query is executed, it will use the cached result
var cachedCustomer = connection.QueryFirstOrDefault<Customer>(sql, new { CustomerId = customerId });

10. Connection Management

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

    // Create a new connection for each query
    var sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
    var customer = connection.QueryFirstOrDefault<Customer>(sql, new { CustomerId = customerId });

    // Reuse the connection for subsequent queries
    var sql2 = "SELECT * FROM Orders WHERE CustomerId = @CustomerId";
    var orders = connection.Query<Order>(sql2, new { CustomerId = customerId });
}

11. Lazy Loading

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int CustomerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public List<Order> Orders { get; set; }
}

var sql = "SELECT * FROM Orders o 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 = orderId }, splitOn: "CustomerId").FirstOrDefault();

// The related customer data will be loaded only when it is accessed
var customerFullName = order.Customer.FullName;

12. Support for Multiple Databases

// Example using SQL Server
var connection = new SqlConnection(connectionString);

// Example using MySQL
var connection = new MySqlConnection(connectionString);

// Example using Oracle
var connection = new OracleConnection(connectionString);

// Example using PostgreSQL
var connection = new NpgsqlConnection(connectionString);

Note that these examples assume that you have already established a connection to a database using Dapper in .NET Core.

Conclusion

In conclusion, Dapper is a lightweight and efficient ORM framework for .NET Core that provides advanced features for querying databases. With Dapper, you can write fast and optimized SQL queries that map the results to .NET objects, and take advantage of features such as custom mapping, query caching, connection management, lazy loading, and support for multiple databases. These features make Dapper a popular choice among .NET Core developers who need a fast and flexible ORM framework that can handle complex database operations. If you’re working on a .NET Core application that interacts with a database, consider using Dapper to simplify your data access layer and improve the performance of your application.

#dotnet #csharp #database #orm #dapper #guide
Similar
Jun 10
Author: Dayanand Thombare
LINQ (Language Integrated Query) has revolutionized the way we interact with data in C#. It offers a consistent, readable, and concise way to manipulate collections, databases, XML, and more. However, the beauty and ease of LINQ can sometimes mask performance...
one week ago
Author: Kenji Elzerman
I have written a few tutorials on Entity Framework already, but I’ve always used SQL Server. The reason is simple: It’s easy to use. Then I got a few questions and requests on how to use SQLite with entity framework...
Oct 27, 2023
Author: Charles Chen
A friend reached out recently regarding the usage of Task Parallel Library (TPL) and ConcurrentBag in some .NET code. I inquired what the code was doing and it turns out that for each entry in some workload, it was performing...
Jul 1
Author: Tepes Alexandru
Say goodbye to the hassle of having to manually set a default filter for every query EF Core provides a useful feature called Global Query Filters. It enables you to apply a filter to all queries sent to the database....
Send message
Type
Email
Your name
*Message
RSS