What is DAPPER?
Dapper is a simple and lightweight Object-Relational Mapping (ORM) library for .NET. It provides a way to interact with a database using SQL queries while mapping the results of those queries to .NET objects. Dapper is designed to be fast, efficient, and easy to use, making it a popular choice for developers who want more control over their SQL queries and database interactions compared to traditional ORMs.
Here's how Dapper works:
Query Mapping:
Dapper allows you to write raw SQL queries and map the results to .NET objects. You can use SQL queries to retrieve data from a database and instruct Dapper on how to map the returned columns to the properties of your .NET classes. This provides a high degree of control over the SQL code executed against the database.
Parameterized Queries:
Dapper supports parameterized queries, which help protect your application against SQL injection. You can pass parameters to your SQL queries and Dapper will safely handle their values.
Object Materialization:
When you execute a query, Dapper takes care of materializing the result set into .NET objects. It uses reflection to map the columns from the query result to the properties of your objects.
Connection Management:
Dapper efficiently manages database connections. It automatically opens and closes connections as needed, and it uses connection pooling to improve performance.
Support for Multiple Database Providers:
Dapper is not tied to a specific database system. It can be used with various database providers, such as SQL Server, MySQL, PostgreSQL, Oracle, and SQLite. You can change the database provider without needing to rewrite your data access code.
Performance:
Dapper is known for its performance. Since it's a lightweight library, it doesn't add significant overhead to your database operations. Dapper is especially useful for scenarios where you need to execute complex or performance-critical SQL queries.
Unit Testing: Dapper's simplicity and lack of a complex, rigid architecture make it a good choice for unit testing. You can easily write tests for your data access code that uses Dapper.
Here is a Code Example for CRUD Operations
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class DataAccess
{
private readonly string _connectionString;
public DataAccess(string connectionString)
{
_connectionString = connectionString;
}
/// <summary>
/// Inserts a record into the database using a stored procedure and returns an integer value (e.g., identity value).
/// </summary>
/// <typeparam name="T">The type of the model representing the record to be inserted.</typeparam>
/// <param name="record">The record to be inserted.</param>
/// <param name="storedProcedureName">The name of the stored procedure for insertion.</param>
/// <returns>The integer value (e.g., identity value) returned by the stored procedure.</returns>
public int InsertRecord<T>(T record, string storedProcedureName)
{
using (IDbConnection dbConnection = new SqlConnection(_connectionString))
{
var parameters = new DynamicParameters();
parameters.Add("@NewId", dbType: DbType.Int32,
direction: ParameterDirection.Output);
parameters.AddDynamicParams(record);
dbConnection.Execute(storedProcedureName, parameters,
commandType: CommandType.StoredProcedure);
return parameters.Get<int>("@NewId");
}
}
/// <summary>
/// Updates a record in the database using a stored procedure.
/// </summary>
/// <typeparam name="T">The type of the model representing the record to be updated.</typeparam>
/// <param name="record">The record to be updated.</param>
/// <param name="storedProcedureName">The name of the stored procedure for update.</param>
public void UpdateRecord<T>(T record, string storedProcedureName)
{
using (IDbConnection dbConnection = new SqlConnection(_connectionString))
{
dbConnection.Execute(storedProcedureName, record,
commandType: CommandType.StoredProcedure);
}
}
/// <summary>
/// Deletes a record in the database using a stored procedure.
/// </summary>
/// <param name="recordId">The ID of the record to be deleted.</param>
/// <param name="storedProcedureName">The name of the stored procedure for deletion.</param>
public void DeleteRecord(int recordId, string storedProcedureName)
{
using (IDbConnection dbConnection = new SqlConnection(_connectionString))
{
dbConnection.Execute(storedProcedureName, new { RecordId = recordId },
commandType: CommandType.StoredProcedure);
}
}
/// <summary>
/// Fetches records from the database using a stored procedure.
/// </summary>
/// <typeparam name="T">The type of the model representing the records to be fetched.</typeparam>
/// <param name="storedProcedureName">The name of the stored procedure for fetching records.</param>
/// <param name="parameters">Optional parameters to pass to the stored procedure.</param>
/// <returns>An enumerable collection of fetched records.</returns>
public IEnumerable<T> FetchRecords<T>(string storedProcedureName,
object parameters = null)
{
using (IDbConnection dbConnection = new SqlConnection(_connectionString))
{
return dbConnection.Query<T>(storedProcedureName, parameters,
commandType: CommandType.StoredProcedure);
}
}
}
In summary, Dapper is a micro-ORM that provides a way to interact with databases using raw SQL queries while offering a lightweight and efficient mapping of the query results to .NET objects. It gives developers greater control over their data access code compared to full-featured ORMs, making it a good choice for scenarios where performance and control are essential.
