Поиск  
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

Автор:
Waqas Ahmed
Источник:
Просмотров:
5729

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.

Похожее
22 апреля 2013 г.
Автор: Max Koverdyaev
В некоторых проектах достаточно часто возникает необходимость в хранении данных, объем которых уже нельзя назвать маленьким, но в тоже время использовать какую-либо СУБД слишком накладно из-за сложности развертывания приложения. И тут на помощь приходит такая прекрасная вещь как SQLite –...
May 14, 2023
Author: Edwin Klesman
In this article, I’ll show you what the basic steps are for converting a SQL query into LINQ. You’ll learn the basic steps needed while we convert an example query.In this article, it's assumed that you have a basic understanding...
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...
Jan 15
Author: MESCIUS inc.
HyperText markup language, commonly referred to as HTML, has been the foundation of creating and navigating web pages from the very beginning. Its significance further increases provided how the world is moving towards digitization. Hence, working with this format just...
Написать сообщение
Почта
Имя
*Сообщение


© 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