Post
Ask Question

Dapper CRUD Win Forms - Part One

Create a SQL Server database with the name "DapperDB"

 
For purposes of the example, we can restore it from the sample file or use the code given below to create it.
  1. USE[DapperDB]  
  2. GO  
  3.   
  4. SET ANSI_NULLS ON  
  5. GO  
  6.   
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9.   
  10. CREATE TABLE[dbo]. [users](  
  11.     [id][nvarchar](50) NOT NULL,  
  12.     [name][nvarchar](50) NULL,  
  13.     [address][nvarchar](50) NULL,  
  14.     [status][nvarchar](50) NULL,  
  15.     CONSTRAINT[PK_users] PRIMARY KEY CLUSTERED(  
  16.         [id] ASC  
  17.     ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]  
  18. ) ON[PRIMARY]  
  19. GO  
  20.   
  21. USE[DapperDB]  
  22. GO  
  23.   
  24. /****** Object: StoredProcedure [dbo].[users_DeleteRow_By_id] Script Date: 06/02/2017 11:13:02 a.m. ******/  
  25.   
  26. SET ANSI_NULLS ON  
  27. GO  
  28.   
  29. SET QUOTED_IDENTIFIER ON  
  30. GO  
  31.   
  32. CREATE Procedure[dbo]. [users_DeleteRow_By_id]  
  33. @id nvarchar(50)  
  34. As  
  35. Begin  
  36. Delete users  
  37. Where[id] = @id  
  38. End  
  39. GO  
  40.   
  41. USE[DapperDB]  
  42. GO  
  43.   
  44. SET ANSI_NULLS ON  
  45. GO  
  46.   
  47. SET QUOTED_IDENTIFIER ON  
  48. GO  
  49.   
  50. CREATE Procedure[dbo]. [users_Insert_Update]  
  51. @id nvarchar(50),  
  52.     @name nvarchar(50),  
  53.     @address nvarchar(50),  
  54.     @status nvarchar(50)  
  55. As  
  56. BEGIN  
  57. IF NOT EXISTS(SELECT * FROM dbo.users u WHERE u.id = @id)  
  58. BEGIN  
  59.   
  60. Insert Into users([id], [name], [address], [status])  
  61. Values(@id, @name, @address, @status)  
  62. END  
  63. ELSE  
  64. BEGIN  
  65. Update users  
  66. Set  
  67.     [id] = @id,  
  68.     [name] = @name,  
  69.     [address] = @address,  
  70.     [status] = @status  
  71. Where[id] = @id  
  72. END  
  73. End  
  74. GO  
  75.   
  76. USE[DapperDB]  
  77. GO  
  78.   
  79. SET ANSI_NULLS ON  
  80. GO  
  81.   
  82. SET QUOTED_IDENTIFIER ON  
  83. GO  
  84.   
  85. CREATE Procedure[dbo]. [users_SelectAll]  
  86. As  
  87. Begin  
  88. SELECT dbo.users.id, dbo.users.name, dbo.users.address, dbo.users.status FROM dbo.users  
  89. End  
  90. GO  
  91.   
  92. USE[DapperDB]  
  93. GO  
  94.   
  95. SET ANSI_NULLS ON  
  96. GO  
  97.   
  98. SET QUOTED_IDENTIFIER ON  
  99. GO  
  100.   
  101. CREATE Procedure[dbo]. [users_SelectRow_By_id]  
  102. @id nvarchar(50)  
  103. As  
  104. Begin  
  105. Select  
  106. dbo.users.id, dbo.users.name, dbo.users.address, dbo.users.status  
  107. From users  
  108. Where[id] = @id  
  109. End  
  110. GO  
  111.   
  112. USE[DapperDB]  
  113. GO  
  114.   
  115. SET ANSI_NULLS ON  
  116. GO  
  117.   
  118. SET QUOTED_IDENTIFIER ON  
  119. GO  
  120.   
  121. CREATE Procedure[dbo]. [users_SelectwithDate]  
  122. As  
  123. Begin  
  124. SELECT dbo.users.id, dbo.users.name, dbo.users.address, dbo.users.status, getdate() AS[date]  
  125. FROM dbo.users  
  126. End  
  127. GO 

