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.

1. Entity framework
2. Dapper
3. NHibernate

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 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.