Basic CRUD in Visual Studio Windows form with Xampp phpmyadmin DB

Run the XAMPP Control Panel as Administrator and start both the Apache and MySQL modules by clicking the Start buttons.

Open your browser and go to http://localhost/phpmyadmin.

To create a new Database you have 2 options without writing SQL and in SQL.

let's discuss the new database without SQL, click New on the left sidebar, enter your database name (e.g., studentrecord), and click Create.

In the newly created database in this case the studentrecord, clicks on the studentrecords from the list on the left panel and,

click the Structure and enter the desired table name and a number of columns for the attribute example is the tbl_course and number of columns of 2 click GO.

Then enter this value and set the course_id as the primary key check the A_I checkbox and click SAVE.

Column Name	Type	Length/Values
course_id	INT	11
CourseName	VARCHAR	50

You can see the newly created table under the studentrecord database and the attribute or column in the structure (Note: the Change icon allows you to edit the column and Drop to delete the column)

To insert data in the table Click on the tbl_course table, Go to the Insert tab, add the coursename disregard the courseid cause it will automatically add a value there unless you want to specially add custom courseid, Click Go to save each row.

Add rows like,

• CourseName: BSIT
• CourseName: BSCS
• CourseName: BSEd

The result can be seen in the Browse tab.

Now create the studentdetails table.

Column Name	Type	Length/Values
StudentID	INT	11
Firstname	VARCHAR	50
Middlename	VARCHAR	50
Lastname	VARCHAR	50
Gender	VARCHAR	10
Bdate	DATE
Course	VARCHAR	50

Alternatively to create the database you can use the SQL tab and enter this query.

-- Create the main student database
CREATE DATABASE IF NOT EXISTS student_db;
USE student_db;

-- Create the 'tbl_course' table
CREATE TABLE tbl_course (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL
);

-- Insert some sample courses
INSERT INTO tbl_course (CourseName) VALUES 
('BSIT'), 
('BSCS'), 
('BSEd');

-- Create the 'prefinalexam' table
CREATE TABLE prefinalexam (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    Firstname VARCHAR(50) NOT NULL,
    Middlename VARCHAR(50),
    Lastname VARCHAR(50) NOT NULL,
    Gender VARCHAR(10),
    Bdate DATE,
    Course VARCHAR(50)
);

