Search  
Always will be ready notify the world about expectations as easy as possible: job change page
May 8, 2023

Dapper advanced features in .NET Core

Author:
Waqas Ahmed
Source:
Views:
6058

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.

Similar
Apr 11, 2023
Nowadays, everybody so concerned about DDD and how to implement business logic properly that people just forget about existence of other layers. Bada-bing bada-boom, other layers do exist.Shocked, don’t ya? Take your sit, you will be even more shocked when...
May 31, 2023
Author: Yohan Malshika
Understanding uow to use And and Or operators with Expression TreesAs a C# developer, you may have come across scenarios where you need to build complex logical expressions dynamically based on user input or other dynamic factors. In such cases,...
Apr 4
Author: João Simões
Performance comparison between ToList and ToArrayEver since Microsoft introduced Language Integrated Query to the .NET framework (also known as LINQ) developers have been using it extensively to work with collections.From a simple filter, to an aggregation, to a transformation, LINQ...
Mar 25
Author: Dayanand Thombare
Creating background services in .NET Core is a powerful way to perform long-running, background tasks that are independent of user interaction. These tasks can range from data processing, sending batch emails, to file I/O operations — all critical for today’s...
Send message
Email
Your name
*Message


© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban