Making a CRUD with C# .NET Winforms User Interface

Table of contents

• Overview
  • Data
  • Nested Folder
  • Screenshot
  • CREATE
  • READ
  • UPDATE
  • DELETE
  • Data Validation
• My process
  • What I learned
  • Continued development
  • Useful resources

Overview

A CRUD made of of a C# .Net Forms.

Data

** Data with * are mandatory. Numbers inside () are Maximum Length accepted.

• Data Variables
• EmployeeID* (11)
• FirstName* (50)
• MiddleName (50)
• LastName* (50)
• birthday
• addressUnitNum* (50)
• addressBrgy* (50)
• addressCity* (50)
• employeePosition* (50)
• employeeDepartment* (50)
• employeeCompany* (50)
• employeeDateJoined
• employeeDateLeft

Nested Folder

• EmployeeList
• README.md
• Images
• Form1.cs
• Form1.Designer
• Form1.resx
  • isDataValid.cs
  • dbconnection.cs
  • queries.cs

Screenshot

A screenshot of the user interface when ran.

A screenshot of the connected database from MySQL

My process

Create

• add input data to database

private void SaveButton_Click(object sender, EventArgs e)
{

        if (DataValidation.CheckEmployeeIDExist(employeeID.Text))
        {
            MessageBox.Show("Employee ID already exist. ", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            employeeID.Clear();
            return;
        }
        else if ((DataValidation.AnyValuesAreNotValid(employeeID.Text, firstName.Text, middleName.Text, lastName.Text,
                                                 addressUnitNum.Text, addressBrgy.Text, addressCity.Text,
                                                 employeePosition.Text, employeeDepartment.Text, employeeCompany.Text)) != "")
        {

            MessageBox.Show(DataValidation.AnyValuesAreNotValid(employeeID.Text, firstName.Text, middleName.Text, lastName.Text,
                                                 addressUnitNum.Text, addressBrgy.Text, addressCity.Text,
                                                 employeePosition.Text, employeeDepartment.Text, employeeCompany.Text), "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return;
        }
        else
        {
            db_connect.Open();
            cmd = new MySqlCommand(Queries.InsertQuery(), db_connect);
            cmd.Parameters.Clear();

            SAVETODATABASE();

            if (i > 0)
            {

                MessageBox.Show("Record Save Success!", "Employee Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Record Save Failed!", "Employee Record", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

            db_connect.Close();
            LoadRecord();
            ClearForm();
        }
  /*  
    catch (Exception ex)
    {
        MessageBox.Show("Warning: " + ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
    }*/
}

Read

  private void LoadRecord()
 {
     db_connect.Open();
    
     employeeRecordTable.Rows.Clear();
     cmd = new MySqlCommand(Queries.SelectQuery(), db_connect);

     dataRead = cmd.ExecuteReader();
     DATAREAD();

     dataRead.Close();
     db_connect.Close();
 }

Update

** Restriction: EmployeeID cannot change.

private void EditButton_Click(object sender, EventArgs e)
{
    EditButton.Enabled = true;

    cmd.Parameters.Clear();


   
    if ((DataValidation.AnyValuesAreNotValid(employeeID.Text, firstName.Text, middleName.Text, lastName.Text,
                                                addressUnitNum.Text, addressBrgy.Text, addressCity.Text,
                                                employeePosition.Text, employeeDepartment.Text, employeeCompany.Text)) != "")
    {

        MessageBox.Show(DataValidation.AnyValuesAreNotValid(employeeID.Text, firstName.Text, middleName.Text, lastName.Text,
                                             addressUnitNum.Text, addressBrgy.Text, addressCity.Text,
                                             employeePosition.Text, employeeDepartment.Text, employeeCompany.Text), "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }
    else
    {

        db_connect.Open();
        cmd = new MySqlCommand(Queries.UpdateQuery(), db_connect);
        SAVETODATABASE();

        if (i > 0)
        {
            MessageBox.Show("Record Update Success!", "Employee Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        else
        {
            MessageBox.Show("Record Update Failed!", "Employee Record", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }

        db_connect.Close();
        LoadRecord();
        ClearForm();
    }

}

Delete

  private void DeleteButton_Click(object sender, EventArgs e)
  {

      db_connect.Open();
      cmd = new MySqlCommand(Queries.DeleteQuery(), db_connect);
      cmd.Parameters.Clear();
      cmd.Parameters.AddWithValue("@EmployeeID", employeeID.Text); //get EmployeeIDText - input and run to query
                                                                       //delete if employeeID match   

      i = cmd.ExecuteNonQuery(); // execute mysqlcommand. checks if 'Column count match value count at row 1'

      if (i > 0)
      {
          MessageBox.Show("Record Delete Success!", "Employee Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
      }
      else
      {
          MessageBox.Show("Record Delete Failed!", "Employee Record", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
      }

      db_connect.Close();
      LoadRecord();
      ClearForm();


  }

Data Validation

• Mandatory Input the required data from the forms
• Won't save if empty is string
• Doesn't accepts more than 50 characters
• Doesn't accept duplicate EmployeeID (unique and 11 characters)

What I learned

• Classes
• C# (I am fairly new to c# but I am still learning)
• More OOP

Continued development

• 💡 Exception Handling
• 💡 Make it into a list or a dictionary(key:value)
• 💡 try to use LINQ
• 💡 fix interface

Useful resources

• EverSQL SQL Query Syntax Check & Validator
• SDPT Solutions Basic C# Playlist | Tagalog | Youtube Playlist
• Handling Exceptions in C# - When to catch them, where to catch them, and how to catch them | IAmTimCorey
• How to Use a Debugger - Debugger Tutorial | Tech With Tim