Setting Up MySQL in a Windows Forms App (C#)

To get started with connecting your Windows Forms App to a MySQL database, you'll first need to create a project and install the required MySQL library.

1. Create a Windows Forms App Project

Open Visual Studio and start a new Windows Forms App project using C#.

2. Install the MySQL Connector via NuGet

Before writing any database code, you need to install the MySql.Data package.

• Go to Tools > NuGet Package Manager > Manage NuGet Packages for Solution, or right-click your project in the Solution Explorer and select Manage NuGet Packages.
• In the Browse tab, search for MySql.Data.
• Click on the package published by Oracle.
• Select your project, then click Install.

3. Import the MySQL Library

After installation, go to your Form1.cs file and add the following line at the top of your code.

using MySql.Data.MySqlClient;

This line imports the MySQL Connector/NET library, which gives your application access to the tools it needs to connect to and interact with a MySQL database—such as running queries and reading data.

Set up of the design

Add controls on the design tab of Form as the picture shows.

Open the Toolbox

If the Toolbox isn't already visible, go to the top menu and click View > Toolbox

(or press Ctrl+Alt+X).

Drag and Drop Controls to the Form

From the Toolbox, find the controls you need under Common Controls.

Simply drag the control from the Toolbox and drop it onto the form.

• Label: displays text
• TextBox: for user input
• Button: to trigger events like Save, Update, Delete
• DateTimePicker: to select a date
• RadioButton: for selecting gender (Male/Female)
• ComboBox: to choose a course
• DataGridView: to display table data

Set Properties After placing a control

• Select it.
• In the Properties Window (usually bottom-right), you can set
  • Name (e.g., tbFirstname, cbcourse)
  • Text (e.g., change “button1” to “Insert”)
  • Font, Size, Location, and more

Organize and Align

The name of the control can be set like on the picture (change the label text for desired text)

you can modify the name of the controls in Properties:Name and the text of the controls in design by Properties: Text.(can be seen at the bottom of properties)

you can see this by clicking View > Other Windows > Document Outline.

Properties Set up

DataGridView

• AllowUsertoAddRows = False
• AllowUsertoDeleteRows = False
• AutoSizeColumnsMode = Fill

ComboBox(cb_Course)

• DropDownStyle = DropDownList

Double-click a button or other control to auto-generate a click event handler in your Form1.cs file, where you can write the logic.In our case the button Insert, Update, Clear, Delete, a textbox for search bar, and the Cellclick on datagridview.

to create a Event hadler for Cellclick on datagridview go to Properties > Click the lightning bolt icon ? at the top of the Properties window to view available events. Scroll down to find the CellClick event. Double-click the empty space beside CellClick.

At the top of your code, inside your namespace (e.g. BasicCRUD) prepare the MySQL connection and selectedstudentid This will help identify the studentid in the datagridview

change the databasename according to what you set in the myphpadmin "server=localhost;user id=root;password=;database=databasename";

public partial class Form1 : Form
{
    private string connection = "server=localhost;user id=root;password=;database=studentrecord";
    int selectedstudentid = -1;

Creating Essential Methods for Data Management and User Interaction

Display Records in the DataGridView

Create a method called loaddata() that retrieves records from your studentdetails table and shows them in the DataGridView.

private void loaddata() {
    using (MySqlConnection conn = new MySqlConnection(connection))
    {
        string query = "SELECT * FROM studentdetails";
        MySqlDataAdapter da = new MySqlDataAdapter(query, conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        dtgrecords.DataSource = dt;
    }
}

Get the Selected Gender

Create a helper method that checks which gender radio button is selected.

private string Getgender()
{
    return rbMale.Checked ? "Male" : rbFemale.Checked ? "Female" : "";
}

Load Course Options into ComboBox

Pull unique course names from your tbl_course table and display them in the dropdown.

private void loadcourse()
{
    string query = "SELECT DISTINCT CourseName FROM tbl_course";
    using (MySqlConnection conn = new MySqlConnection(connection))
    {
        try
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            MySqlDataReader rd = cmd.ExecuteReader();

            cbcourse.SelectedIndex = -1;
            while (rd.Read())
            {
                cbcourse.Items.Add(rd["CourseName"].ToString());
            }
            rd.Close();
        }
        catch (MySqlException ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }
    }
}

Clear All Input Fields

This helper method resets the form fields.

private void clearfields() {
    tbFirstname.Clear();
    tbMiddlename.Clear();
    tbLastname.Clear();
    cbcourse.SelectedIndex = -1;
    dtpbday.Value = DateTime.Now;
    selectedstudentid = -1;
    rbFemale.Checked = false;
    rbMale.Checked = false;
}

Inside the constructor, add loaddata(); and loadcourse(); to load existing data from the database.

public Form1()
{
    InitializeComponent();
    loaddata();
    loadcourse();
}

Insert Button (btinsert)

In your Form1, double-click the Insert button (btinsert) on the design view to auto-generate the Click event handler in the Form1.cs file. Inside the auto-generated btinsert_Click method, write the logic to insert a new student record into the database.

private void btinsert_Click(object sender, EventArgs e)
{
    string query = "INSERT INTO studentdetails (Firstname, Middlename, Lastname, Gender, Bdate, Course) VALUES (@FIRSTNAME, @MIDDLENAME, @LASTNAME, @GENDER, @BDATE, @COURSE)";
    using (MySqlConnection conn = new MySqlConnection(connection))
    {
        try {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@FIRSTNAME", tbFirstname.Text);
            cmd.Parameters.AddWithValue("@MIDDLENAME", tbMiddlename.Text);
            cmd.Parameters.AddWithValue("@LASTNAME", tbLastname.Text);
            cmd.Parameters.AddWithValue("@GENDER", Getgender());
            cmd.Parameters.AddWithValue("@BDATE", dtpbday.Value);
            cmd.Parameters.AddWithValue("@COURSE", cbcourse.Text);

            int result = cmd.ExecuteNonQuery(); 
            if (result > 0 )
            {
                MessageBox.Show("Record Inserted Successfully", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information);
                clearfields();
            }
            else
            {
                MessageBox.Show("Error Inserting Record", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                clearfields();
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }
        loaddata();
    }
}

Clear Button (btclear)

In the design view, double-click the Clear button (btclear) to generate the Click event handler in Form1.cs. Inside the btclear_Click method, write the logic to reset all input fields.

private void btclear_Click(object sender, EventArgs e)
{
    clearfields();
}

Update Button (btupdate)

Double-click the Update button (btupdate) in the design view to auto-generate the Click event handler in Form1.cs. Inside the btupdate_Click method, write the logic to update a student's record in the database. Use an UPDATE SQL query that updates the student’s information based on the selectedstudentid. Before executing the query, check if a student is selected and display a message if not. Once the update is complete, show a confirmation message and refresh the data grid.

private void btupdate_Click(object sender, EventArgs e)
{
    string query = "UPDATE studentdetails SET Firstname = @FIRSTNAME, Middlename = @MIDDLENAME, Lastname = @LASTNAME, Gender = @GENDER, Bdate = @BDATE, Course = @COURSE WHERE StudentID = @STUDENTID";

    if (selectedstudentid == -1) 
    {
        MessageBox.Show("Please select a student to update", "Error Update", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        return;
    }

    using (MySqlConnection conn = new MySqlConnection(connection)) 
    {
        try
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@FIRSTNAME", tbFirstname.Text);
            cmd.Parameters.AddWithValue("@MIDDLENAME", tbMiddlename.Text);
            cmd.Parameters.AddWithValue("@LASTNAME", tbLastname.Text);
            cmd.Parameters.AddWithValue("@GENDER", Getgender());
            cmd.Parameters.AddWithValue("@BDATE", dtpbday.Value);
            cmd.Parameters.AddWithValue("@COURSE", cbcourse.Text);
            cmd.Parameters.AddWithValue("@STUDENTID", selectedstudentid);

            int result = cmd.ExecuteNonQuery();
            if (result > 0)
            {
                MessageBox.Show("Record Updated Successfully", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information);
                clearfields();
            }
            else
            {
                MessageBox.Show("Error Updating Record", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                clearfields();
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }
        loaddata();
    }
}

Delete Button (btdelete)

In the design view, double-click the Delete button (btdelete) to generate the Click event handler in Form1.cs. Inside the btdelete_Click method, write the logic to delete a student record from the database

private void btdelete_Click(object sender, EventArgs e)
{
    string query = "DELETE FROM studentdetails WHERE StudentId = @STUDENTID0";

    if (selectedstudentid == -1)
    {
        MessageBox.Show("Please select a student to delete");
        return;
    }

    DialogResult result = MessageBox.Show("Are you sure you want to delete this student?", "Confirm Delete", MessageBoxButtons.YesNo);
    if (result == DialogResult.Yes)
    {
        using (MySqlConnection conn = new MySqlConnection(connection))
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@STUDENTID0", selectedstudentid);
                conn.Open();
                cmd.ExecuteNonQuery();

                MessageBox.Show("Record Deleted Successfully");
                clearfields();
                selectedstudentid = -1;
                loaddata();
            }
            catch(MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
        }
    }
}

Search Bar (tbsearch)

In the design view, select the Search TextBox (tbsearch) and go to the events window. Double-click on the TextChanged event to auto-generate the tbsearch_TextChanged event handler in Form1.cs. Inside the tbsearch_TextChanged method, write the logic to filter the student records based on the search query entered.

private void tbsearch_TextChanged(object sender, EventArgs e)
{
    string query = "SELECT * FROM studentdetails WHERE CAST(StudentId as CHAR) LIKE @search OR Firstname LIKE @search OR Lastname LIKE @search";

    using (MySqlConnection conn = new MySqlConnection(connection))
    {
        try
        {
            MySqlDataAdapter da = new MySqlDataAdapter(query, conn);
            da.SelectCommand.Parameters.AddWithValue("@search", "%" + tbsearch.Text + "%");
            DataTable dt = new DataTable();
            da.Fill(dt);
            dtgrecords.DataSource = dt;
        } 
        catch (MySqlException ex) 
        { 
            MessageBox.Show("Error: " + ex.Message); 
        }
    }
}

DataGridView Cell Click (dtgrecords_CellClick)

In the design view, double-click the DataGridView (dtgrecords) to auto-generate the CellClick event handler in Form1.cs. Inside the dtgrecords_CellClick method, write the logic to retrieve the data from the selected row in the DataGridView and display it in the corresponding input fields.

private void dtgrecords_CellClick(object sender, DataGridViewCellEventArgs e)
{
    if (e.RowIndex >= 0 && dtgrecords.Rows[e.RowIndex].Cells[0].Value != null)
    {
        DataGridViewRow row = dtgrecords.Rows[e.RowIndex];

        tbFirstname.Text = row.Cells["Firstname"].Value?.ToString();
        tbMiddlename.Text = row.Cells["Middlename"].Value?.ToString();
        tbLastname.Text = row.Cells["Lastname"].Value?.ToString();

        selectedstudentid = Convert.ToInt32(row.Cells["StudentID"].Value);
        string gender = row.Cells["Gender"].Value?.ToString() ?? "";

        if (gender == "Male")
            rbMale.Checked = true;
        else if (gender == "Female")
            rbFemale.Checked = true;
        else
        {
            rbMale.Checked = false;
            rbFemale.Checked = false;
        }

        dtpbday.Value = Convert.ToDateTime(row.Cells["Bdate"].Value);
        cbcourse.Text = row.Cells["Course"].Value?.ToString();
    }
}

Output