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:
