How to use Dapper ORM in C#

In this tutorial, I'll show you how to use micro ORM in C#.NET. As you know, Dapper ORM is free as open source software, it's a micro ORM framework allows you to access data form your database.

To play the demo you need to install Dapper from Nuget Package Manager or you can download it directly from https://github.com/StackExchange/Dapper, then create a Customer class to map data return from your table query

public class Customer
{
    public int CustomerId { get; set; }
    public string FullName { get; set; }
    public bool Gender { get; set; }
    public string Email { get; set; }
    public string Address { get; set; }
    public string Phone { get; set; }
}

Dapper provides support for both static and dynamic object binding using transactions, stored procedures or bulk inserts of data.

public List<Customer> GetAll()
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        return db.Query<Customer>("select *from Customers").ToList();
    }
}

You can use Query method to query data, then mapping your data return from database to entity. You can also use stored procedures, but you should mention the command type explicitly when calling Query or Execute methods

public List<Customer> GetAll()
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        return db.Query<Customer>("sp_Customer_GetAll", commandType: CommandType.StoredProcedure).ToList();
    }
}

You can use parameter to query filter data as below

public List<Customer> GetById(int id)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        return db.Query<Customer>($"select *from Customers where CustomerId = @CustomerId", new { CustomerId = id }).ToList();
    }
}

You can use Execute method to insert, update, or delete data into your database. This method returns an integer value that implies the number of rows affected when the query was executed.

public bool Insert(Customer obj)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        return db.Execute("insert into Customers(FullName, Gender, Email, Address, Phone) values(@FullName, @Gender, @Email, @Address, @Phone)", new { FullName = obj.FullName, Gender = obj.Gender, Email = obj.Email, Address = obj.Address, Phone = obj.Phone }) > 0;
    }
}

Update data

public bool Update(Customer obj)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        return db.Execute($"update Customers set FullName = N'{obj.FullName}', Gender = '{obj.Gender}', Email = '{obj.Email}', Address = N'{obj.Address}', Phone = '{obj.Phone}' where CustomerId = '{obj.CustomerId}'") > 0;
    }
}

Delete data

public bool Delete(int id)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        return db.Execute($"delete from Customers where CustomerId = @CustomerId", new { CustomerId = id }) > 0;
    }
}

Dapper ORM also supports transactions, you can take advantage of the BeginTransaction() and EndTransaction() methods as you usually do when working with transactions in ADO.Net

public Guid Create(Invoice obj)
{
    using (IDbConnection db = new SqlConnection(Helper.ConnectionString))
    {
        if (db.State == ConnectionState.Closed)
            db.Open();
        using (var transaction = db.BeginTransaction())
        {
            var id = db.QuerySingle<Guid>("INSERT INTO dbo.[Invoice](InvoiceNumber, BuyerName, BuyerAddress, BuyerAccountNumber, BuyerBankName, Total, InvoiceDate) OUTPUT INSERTED.[Id] VALUES(@InvoiceNumber, @BuyerName, @BuyerAddress, @BuyerAccountNumber, @BuyerBankName, @Total, @InvoiceDate)",
                new { InvoiceNumber = obj.InvoiceNumber, BuyerName = obj.BuyerName, BuyerAddress = obj.BuyerAddress, BuyerAccountNumber = obj.BuyerAccountNumber, BuyerBankName = obj.BuyerBankName, Total = obj.Total, InvoiceDate = obj.InvoiceDate }, transaction);
            if (obj.InvoiceDetails != null)
            {
                foreach (InvoiceDetails detail in obj.InvoiceDetails)
                {
                    db.Execute("INSERT INTO dbo.[InvoiceDetails](InvoiceId, ItemId, ItemName, Unit, Vat, Quantity, Price) VALUES(@InvoiceId, @ItemId, @ItemName, @Unit, @Vat, @Quantity, @Price)", new { InvoiceId = id, ItemId = detail.ItemId, ItemName = detail.ItemName, Unit = detail.Unit, Vat = detail.Vat, Quantity = detail.Quantity, Price = detail.Price }, transaction);
                }
            }
            transaction.Commit();
            return id;
        }
    }
}