CRUD Operation Using Dapper In C#

Prerequisites

We must have knowledge of C# programming
We must have an understanding of database
We must have an understanding of CRUD operations

Background

In most programming languages, we must keep maintainability and extensibility in mind. These are the most important factors for keeping the applications stable for a long time. These can be achieved using loose coupling in software modules and hence we separate modules to perform independent tasks.

While performing CRUD operations in the database, we must connect our software with the database using a database connection. This connectivity is important because when an application needs to restore data in the database, we need this connectivity.

In most of the applications, to achieve loose coupling, we maintain an ORM layer between a software application and the database, which maps database tables to C# entities.

ORM Layer

ORM layer is responsible to map database tables to c# entities and visa versa.

We have many ORM libraries available in C#, out of which the below are some main libraries.

Dapper

Dapper is an ORM library, which extends methods of IDbConnection interface. These extension methods have efficient code to perform insert, update delete and select methods. These extension methods are,

Execute
Query
QueryFirstOrDefault
QuerySingle
QuerySingleOrDefault
QueryMultiple

Let’s start with the implementation of CRUD operation using dapper.

Step 1

Create a database and a table.

Create table Student (Id Int Identity, Name Varchar(100), Marks Numeric)

Step 2

Open Visual Studio. File - New Project - WPF App (You can create either console application or web application according to your preferences).

Step 3

Open MainWinodw.xaml. Add two labels for Name and Marks, a submit button and a DataGrid as shown below. (In other application types you can add inputs and data grids according to application type).

<Grid Margin="0,0,-31,-189">
<DataGrid x:Name="studentDataGrid" x:Uid="employeeDataGrid" SelectionMode="Single" Height="auto" MaxHeight="300" HorizontalAlignment="Left" Margin="62,140,25,214" ItemsSource="{Binding}" AutoGenerateColumns="False" Grid.ColumnSpan="2">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding Id}" Header="ID" x:Name="id" IsReadOnly="True" Width="Auto"></DataGridTextColumn>
<DataGridTextColumn Binding="{Binding Name}" Header="Name" x:Name="name" Width="Auto"></DataGridTextColumn>
<DataGridTextColumn Binding="{Binding Marks}" Header="Marks" x:Name="city" Width="Auto"></DataGridTextColumn>
<DataGridTemplateColumn Header="Edit">
<DataGridTemplateColumn.CellTemplate >
<DataTemplate>
<Button Content="Edit" Click="Edit_Click" CommandParameter="{Binding id}"></Button>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
<DataGridTemplateColumn Header="Delete">
<DataGridTemplateColumn.CellTemplate >
<DataTemplate>
<Button Content="Delete" Click="Delete_Click" ></Button>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
</DataGrid.Columns>
</DataGrid>
<TextBox Grid.ColumnSpan="2" HorizontalAlignment="Left" Height="23" TextWrapping="Wrap" VerticalAlignment="Top" Width="120" Margin="143,34,0,0" Name="txtName"/>
<Label Grid.ColumnSpan="2" Content="Name:" HorizontalAlignment="Left" Margin="48,34,0,0" VerticalAlignment="Top" Target="{Binding ElementName=txtName}"/>
<Label Grid.ColumnSpan="2" Content="Marks:" HorizontalAlignment="Left" Margin="52,68,0,0" VerticalAlignment="Top" Target="{Binding ElementName=txtMarks}"/>
<TextBox Grid.ColumnSpan="2" HorizontalAlignment="Left" Height="23" TextWrapping="Wrap" VerticalAlignment="Top" Width="120" Margin="143,68,0,0" x:Name="txtMarks"/>
<Button x:Name="btnsubmit" Grid.ColumnSpan="2" Content="Submit" HorizontalAlignment="Left" VerticalAlignment="Top" Width="75" Margin="109,100,0,0" Click="InsertOrUpdateStudent"/>
</Grid>

The below screen will be created

Here we are creating UI only. This can be done in many ways according to the requirement. This UI is just to show implementation.

Step 4

Create a model class or entity of student type. Note that this entity will be mapped to Student table in the database.

public class Student
{
public int Id{get;set;}
public string Name{get;set;}
public int Marks {get;set;}
}

Step 5

Add reference of Dapper. Right click on solution explorer, click Manage Nuget.

Packages

In the search bar search for Dapper then click install.

Step 6

Write methods to perform select, insert, update and delete operations in database.

private string sqlConnectionString = @"Data Source = YourDatabaseServerAddress;initial catalog=YourDatabaseName;user id=YourDatabaseLoginId;password=YourDatabaseLoginPassword";
//This method gets all record from student table
private List<Student> GetAllStudent()
{
List<Student> students = new List<Student>();
using (var connection = new SqlConnection(sqlConnectionString))
{
connection.Open();
students = connection.Query<Student>("Select Id, Name, Marks from Student").ToList();
connection.Close();
}
return students;
}
//This method inserts a student record in database
private int InsertStudent(Student student)
{
using (var connection = new SqlConnection(sqlConnectionString))
{
connection.Open();
var affectedRows = connection.Execute("Insert into Student (Name, Marks) values (@Name, @Marks)", new { Name = student.Name, Marks = student.Marks });
connection.Close();
return affectedRows;
}
}
//This method update student record in database
private int UpdateStudent(Student student)
{
using (var connection = new SqlConnection(sqlConnectionString))
{
connection.Open();
var affectedRows = connection.Execute("Update Student set Name = @Name, Marks = @Marks Where Id = @Id", new { Id = studentId, Name = txtName.Text, Marks = txtMarks.Text });
connection.Close();
return affectedRows;
}
}
//This method deletes a student record from database
private int DeleteStudent(Student student)
{
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
connection.Open();
var affectedRows = connection.Execute("Delete from Student Where Id = @Id", new { Id = studentId });
connection.Close();
return affectedRows;
}
}

Step 7

All set. Now build the application and execute it. My WPF application output screens are below.

Points to be noted:

While using SQL database dapper uses ‘@’ symbol in query syntax, whereas it uses the ‘:’ symbol when using Oracle database.

Conclusion

Dapper is a faster object-relational mapping tool. It performs very well on bulk data.
Easy to write code.
Excellent entities mapping.

4 Comments

Join the conversation! Your thoughts help the community grow.

IMAD AYOUB6y ago
Thanks a lot for this excellent tutorial.
P E6y ago
Hi, I cannot connect to the DB... I downloaded your example and created a SQL Database in the solution folder (i do not have/want to install SQL Express or others in my computer). It seems I am doing wrong with the connection string... Could you please let me how should it look like in this case? Thanks in advance,
- Pawan Tiwari6y ago
  Hi, Which database server you want to connect. If its not in local machine, it must be in any remote location. How you have created database in solution folder. Can you please attach screen shot of solution folder or you can post connection string.
Ashok Chawdry7y ago
Thanks! Nice & simple explanation