Create a Windows form C# Application with the name "DapperRepoWinForm"

 
Add the Dapper package to the project.
 
Click on tools-> NuGet Packages Manager-> Manage NuGet Packages for Solution.
 
 
Now, click on the Manage Nuget Packages option, then Windows given below will appear.
  • Click on Browse.
  • As shown in the image, type in the search box “dapper”.
  • Select Dapper, as shown in the image.
  • Check the project solution.
Click on the install button.
 
 
Creating folders 
 
Now, we must create a folder in the project. We'll call it utilities. Inside it, we will create a class named "Globals".
 
This class will serve to create a variable "stringConn", which contains the information for the login in the database. A constant path with the address of the XML file contains the login information. 
  1. using System;    
  2. using System.Collections.Generic;    
  3. using System.Data;    
  4.     
  5. namespace DapperRepoWinForm.Utilities {    
  6.     public static class Globals {    
  7.         public static String stringConn = "";    
  8.         public    
  9.         const string path = "c:\\conn.xml";    
  10.     }    
  11. }   
Now, we will create a class, which we will call "ConnectionDB".
 
This class is used to read the XML file and get the data for the login. Here, you can place the code to encrypt the information. For this example, I do not put it. 
  1. using System.Xml;    
  2.     
  3. namespace DapperRepoWinForm.Utilities {    
  4.     class ConnectionDB {    
  5.         public static string xml_conn(string path) {    
  6.             XmlDocument xmlDoc = new XmlDocument();    
  7.             xmlDoc.Load(path);    
  8.             XmlNodeList nodeList = xmlDoc.DocumentElement.SelectNodes("/Table/Conexion");    
  9.             string proServer = "", proDatabase = "", proUser = "", proPassword = "";    
  10.     
  11.             foreach(XmlNode node in nodeList) {    
  12.                 proServer = node.SelectSingleNode("Server").InnerText;    
  13.                 proDatabase = node.SelectSingleNode("Database").InnerText;    
  14.                 proUser = node.SelectSingleNode("User").InnerText;    
  15.                 proPassword = node.SelectSingleNode("Password").InnerText;    
  16.             }    
  17.             return ("Server = " + proServer + "; Database =" + proDatabase + "; User Id = " + proUser + ";Password = " + proPassword + ";");    
  18.         }    
  19.     }    
  20. }   
We create a folder in the project, which we will call it "Repository". Within this folder, we will create a class called "RepGen" In the beginning, add the code given below.
  1. using System.Data.SqlClient;      
  2. using Dapper;      
  3. using DapperRepoWinForm.Utilities; 
This class will serve to connect to the database and execute the stored procedures of type (non query, return scalar, or return numeric value).
 
How does it work
The void connection fills the value of the "with" variable with the connection data to the database.
  1. private void connection() {    
  2.     con = new SqlConnection(Globals.stringConn);    
  3. }   
The function executeNonQuery executes a no-query stored procedure (like, insert, update, delete).
  1. public string executeNonQuery(string query, DynamicParameters param) {    
  2.     try {    
  3.         connection();    
  4.         con.Open();    
  5.         con.Execute(query, param, commandType: CommandType.StoredProcedure);    
  6.         con.Close();    
  7.         return "0";    
  8.     } catch (Exception ex) {    
  9.         return ex.Message;    
  10.     }    
  11. }   
