C# Dapper is a lightwight ORM. In the world of software development, interacting with databases is a common task. Developers often use Object-Relational Mapping (ORM) tools to simplify the process of database operations. Dapper is one such ORM tool. Dapper is a lightweight, open-source library that provides simple and fast database operations. In this blog post, we will explore the benefits of using Dapper and create a sample project using it. If you want to skyrocket your C# career, check out our powerful ASP.NET full-stack web development course that also covers test-driven development. You can also learn more about the FluentAssertions library in this post.
Prerequisites
To follow along with this tutorial, you will need the following:
- An IDE of your choice
- .NET Core 3.1 or later
- SQL Server
Setting the project up
Create a new console project
1 2 |
dotnet new console |
Install the Dapper NuGet package.
1 2 |
dotnet add package Dapper |
Configuring the database connection
Next, we need to add a connection string to our project to connect to the database. Follow the steps below:
- In the “Program.cs” file, add the following using statement at the top:
1 2 3 |
using System.Data.SqlClient; |
- Add the following code to create a
SqlConnection
object:
1 2 |
string connectionString = "..."; var connection = new SqlConnection(connectionString); |
You can replace the entire connection string with yours. By the way, did you know that we offer a unique online course that boosts your C# career? Check it out here!
Creating a table in the database
We will create a simple Employees
table in the database for this tutorial. Follow the steps below:
- Open SQL Server Management Studio and connect to your database server.
- Right-click on the database name and select “New Query”.
- Paste the following SQL code in the query window and click “Execute”.
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Employees ( Id INT PRIMARY KEY IDENTITY, Name VARCHAR(50), Age INT, Salary DECIMAL(18,2) ); |
Using Dapper to perform database operations
Now that we have our project set up and our database configured, let’s use Dapper to perform some database operations. We will create a new employee, retrieve a list of all employees, and update an employee’s information using Dapper.
Step 1: Creating The Entity
Create a new Employee class with the following code:
1 2 3 4 5 6 7 |
public class Employee { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } public decimal Salary { get; set; } } |
Step 2: Insertion
Add the following using statement at the top of the “Program.cs” file:
1 2 |
using Dapper; |
To insert a new employee, add the following code to the Program.cs
file:
1 2 3 |
var employee = new Employee { Name = "John Doe", Age = 30, Salary = 50000 }; string insertQuery = "INSERT INTO Employees (Name, Age, Salary) VALUES (@Name, @Age, @Salary); SELECT CAST(SCOPE_IDENTITY() as int)"; int employeeId = connection.Query<int>(insertQuery, employee).Single(); |
This code creates a new Employee object and inserts it into the Employees
table using Dapper’s Query method. The Query method returns the ID of the newly inserted employee.
Step 3: Data Selection
To retrieve a list of all employees, add the following code to the Program.cs
file:
1 2 3 4 5 6 |
string selectQuery = "SELECT * FROM Employees"; List<Employee> employees = connection.Query<Employee>(selectQuery).ToList(); foreach (var emp in employees) { Console.WriteLine($"Id: {emp.Id}, Name: {emp.Name}, Age: {emp.Age}, Salary: {emp.Salary}"); } |
This code retrieves all employees from the Employees
table using Dapper’s Query method and prints their information to the console.
Step 4: Updating
Add the following code to the Program.cs
file to update an employee’s information:
1 2 3 |
int idToUpdate = 1; string updateQuery = "UPDATE Employees SET Salary = @Salary WHERE Id = @Id"; connection.Execute(updateQuery, new { Salary = 60000, Id = idToUpdate }); |
This code updates the employee’s salary with ID 1 using Dapper’s Execute
method.
Conclusion
Dapper is a powerful ORM tool that simplifies database operations for C# developers. In this blog post, we created a sample project using Dapper and performed basic database operations. Dapper’s simplicity, flexibility, and performance make it an ideal choice for developers who want to get things done quickly and efficiently. If you haven’t tried Dapper yet, try it and see how it can simplify your database operations. Do you know the differences between IEnumerable
and IQueryable
? If not, learn them here.