Dapper.NET, Component untuk akses data (database transaction) yang Handal, Cepat dan simple object mapper

Ada seorang rekan bercerita tentang Dapper.NET ... Dapper.NET is Open-Source product, very light ORM which is taken from Stack Overflow developer. Dapper easy to use and has compatibility with ado.net, odbc etc.

So what is the advantage of dapper? one of that is dapper realy easy to use, mapping object automatically. fast and great..!

How to use dapper for biginner?

1. First need to Download the component on https://code.google.com/p/dapper-dot-net/

2. Then open the visual studion, then make two project, one for data access library, and the second for User Interface (Form)
3. Add reference the Dapper  Library into project Data access library.
4. Then open database editor to create below table

 USE [db_Test]  
 GO  
 /****** Object: Table [dbo].[Test]  Script Date: 02/09/2016 23:21:16 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 SET ANSI_PADDING ON  
 GO  
 CREATE TABLE [dbo].[Test](  
      [ID] [uniqueidentifier] NOT NULL,  
      [Code] [varchar](100) NULL,  
      [Name] [varchar](100) NULL,  
  CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED   
 (  
      [ID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  
 GO  
 SET ANSI_PADDING OFF  
 GO  

5. Create an object/class Test like below code

  public class Test  
   {  
     public Guid ID { get; set; }  
     public string Code { get; set; }  
     public string Name { get; set; }  
     const string connString = "Server=MYSERVER;MYDATABASE=PJT2;User id=myuser;password=mypassword";  

     //READ
     public static IEnumerable<Test> GetAll()  
     {  

       //disini ketika select query dapper langsung menkonversi dari hasil select query 

       //menggunakan sql menjadi object Test
       using (IDbConnection conn = new SqlConnection(connString))  
       {  
         string sql = @" SELECT [ID] ,[Name] ,[Code] FROM [Test] ";   
         return conn.Query<Test>(sql).ToList();  
       }  
     }  

     //READ
     public static Test GetById(Guid id)  
     {  
       using (IDbConnection conn = new SqlConnection(connString))  
       {  
         string sql = @" SELECT [ID] ,[Name],[Code] FROM [Test] where ID=@ID ";   
         return conn.Query<Test>(sql, new { @ID = id }).FirstOrDefault();  
       }  
     }  

         //INSERT

     public static int Insert(Test p)  
     {  
       try  
       {  
         p.ID = Guid.NewGuid();  
         using (IDbConnection conn = new SqlConnection(connString))  
         {  
           string sql = @" INSERT INTO [dbo].[Test] ([ID] ,[Name],[Code]) VALUES (@ID ,@Name,@Code) ";  
           return conn.Execute(sql, p);  
         }  
       }  
       catch (Exception)  
       {  
         return -1;  
       }  
     }  

     //DELETE
     public static int Delete(Guid id)  
     {  
       try  
       {  
         using (IDbConnection conn = new SqlConnection(connString))  
         {  
           string sql = @" delete from [Test] where [ID] =@ID ";  
           return conn.Execute(sql, new { @ID = id });  
         }  
       }  
       catch (Exception)  
       {  
         return -1;  
       }  
     }  
   }  

6. Make sure the User Interfacenya become as this form image

• 3 textbox
• 2 button
• 1 datagridview

Please Design the form like this.

7. and below the source code of form1

 using System;  
 using System.Collections.Generic;  
 using System.ComponentModel;  
 using System.Data;  
 using System.Drawing;  
 using System.Linq;  
 using System.Text;  
 using System.Windows.Forms;  
 using AccessLayer;  
 namespace Dapper2  
 {  
   public partial class Form1 : Form  
   {  
     public Form1()  
     {  
       InitializeComponent();  
     }  
     private void Form1_Load(object sender, EventArgs e)  
     {  
       dataGridView1.DataSource = Test.GetAll();  
     }  
     private void btnSimpan_Click(object sender, EventArgs e)  
     {  
       Test test = new Test();  
       test.Code = textBoxCode.Text;  
       test.Name = textBoxName.Text;  
       Test.Insert(test);  
       dataGridView1.DataSource = Test.GetAll();  
     }  
     private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)  
     {  
       int rowIndex = e.RowIndex;  
       DataGridViewRow row = dataGridView1.Rows[rowIndex];  
       textBoxID.Text = string.Format("{0}", row.Cells[0].Value);  
       textBoxCode.Text = string.Format("{0}", row.Cells[1].Value);  
       textBoxName.Text = string.Format("{0}", row.Cells[2].Value);  
     }  
     private void btnHapus_Click(object sender, EventArgs e)  
     {  
       Guid id = Guid.Empty;  
       if (Guid.TryParse(textBoxID.Text, out id))  
         Test.Delete(id);  
       dataGridView1.DataSource = Test.GetAll();  
     }  
   }  
 }  

Event handle that must be in the form1 :
event dataGridView1_CellClick adalah event cell click dari datagrid view
event Form1_Load adalah event dari form1
event btnHapus_Click adalah event dari tombol hapus
event btnSimpan_Click adalah event dari tombol simpan

Kemudian setalah ok. compile yang sudah dibuat. dan selamat mencoba. kalau anda berhasil anda akan dapat insert dan hapus data. serta menampilakan data dari hasil yang di-insert.

Tulisan ini saya sertakan juga project solution yang dapat di download disini. Semuga anda cepat pandai ya. :)
jangan lupa tularkan ke temen yang lain agar pehala saya bertambah... hahaha..!!