IMG-LOGO

How to use Dapper.Net to perform ORM mapping to database table entity via stored procedure?

andy - 26 Aug, 2016 10221 Views 8 Comment

In this tutorial you will learn how easily you can use Dapper.Net to map a database table object. So why do we need to use ORM mapping? Well the answer is simple, the main reason is to save time in coding. You can imagine if you have table contains multiple column fields and you have to map it one by one manually. This can be time consuming.

The first thing you need to do is to add Dapper.Net library dll into your project. You can easily add this by using Nuget Package Manager. Type the following install command.

Install-Package Dapper

Let starts with the code programming. You will learn how to list, insert, update or delete a record using Dapper.net with stored procedure options. In this particular example, we will program it under Console Application program. The concept of the code would be the same for windows or web programming. Imagine we have a customer table with the following column fields.

CREATE TABLE Customers (
    customer_id     int IDENTITY(1, 1) PRIMARY KEY,
    name   varchar(50),
    email varchar(50)
)

We will then need to create the following stored procedures

CREATE PROCEDURE GetCustomers
	
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM
	Customers
END
GO

CREATE PROCEDURE GetCustomer
	@customer_id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM
	Customers
	WHERE customer_id = @customer_id
END
GO

CREATE PROCEDURE DeleteCustomer
	@customer_id int
AS
BEGIN
	SET NOCOUNT ON;

	Delete FROM
	Customers
	WHERE customer_id = @customer_id
END
GO

CREATE PROCEDURE InsertCustomer
	@name nvarchar(50),
	@email nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO Customers(
		name,
		email
	)VALUES(
		@name,
		@email
	)
END
GO

CREATE PROCEDURE UpdateCustomer
	@customer_id int,
	@name nvarchar(50),
	@email nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE Customers
	SET	name = @name,
		email = @email
	WHERE customer_id = @customer_id
END
GO

Here is the full code to perform above stored procedure queries.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = @"Data Source=.\SQLExpress;Initial Catalog=sample;User ID=sa;Password=sa_password";
            SqlConnection objConnection = new SqlConnection(connectionString);
            
            //List all customers
            List<CustomerInfo> customerList = SqlMapper.Query<CustomerInfo>(objConnection, "GetCustomers", commandType: CommandType.StoredProcedure).ToList();
            foreach (var customer in customerList)
            {
                Console.WriteLine(string.Format("{0}, {1}, {2}", customer.customer_id.ToString(), customer.name, customer.email));
            }

            //Insert Customer record
            DynamicParameters p = new DynamicParameters();
            p.Add("@name", "Andy");
            p.Add("@email", "info@bytutorial.com");
            SqlMapper.Query<CustomerInfo>(objConnection, "InsertCustomer", p, commandType: CommandType.StoredProcedure);
            Console.WriteLine("Customer record has been inserted successfully");

            //Update customer record
            p = new DynamicParameters();
            p.Add("@customer_id", 1);
            p.Add("@name", "Andy");
            p.Add("@email", "info@bytutorial.com");
            SqlMapper.Query<CustomerInfo>(objConnection, "UpdateCustomer", p, commandType: CommandType.StoredProcedure);
            Console.WriteLine("Customer record has been updated successfully");

            //Delete customer record
            p = new DynamicParameters();
            p.Add("@customer_id", 3);
            SqlMapper.Query<CustomerInfo>(objConnection, "DeleteCustomer", p, commandType: CommandType.StoredProcedure);
            Console.WriteLine("Customer record has been deleted successfully");

            Console.ReadLine();
        }
    }

    public class CustomerInfo
    {
        public int customer_id { get; set; }
        public string name { get; set; }
        public string email { get; set; }
    }
}

If you get an errors or cant compile. There could be only two possible issue you have. First one is you forget to include Dapper.Net library to your project and second option is you may have entered wrong connection string information in the code.

Comments

Luke
27 Aug, 2017
Console.WriteLine("Customer record has been deleted successfully"); This should read "inserted" successfully for the insert SP :)
andy
28 Aug, 2017
thx Luke for the correction. I have updated the article.
Ray
29 Nov, 2017
Hi , When I execute the following the code , it show the "List" type errors. Could it be lack the CustomerInfo type to convert ? Thanks. //List all customers List customerList = SqlMapper.Query(objConnection, "GetCustomers", commandType: CommandType.StoredProcedure).ToList();
andy
30 Nov, 2017
Hi Ray, Did you build the code in the console application or a site? Try build the project and see if you receive any error before running it. The list in here represents the object list of System.Collections.Generic
Ray
01 Dec, 2017
Hi Andy, Thanks for your response. After building the project , it will show the error "CS0305 C# Using the generic type 'List' requires 1 type arguments" message. It is little strange that "Customerinfo never used in the code. Currently , I replaced the "list ..." with "var ..." statement. It will work. Maybe I need some time to verify it.
andy
01 Dec, 2017
Sorry Ray, My mistake I forgot to decode any symbol < and > in the source code. I have decoded all the source code, and you should see the correct version now. You can use the following option: List<CustomerInfo> Or you can use var alternatively.
Ray
01 Dec, 2017
Hi Andy, It works with your new code. Thanks for your great support .
andy
04 Dec, 2017
You are welcome ;-)
Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.

Related Articles

How to remove html tags from string in c#?

Sometimes you need to remove HTML tags from string to ensure there are no dangerous or malicious scripts especially when you want to store the string or data text into the database.