Published on: 26 August, 2016

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

Posted by andy | 4656 views 2 likes 0 favourites 8 comments
Tumblr Digg Google Plus

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
Commented on: 27 Aug, 2017 11:34:47 AM
Console.WriteLine("Customer record has been deleted successfully"); This should read "inserted" successfully for the insert SP :)
andy
Commented on: 28 Aug, 2017 06:33:47 AM
thx Luke for the correction. I have updated the article.
Ray
Commented on: 29 Nov, 2017 01:32:38 PM
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
Commented on: 30 Nov, 2017 09:22:51 AM
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
Commented on: 01 Dec, 2017 12:35:33 PM
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
Commented on: 01 Dec, 2017 03:00:54 PM
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
Commented on: 01 Dec, 2017 06:19:47 PM
Hi Andy, It works with your new code. Thanks for your great support .
andy
Commented on: 04 Dec, 2017 09:23:15 AM
You are welcome ;-)
Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.
Share your article with us and add your own google adsense account to earn extra money. Plus, you can promote a link back to your site.
Related Articles
Published on: 13 February, 2017
One thing to remember when publishing your ASP.Net web applications, you may find out, some files could be not completely published due to the settings on the file properties. One example that I had are some font types file. In order to fix this problem you can navigate to the files that are missing and right click the properties.
Published on: 11 May, 2016
The slidingExpiration attribute is used in ASP.Net form authentication. This property is used to reset the expiration time for a valid authentication cookie if a request that has already being made has passed the timeout interval. Which means, if it is already expires, the current user will require to re-authentication again.
Related Tutorials