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.