Published on: 26 August, 2016

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

Posted by andy | 2370 views 2 likes 0 favourites 0 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 customerList = SqlMapper.Query(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(objConnection, "InsertCustomer", p, commandType: CommandType.StoredProcedure);
            Console.WriteLine("Customer record has been deleted successfully");

            //Update customer record
            p = new DynamicParameters();
            p.Add("@customer_id", 1);
            p.Add("@name", "Andy");
            p.Add("@email", "info@bytutorial.com");
            SqlMapper.Query(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(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

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.
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