C# – Dapper

By | 06/12/2019

From Dapper Official Web Site:
Dapper is a simple object mapper for .NET and owns the title of King of Micro ORM in terms of speed and is virtually as fast as using a raw ADO.NET data reader.
An ORM is an Object Relational Mapper, which is responsible for mapping between database and programming language.
Dapper extends the IDbConnection by providing useful extension methods to query your database”
.
In a nutshell, we can use Dapper when we don’t want to use Entity Framework, for example if we need to create our Sql queries or if we have to run Stored Procedures.

In this post, we will see how to define and use Dapper in a Console Application.
First of all, we open SSMS, create a Database called “DapperDB” and then we run some sql scripts, in order to create tables and Stored Procedures:

TABUSERTYPE

CREATE TABLE [dbo].[TabUserType]
(
[Id] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_TabUserType] PRIMARY KEY CLUSTERED 
([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



TABUSER

CREATE TABLE [dbo].[TabUser]
([Id] [int] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](100) NOT NULL,[Surname] [nvarchar](100) NOT NULL,[TypeId] [int] NOT NULL,
CONSTRAINT [PK_TabUser] PRIMARY KEY CLUSTERED 
([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[TabUser]  WITH CHECK ADD  CONSTRAINT [FK_TabUser_TabUserType] FOREIGN KEY([TypeId])
REFERENCES [dbo].[TabUserType] ([Id])
GO

ALTER TABLE [dbo].[TabUser] CHECK CONSTRAINT [FK_TabUser_TabUserType]
GO



Now, we run these sql scripts, in order to feed the tables:

insert into TabUserType (Description) values ('Admin')
insert into TabUserType (Description) values ('Reader')
insert into TabUserType (Description) values ('Audit')


insert into TabUser (Name,Surname, TypeId) values ('Name1', 'Surname1', 1)
insert into TabUser (Name,Surname, TypeId) values ('Name2', 'Surname2', 2)
insert into TabUser (Name,Surname, TypeId) values ('Name3', 'Surname3', 2)
insert into TabUser (Name,Surname, TypeId) values ('Name4', 'Surname4', 2)
insert into TabUser (Name,Surname, TypeId) values ('Name5', 'Surname5', 2)
insert into TabUser (Name,Surname, TypeId) values ('Name6', 'Surname6', 2)
insert into TabUser (Name,Surname, TypeId) values ('Name7', 'Surname7', 3)
insert into TabUser (Name,Surname, TypeId) values ('Name8', 'Surname8', 3)



Finally, we will create three Stored Procedure using these scripts:

GETLISTUSERBYUSERTYPEID

Create procedure [dbo].[GetListUserByUserTypeId](@IdTypeUser int)    
AS    
BEGIN    
     SELECT TabUser.Name as UserName, TabUser.Surname as UserSurname, TabUserType.Description as UserUserType FROM TabUser inner join TabUserType on TabUserType.Id = TabUser.TypeId  
	 where TabUserType.Id=@IdTypeUser  
END 



GETLISTUSERTYPE

Create procedure [dbo].[GetListUserType]    
AS    
BEGIN    
     SELECT TabUserType.Id as UserTypeId, TabUserType.Description as UserTypeDescription FROM  TabUserType order by 1 
END 



GETCOUNTUSERBYUSERTYPEID

Create procedure [dbo].[GetCountUserByUserTypeId](@IdTypeUser int, @CountUsers int out)    
AS    
BEGIN    
     SELECT @CountUsers = count(*) FROM TabUser inner join TabUserType on TabUserType.Id = TabUser.TypeId  
	 where TabUserType.Id=@IdTypeUser    
END 



Now, we open Visual Studio Code, create a Console Application called “DapperPost” and we run, in the package Manager Console, the command Install-Package Dapper, in ordet to install Dapper.
After the installation, we define two Entities:

[USERTYPE.CS]

namespace DapperPost.Entities
{
    public class UserType
    {
        public int UserTypeId { get; set; }
        public string UserTypeDescription { get; set; }
    }
}

[USER.CS]

namespace DapperPost.Entities
{
    public class User
    {
        public string UserName { get; set; }
        public string UserSurname { get; set; }
        public string UserUserType { get; set; }
    }
}



Then, we create our Business Layer using a class, called DapperCore, defined in the interface IDapperCore:

[IDAPPERCORE.CS]

using DapperPost.Entities;
using System.Collections.Generic;

namespace DapperPost
{
    public interface IDapperCore
    {
        List<User> GetUserByUserType(int IdUserType);
        List<User> GetAllUser();
        List<UserType> GetAllUserType();
        int GetCountUserByUserType(int IdUserType);
    }
}



[DAPPERCORE.CS]

using Dapper;
using DapperPost.Entities;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DapperPost
{
    public class DapperCore:IDapperCore
    {
        private string connectionString;
        public DapperCore()
        {
            // In a real project, it should be better putting the connection string in a Configuration file
            connectionString = "Server=localhost;Database=DapperDB;Trusted_Connection=True;MultipleActiveResultSets=true";
        }
        public List<User> GetAllUser()
        {
            // Define the sql query to get the list of all users
            string strSqlQuery = "select TabUser.Name as UserName, TabUser.Surname as UserSurname, TabUserType.Description as UserUserType " +
                                 "from TabUser inner join TabUserType on TabUserType.Id = TabUser.TypeId order by TabUser.Surname";

            using IDbConnection dbTestDapper = new SqlConnection(connectionString);

            // run the query
            return dbTestDapper.Query<User>(strSqlQuery).ToList();
        }

        public List<UserType> GetAllUserType()
        {
            // Define the Stored Procedure.
            // In a real project, it would be better putting this value in an enum or in a Config file
            string spGetListUserType = "GetListUserType";

            using IDbConnection dbTestDapper = new SqlConnection(connectionString);
            
            // run the Stored Procedure
            return dbTestDapper.Query<UserType>(spGetListUserType, commandType: CommandType.StoredProcedure).ToList();
        }

        public List<User> GetUserByUserType(int IdUserType)
        {
            // Define the Stored Procedure.
            // In a real project, it would be better putting this value in an enum or in a Config file
            string spGetListUserByUserType = "GetListUserByUserTypeId";

            using IDbConnection dbTestDapper = new SqlConnection(connectionString);

            // Define the parameter to pass at Stored Procedure
            DynamicParameters parameter = new DynamicParameters();

            parameter.Add("@IdTypeUser", IdUserType, DbType.Int32, ParameterDirection.Input);

            // Run the Stored Procedure
            return dbTestDapper.Query<User>(spGetListUserByUserType, parameter, commandType: CommandType.StoredProcedure).ToList();
        }

        public int GetCountUserByUserType(int IdUserType)
        {
            // Define the Stored Procedure.
            // In a real project, it would be better putting this value in an Enum or in a Config file
            string spGetCountUserByUserTypeId = "GetCountUserByUserTypeId";
            // Define the output parameter
            string outParameter = "@CountUsers";

            using IDbConnection dbTestDapper = new SqlConnection(connectionString);

            // Define the parameters to pass at Stored Procedure
            DynamicParameters parameter = new DynamicParameters();

            parameter.Add("@IdTypeUser", IdUserType, DbType.Int32, ParameterDirection.Input);
            parameter.Add(outParameter, dbType: DbType.Int32, direction: ParameterDirection.Output);

            // Run the Stored Procedure
            dbTestDapper.Execute(spGetCountUserByUserTypeId, parameter, commandType: CommandType.StoredProcedure);

            return parameter.Get<int>(outParameter);
        }
    }
}



Finally, we modify the file Program.cs:

using System;

namespace DapperPost
{
    class Program
    {
        static void Main(string[] args)
        {
            DapperCore objDapperCore = new DapperCore();

            // List all Users
            PrintListAllUsers(objDapperCore);

            Console.WriteLine();
            Console.WriteLine();

            // List all User Types
            PrintListAllUserTypes(objDapperCore);

            Console.WriteLine();
            Console.WriteLine();

            // Count User by User Type = 2 (Reader)
            PrintCountUserByUserType(objDapperCore, 2);

            Console.WriteLine();
            Console.WriteLine();

            // List all Users by User Type
            PrintListUserByUserTypes(objDapperCore, 2);

            Console.ReadKey();
        }

        private static void PrintListAllUsers(DapperCore objDapperCore)
        {
            var lstUser = objDapperCore.GetAllUser();

            Console.WriteLine("###########  GET ALL USERS  ###########");

            foreach (var item in lstUser)
            {
                Console.WriteLine($"User: {item.UserName} {item.UserSurname} - Type of User: {item.UserUserType}");
            }

            Console.WriteLine("#######################################");
        }

        private static void PrintListAllUserTypes(DapperCore objDapperCore)
        {
            var lstUserType = objDapperCore.GetAllUserType();

            Console.WriteLine("###########  GET ALL USER TYPES  ###########");

            foreach (var item in lstUserType)
            {
                Console.WriteLine($"UserTypeId: {item.UserTypeId} - Description User Type: {item.UserTypeDescription}");
            }

            Console.WriteLine("###########################################");
        }

        private static void PrintCountUserByUserType(DapperCore objDapperCore, int userTypeId)
        {
            var countUser = objDapperCore.GetCountUserByUserType(userTypeId);

            Console.WriteLine("###########  GET COUNT USERS BY USER TYPE  ###########");

            Console.WriteLine($"Number of Reader users: {countUser}");

            Console.WriteLine("######################################################");
        }

        private static void PrintListUserByUserTypes(DapperCore objDapperCore, int userTypeId)
        {
            var lstUser = objDapperCore.GetUserByUserType(userTypeId);

            Console.WriteLine("###########  GET ALL USER BY USER TYPE  ###########");

            foreach (var item in lstUser)
            {
                Console.WriteLine($"User: {item.UserName} {item.UserSurname} - Type of User: {item.UserUserType}");
            }

            Console.WriteLine("###########################################");
        }
    }
}



If we run the application, this will be the result:



Leave a Reply

Your email address will not be published.