The fuction returnNumericValue executes a stored procedure with numeric return values.
  1. public string returnNumericValue(string query, DynamicParameters param) {    
  2.     try {    
  3.         string valor = "";    
  4.         param.Add("@output", dbType: DbType.Int32, direction: ParameterDirection.Output);    
  5.         param.Add("@Returnvalue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);    
  6.         // Getting Return value        
  7.         connection();    
  8.         con.Open();    
  9.         valor = con.ExecuteScalar < string > (query, param, commandType: CommandType.StoredProcedure);    
  10.         con.Close();    
  11.         return valor;    
  12.     } catch (Exception ex) {    
  13.         return ex.Message;    
  14.     }    
  15. }   
The returnScalar function executes stored procedures with return values ​​of type scalar.
  1. string returnScalar(string query, DynamicParameters param) {  
  2.     try {  
  3.         string valor = "";  
  4.         connection();  
  5.         con.Open();  
  6.         valor = con.ExecuteScalar < string > (query, param, commandType: CommandType.StoredProcedure);  
  7.         con.Close();  
  8.         return valor;  
  9.     } catch (Exception ex) {  
  10.         return ex.Message;  
  11.     }  
  12. } 
We will create a next class named "RepGen"
 
This class will call the stored procedures that return groups of data or a class. We will use dapper and fill a non-generic collection of the objects. I use it to fill the grids, which can be accessed individually by an index "Ilist <t>" or we can return a single class (for search purposes).
  1. using System.Collections.Generic;  
  2. using System.Linq;  
  3. using System.Data.SqlClient;  
  4. using Dapper;  
  5. using System.Data;  
  6. using DapperRepoWinForm.Utilities;  
  7.   
  8. namespace DapperRepoWinForm.Repository {  
  9.     class RepList < T > where T: class {  
  10.         public SqlConnection con;  
  11.         private void connection() {  
  12.             con = new SqlConnection(Globals.stringConn);  
  13.         }  
  14.         public List < T > returnListClass(string query, DynamicParameters param) {  
  15.             try {  
  16.                 connection();  
  17.                 con.Open();  
  18.                 IList < T > Tlista = SqlMapper.Query < T > (con, query, param, null, true, null, commandType: CommandType.StoredProcedure).ToList();  
  19.                 con.Close();  
  20.                 return Tlista.ToList();  
  21.             } catch (Exception) {  
  22.                 throw;  
  23.             }  
  24.         }  
  25.   
  26.         public T returnClass(string query, DynamicParameters param) {  
  27.             try {  
  28.                 connection();  
  29.                 con.Open();  
  30.   
  31.                 //     return this.con.Query( query, param, null, true, null, commandType: CommandType.StoredProcedure).FirstOrDefault();    
  32.                 T Tlista = SqlMapper.Query < T > (con, query, param, null, true, null, commandType: CommandType.StoredProcedure).FirstOrDefault();  
  33.                 con.Close();  
  34.                 return Tlista;  
  35.             } catch (Exception) {  
  36.                 throw;  
  37.             }  
  38.         }  
  39.     }  
  40. } 
In the utility folder, we will create a "Functions" class that contains a function that converts from IEnumerable to the datatable. This function serves to fill the grid controls.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Linq;  
  5. using System.Text;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace DapperRepoWinForm.Utilities {  
  9.     public class Funciones {  
  10.         public DataTable ConvertToDataTable(IEnumerable < dynamic > items) {  
  11.             var t = new DataTable();  
  12.             var first = (IDictionary < string, object > ) items.First();  
  13.             foreach(var k in first.Keys) {  
  14.                 var c = t.Columns.Add(k);  
  15.                 var val = first[k];  
  16.                 if (val != null)  
  17.                     c.DataType = val.GetType();  
  18.             }  
  19.   
  20.             foreach(var item in items) {  
  21.                 var r = t.NewRow();  
  22.                 var i = (IDictionary < string, object > ) item;  
  23.                 foreach(var k in i.Keys) {  
  24.                     var val = i[k];  
  25.                     if (val == null)  
  26.                         val = DBNull.Value;  
  27.                     r[k] = val;  
  28.                 }  
  29.                 t.Rows.Add(r);  
  30.             }  
  31.             return t;  
  32.         }  
  33.     }  
  34. } 
Now, we will create the folder ClassObjects. Inside the folder, we add a partial class "users".
  1. namespace DapperRepoWinForm.ClassObjects {  
  2.     public partial class users {  
  3.         public string id { get;  
  4.             set; }  
  5.         public string name { get;  
  6.             set; }  
  7.         public string address { get;  
  8.             set; }  
  9.         public string status { get;  
  10.             set; }  
  11.     }  
  12. } 
The folder Bll is created and inside the folder, we add the class "usersBll". This class calls the processes stored, using the repository.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using DapperRepoWinForm.ClassObjects;  
  7. using DapperRepoWinForm.Repository;  
  8. using DapperRepoWinForm.Utilities;  
  9. using Dapper;  
  10.   
  11. namespace DapperRepoWinForm.Bll {  
  12.     partial class users {  
  13.         public string insertUpdate(ClassObjects.users _users) {  
  14.             RepGen reposGen = new Repository.RepGen();  
  15.             DynamicParameters param = new DynamicParameters();  
  16.             param.Add("@id", _users.id);  
  17.             param.Add("@name", _users.name);  
  18.             param.Add("@address", _users.address);  
  19.             param.Add("@status", _users.status);  
  20.             return reposGen.executeNonQuery("users_Insert_Update", param);  
  21.         }  
  22.   
  23.         public string delete(ClassObjects.users _users) {  
  24.             RepGen reposGen = new Repository.RepGen();  
  25.             DynamicParameters param = new DynamicParameters();  
  26.             param.Add("@id", _users.id);  
  27.             return reposGen.executeNonQuery("users_DeleteRow_By_id", param);  
  28.         }  
  29.   
  30.         public List < ClassObjects.users > allRecords(ClassObjects.users _usuario) {  
  31.             RepList < ClassObjects.users > lista = new RepList < ClassObjects.users > ();  
  32.             DynamicParameters param = new DynamicParameters();  
  33.             return lista.returnListClass("users_SelectAll", param);  
  34.         }  
  35.   
  36.         public List < ClassObjects.users > AllRecordsById(string id) {  
  37.             RepList < ClassObjects.users > lista = new RepList < ClassObjects.users > ();  
  38.             DynamicParameters param = new DynamicParameters();  
  39.             param.Add("@id", id);  
  40.             return lista.returnListClass("users_SelectRow_By_id", param);  
  41.         }  
  42.   
  43.         public ClassObjects.users findById(string id)  
  44.   
  45.         {  
  46.             RepList < ClassObjects.users > class_usu = new RepList < ClassObjects.users > ();  
  47.             DynamicParameters param = new DynamicParameters();  
  48.             param.Add("@Id", id);  
  49.             return class_usu.returnClass("users_SelectRow_By_id", param);  
  50.         }  
  51.   
  52.         public List < dynamic > dynamicsList() {  
  53.             Funciones FG = new Funciones();  
  54.             DynamicParameters param = new DynamicParameters();  
  55.             Repository.RepList < dynamic > repo = new Repository.RepList < dynamic > ();  
  56.             var items = repo.returnListClass("users_SelectwithDate", param);  
  57.             return items;  
  58.         }  
  59.     }  
  60. } 
Creating the XML file
 
Open a text editor and create a new file.
  1. <?xml version="1.0" encoding="utf-8" standalone="yes"?>  
  2. <Table>  
  3.      <Conexion>  
  4.           <Server>GP08</Server>  
  5.           <Database>DB_MVC</Database>  
  6.           <User>Admin2</User>  
  7.           <Password>123456</Password>  
  8.      </Conexion>  
  9. </Table> 
Save it as conn.xml in "c:\”.
 
This XML provides information for login and database to the project.
 
In the next article, I will explain how to use dapper in forms to execute the queries, search for the records, and fill the grids.


Similar Articles