Dapper CRUD Win Forms - Part Two

The source code is in part one of the article,
Fill the variables

In the program.cs the following code must be created. The variable path is assigned the value of the path where 
the conn.xml file is located and the variable Utilities.Globals.stringConn is assigned the value string connection of the function xml_conn,
  1. using DapperRepoWinForm.Forms;  
  2. namespace DapperRepoWinForm  
  3. {  
  4.     static class Program  
  5.     {  
  6.         /// <summary>  
  7.         /// The main entry point for the application.  
  8.         /// </summary>  
  9.         [STAThread]  
  10.         static void Main()  
  11.         {  
  12.               
  13.             showForm();  
  14.         }  
  15.   
  16.   
  17.         public static void showForm()  
  18.         {  
  19.             try  
  20.             {  
  21.                 //Remenber you must have this file--> c:\conn.xml  
  22.                 string path = Utilities.Globals.path;  
  23.                 Utilities.Globals.stringConn = ConnectionDB.xml_conn(path);  
  24.                 Application.EnableVisualStyles();  
  25.                 Application.SetCompatibleTextRenderingDefault(false);  
  26.                 Form1 frm = new Form1();  
  27.                 frm.Show();  
  28.                 Application.Run();  
  29.             }  
  30.             catch (Exception e)  
  31.             {  
  32.                 MessageBox.Show(e.Message);  
  33.             }  
  34.   
  35.   
  36.             // Do some more work here  
  37.         }  
  38.   
  39.     }  

Working with Forms and Controls

In the form1 form we are going to put some controls. The name of the controls to use are shown in the following image,



Note

In the datagridview2, in each column the Name value must be equal to the name of the datatable field
that fills it,



For this example the values ​​are,

ID USER = id
USERNAME = name
SHORT ADDRESS = address
USER STATUS = status

Adding some code
  • First we create an _user variable of type ClassObjects.users (); Which we will use in the field of form1
  • Second we create a _metod variable of type Bll.users (); Which we will use in the scope of form1 and will
  • serve to call the "methods" class BillUsers
  1. using System.Windows.Forms;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4.   
  5. public partial class Form1 : Form  
  6.     {  
  7.         ClassObjects.users _user = new ClassObjects.users();  
  8.         Bll.users _metod = new Bll.users();  
  9.         public Form1()  
  10.         {  
  11.             InitializeComponent();  
  12.         }  
  13.   
  14.         private void Form1_Load(object sender, System.EventArgs e)  
  15.         {  
  16.   
  17.         }  

 A function to clean the fields, 
  1. private void cleanform()  
  2.         {  
  3. foreach (Control c in this.Controls)  
  4.             {  
  5.                 if (c is TextBox)  
  6.                 {  
  7.                     (c as TextBox).Text = string.Empty;  
  8.                 }  
  9.             }              
  10.   
  11.   
  12.         } 
Code for toolstrip´s buttoms 
 
Button Save: Call function to clean form´s fields, 
  1. private void btnNew_Click(object sender, System.EventArgs e)  
  2.       {  
  3.           cleanform();  
  4.       } 
Button Save

Assign values ​​to the attributes of the _user class, then create a variable msg and this variable receives the value of the result of the process _metod.insertUpdate,
  1. private void btnSave_Click(object sender, System.EventArgs e)  
  2.      {  
  3.          _user.id = textBox1.Text;  
  4.          _user.name = textBox2.Text;  
  5.          _user.address = textBox3.Text;  
  6.          _user.status = textBox4.Text;  
  7.          string msg = _metod.insertUpdate(_user);  
  8.          if (msg == "0")  
  9.          {  
  10.              MessageBox.Show("Record added");  
  11.              cleanform();  
  12.          }  
  13.          else  
  14.          {  
  15.              MessageBox.Show(msg);  
  16.          }  
  17.   
  18.      } 
Button clean: we will only clean the textboxs form,
  1. private void btnClear_Click(object sender, System.EventArgs e)  
  2.         {  
  3.             cleanform();  
  4.         } 
Delete button

This code deletes the record whose Id matches the value of the TextBox1 control,
  1. private void btnDelete_Click(object sender, System.EventArgs e)  
  2.         {  
  3.             _user.id = textBox1.Text;  
  4.             string msg = _metod.delete(_user);  
  5.             if (msg == "0")  
  6.             {  
  7.                 MessageBox.Show("record deleted");  
  8.                 cleanform();  
  9.             }  
  10.             else  
  11.             {  
  12.                 MessageBox.Show(msg);  
  13.             }  
  14.         } 
Search button

This button executes a record search based on the ID field written to the TextBox1 control
  1. private void btnFind_Click(object sender, System.EventArgs e)  
  2.         {  
  3.             if (textBox1.Text != "")  
  4.             {  
  5.   
  6.                 var items = _metod.findById (textBox1.Text);  
  7.                 if (items != null)  
  8.                 {  
  9.                     textBox2.Text = items.name;  
  10.                     textBox3.Text = items.address;  
  11.                     textBox4.Text = items.status;  
  12.                 }  
  13.                 else  
  14.                 {  
  15.                     MessageBox.Show("Record not found");  
  16.                 }  
  17.   
  18.             }  
  19.         } 
Populate Button

This button returns a list of class _user, and fills the grid. For this exercise columns were created in the grid, then the properties of the _user class are traversed and the Name value is assigned to the property of the dataGridView2.Columns [n] .DataPropertyName,
  1. private void btnPopulate_Click(object sender, System.EventArgs e)  
  2.         {  
  3.             List<ClassObjects.users> lista = _metod.allRecords(_user);  
  4.             dataGridView1.DataSource = lista;  
  5.             int i = 0;  
  6.             string xx = "";  
  7.             dataGridView2.AutoGenerateColumns = false;  
  8.             foreach (var prop in _user.GetType().GetProperties())  
  9.             {  
  10.                 dataGridView2.Columns[i].DataPropertyName =  prop.Name;  
  11.                 i = i + 1;  
  12.             }  
  13.             dataGridView2.DataSource = lista;  
  14.         } 
Populate Dynamics button

This button returns a dynamic list, so that you can fill the grid with the FG.ConvertToDataTable ()
  1. private void btnPopulateDyn_Click(object sender, System.EventArgs e)  
  2.         {  
  3.              Utilities.Funciones FG = new Utilities.Funciones();  
  4.             var items = _metod.dynamicsList();  
  5.             DataTable dt = new DataTable();  
  6.             dt = FG.ConvertToDataTable(items);  
  7.             dataGridView1.DataSource = dt;  
  8.               
  9.            
  10.             dataGridView2.AutoGenerateColumns = false;  
  11.             for (int i = 0; i < dt.Columns.Count - 1; i++)  
  12.             {  
  13.                 dataGridView2.Columns[i].DataPropertyName = dt.Columns[i].ColumnName;  
  14.             }  
  15.             dataGridView2.DataSource = dt;  
  16.   
  17.   
  18.         } 
I hope this small example will help your projects. I'm always attentive to your comments.