This article describes how to perform CRUD operations on the database using the dapper object relation mapperl

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.

CRUD Operation Using Dapper In C#

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.

  1. Entity framework
  2. Dapper
  3. NHibernate
CRUD Operation Using Dapper In C#

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,

  1. Execute
  2. Query
  3. QueryFirstOrDefault
  4. QuerySingle
  5. QuerySingleOrDefault
  6. QueryMultiple

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

Step 1Create a database and a table.

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

CRUD Operation Using Dapper In C#

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

CRUD Operation Using Dapper In C#

Step 3Open 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).

  1. <Grid Margin=”0,0,-31,-189″>  
  2.     <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″>  
  3.         <DataGrid.Columns>  
  4.             <DataGridTextColumn Binding=”{Binding Id}” Header=”ID” x:Name=”id” IsReadOnly=”True” Width=”Auto”></DataGridTextColumn>  
  5.             <DataGridTextColumn Binding=”{Binding Name}” Header=”Name” x:Name=”name” Width=”Auto”></DataGridTextColumn>  
  6.             <DataGridTextColumn Binding=”{Binding Marks}” Header=”Marks” x:Name=”city” Width=”Auto”></DataGridTextColumn>  
  7.             <DataGridTemplateColumn Header=”Edit”>  
  8.                 <DataGridTemplateColumn.CellTemplate >  
  9.                     <DataTemplate>  
  10.                         <Button Content=”Edit” Click=”Edit_Click” CommandParameter=”{Binding id}”></Button>  
  11.   
  12.                     </DataTemplate>  
  13.                 </DataGridTemplateColumn.CellTemplate>  
  14.             </DataGridTemplateColumn>  
  15.             <DataGridTemplateColumn Header=”Delete”>  
  16.                 <DataGridTemplateColumn.CellTemplate >  
  17.                     <DataTemplate>  
  18.                         <Button Content=”Delete” Click=”Delete_Click” ></Button>  
  19.                     </DataTemplate>  
  20.                 </DataGridTemplateColumn.CellTemplate>  
  21.             </DataGridTemplateColumn>  
  22.         </DataGrid.Columns>  
  23.     </DataGrid>  
  24.     <TextBox Grid.ColumnSpan=”2″ HorizontalAlignment=”Left” Height=”23″ TextWrapping=”Wrap” VerticalAlignment=”Top” Width=”120″ Margin=”143,34,0,0″ Name=”txtName”/>  
  25.     <Label Grid.ColumnSpan=”2″ Content=”Name:” HorizontalAlignment=”Left” Margin=”48,34,0,0″ VerticalAlignment=”Top”  Target=”{Binding ElementName=txtName}”/>  
  26.     <Label Grid.ColumnSpan=”2″ Content=”Marks:” HorizontalAlignment=”Left” Margin=”52,68,0,0″ VerticalAlignment=”Top”  Target=”{Binding ElementName=txtMarks}”/>  
  27.     <TextBox Grid.ColumnSpan=”2″ HorizontalAlignment=”Left” Height=”23″ TextWrapping=”Wrap” VerticalAlignment=”Top” Width=”120″ Margin=”143,68,0,0″ x:Name=”txtMarks”/>  
  28.     <Button x:Name=”btnsubmit” Grid.ColumnSpan=”2″ Content=”Submit” HorizontalAlignment=”Left” VerticalAlignment=”Top” Width=”75″ Margin=”109,100,0,0″ Click=”InsertOrUpdateStudent”/>  
  29. </Grid>  

The below screen will be created

CRUD Operation Using Dapper In C#

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 4Create a model class or entity of student type. Note that this entity will be mapped to Student table in the database.

  1. public class Student  
  2. {  
  3.    public int Id{get;set;}  
  4.    public string Name{get;set;}  
  5.    public int Marks {get;set;}  
  6. }  

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

PackagesIn the search bar search for Dapper then click install.

CRUD Operation Using Dapper In C#

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

  1. private string sqlConnectionString = @”Data Source = YourDatabaseServerAddress;initial catalog=YourDatabaseName;user id=YourDatabaseLoginId;password=YourDatabaseLoginPassword”;  
  2.   
  3. //This method gets all record from student table    
  4.         private List<Student> GetAllStudent()    
  5.         {    
  6.             List<Student> students = new List<Student>();    
  7.             using (var connection = new SqlConnection(sqlConnectionString))    
  8.             {    
  9.                 connection.Open();    
  10.                 students = connection.Query<Student>(“Select Id, Name, Marks from Student”).ToList();    
  11.                 connection.Close();    
  12.             }    
  13.             return students;    
  14.         }    
  15.     
  16.         //This method inserts a student record in database    
  17.         private int InsertStudent(Student student)    
  18.         {    
  19.             using (var connection = new SqlConnection(sqlConnectionString))    
  20.             {    
  21.                 connection.Open();    
  22.                 var affectedRows = connection.Execute(“Insert into Student (Name, Marks) values (@Name, @Marks)”, new { Name = student.Name, Marks = student.Marks });    
  23.                 connection.Close();    
  24.                 return affectedRows;    
  25.             }    
  26.         }    
  27.     
  28.         //This method update student record in database    
  29.         private int UpdateStudent(Student student)    
  30.         {    
  31.             using (var connection = new SqlConnection(sqlConnectionString))    
  32.             {    
  33.                 connection.Open();    
  34.                 var affectedRows = connection.Execute(“Update Student set Name = @Name, Marks = @Marks Where Id = @Id”, new { Id = studentId, Name = txtName.Text, Marks = txtMarks.Text });    
  35.                 connection.Close();    
  36.                 return affectedRows;    
  37.             }    
  38.         }    
  39.     
  40.         //This method deletes a student record from database    
  41.         private int DeleteStudent(Student student)    
  42.         {    
  43.             using (SqlConnection connection = new SqlConnection(sqlConnectionString))    
  44.             {    
  45.                 connection.Open();    
  46.                 var affectedRows = connection.Execute(“Delete from Student Where Id = @Id”, new { Id = studentId });    
  47.                 connection.Close();    
  48.                 return affectedRows;    
  49.             }    
  50.         }    
CRUD Operation Using Dapper In C#

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

CRUD Operation Using Dapper In C#

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.
https://dapper-tutorial.net/

BÌNH LUẬN

Please enter your comment!
Please enter your name here