Dapper CRUD Win Forms - Part Two

The source code is in part one of the articles,

• Dapper CRUD Win Forms - Part One

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,

using DapperRepoWinForm.Forms;
namespace DapperRepoWinForm {
    static class Program {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() {

            showForm();
        }

        public static void showForm() {
            try {
                //Remenber you must have this file--> c:\conn.xml
                string path = Utilities.Globals.path;
                Utilities.Globals.stringConn = ConnectionDB.xml_conn(path);
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Form1 frm = new Form1();
                frm.Show();
                Application.Run();
            } catch (Exception e) {
                MessageBox.Show(e.Message);
            }
            // Do some more work here
        }
    }
}

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

using System.Windows.Forms;
using System.Collections.Generic;
using System.Data;

public partial class Form1: Form {
    ClassObjects.users _user = new ClassObjects.users();
    Bll.users _metod = new Bll.users();
    public Form1() {
        InitializeComponent();
    }

    private void Form1_Load(object sender, System.EventArgs e) {
    }
}

 A function to clean the fields, 

private void cleanform() {
    foreach(Control c in this.Controls) {
        if (c is TextBox) {
            (c as TextBox).Text = string.Empty;
        }
    }
}

Code for toolstrip´s buttoms 

 

Button Save: Call function to clean form´s fields, 

private void btnNew_Click(object sender, System.EventArgs e) {
    cleanform();
}

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,

private void btnSave_Click(object sender, System.EventArgs e) {
    _user.id = textBox1.Text;
    _user.name = textBox2.Text;
    _user.address = textBox3.Text;
    _user.status = textBox4.Text;
    string msg = _metod.insertUpdate(_user);
    if (msg == "0") {
        MessageBox.Show("Record added");
        cleanform();
    } else {
        MessageBox.Show(msg);
    }

}

Button clean: we will only clean the textboxs form,

private void btnClear_Click(object sender, System.EventArgs e) {
    cleanform();
}

Delete button

 

This code deletes the record whose Id matches the value of the TextBox1 control,

private void btnDelete_Click(object sender, System.EventArgs e) {
    _user.id = textBox1.Text;
    string msg = _metod.delete(_user);
    if (msg == "0") {
        MessageBox.Show("record deleted");
        cleanform();
    } else {
        MessageBox.Show(msg);
    }
}

Search button

 

This button executes a record search based on the ID field written to the TextBox1 control

private void btnFind_Click(object sender, System.EventArgs e) {
    if (textBox1.Text != "") {
        var items = _metod.findById(textBox1.Text);
        if (items != null) {
            textBox2.Text = items.name;
            textBox3.Text = items.address;
            textBox4.Text = items.status;
        } else {
            MessageBox.Show("Record not found");
        }
    }
}

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,

private void btnPopulate_Click(object sender, System.EventArgs e) {
    List < ClassObjects.users > lista = _metod.allRecords(_user);
    dataGridView1.DataSource = lista;
    int i = 0;
    string xx = "";
    dataGridView2.AutoGenerateColumns = false;
    foreach(var prop in _user.GetType().GetProperties()) {
        dataGridView2.Columns[i].DataPropertyName = prop.Name;
        i = i + 1;
    }
    dataGridView2.DataSource = lista;
}

Populate Dynamics button

 

This button returns a dynamic list so that you can fill the grid with the FG.ConvertToDataTable ()

private void btnPopulateDyn_Click(object sender, System.EventArgs e) {
    Utilities.Funciones FG = new Utilities.Funciones();
    var items = _metod.dynamicsList();
    DataTable dt = new DataTable();
    dt = FG.ConvertToDataTable(items);
    dataGridView1.DataSource = dt;
    dataGridView2.AutoGenerateColumns = false;
    for (int i = 0; i < dt.Columns.Count - 1; i++) {
        dataGridView2.Columns[i].DataPropertyName = dt.Columns[i].ColumnName;
    }
    dataGridView2.DataSource = dt;
}

I hope this small example will help your projects. I'm always attentive to your comments.