12. Dapper Practise

Dapper Practice in C#

In this note, we will delve into Dapper, a simple object mapper for .NET, and understand its application using C#. Dapper is a high performance micro-ORM, supporting SQL Server, MySQL, SQLite, SqlCE, Firebird, etc., out of the box.

What is Dapper?

Dapper is a lightweight, fast, and very efficient open-source ORM (Object-Relational Mapping) tool built by the Stack Overflow team. It works as an extension to the IDbConnection interface and provides a set of easy-to-use methods for querying and executing commands.

Key Points:

• Dapper extends the IDbConnection interface.

• It's lightweight and has less overhead compared to other ORMs.

• Dapper allows you to map complex and flexible objects directly from SQL queries.

• It provides support for a wide range of databases.

Setting up Dapper

To use Dapper, you need to install the Dapper library into your project. This can be done from the NuGet Package Manager in Visual Studio or by running the following command in the Package Manager Console:

Install-Package Dapper

Using Dapper to Execute Queries

In Dapper, you can execute a query that returns data using the Query method. Here's an example:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var employees = connection.Query<Employee>("SELECT * FROM Employees");
}

In this example, Dapper opens a connection to the database, executes the SQL query, and maps the result to a list of Employee objects.

Using Dapper to Execute Commands

Dapper can also be used to execute commands such as INSERT, UPDATE, and DELETE. Here's an example:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var affectedRows = connection.Execute("UPDATE Employees SET Name = @Name WHERE Id = @Id", new {Name = "John", Id = 1});
}

In this example, Dapper executes the UPDATE command and returns the number of affected rows.

Parameterized Queries

Dapper supports parameterized queries, which can help prevent SQL injection attacks. Here's an example:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var employee = connection.QueryFirstOrDefault<Employee>("SELECT * FROM Employees WHERE Id = @Id", new {Id = 1});
}

In this example, Dapper executes a parameterized SQL query and maps the result to an Employee object.

Stored Procedures

Dapper also supports calling stored procedures. Here's an example:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var employee = connection.QueryFirstOrDefault<Employee>("spGetEmployeeById", new {Id = 1}, commandType: CommandType.StoredProcedure);
}

In this example, Dapper calls a stored procedure named spGetEmployeeById and maps the result to an Employee object.

Conclusion

Dapper is a powerful micro-ORM that provides a simple, flexible, and efficient way to interact with your database using .NET. Its support for parameterized queries and stored procedures makes it a versatile tool that can meet most database interaction needs.

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version