How to Insert Update Delete View and Search data from MySQL in C#

In this article, we will walk through the steps to create a CRUD (Create, Read, Update, Delete) application using C# and MySQL.

We will build an application that manages customer data, which includes their full name, email, and address. The data will be stored in a MySQL database, and the application will allow the user to perform basic CRUD operations

How to Insert Update Delete View and Search data from MySQL in C#?

Open Visual Studio, then click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "CRUDMySql" and then click OK

Drag and drop the Label, Panel, Button, TextBox and DataGridView from the Visual Toolbox onto your form designer, then design your form as shown below.

Set up a MySQL database with a customers table.

CREATE TABLE customers (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    FullName VARCHAR(255),
    Email VARCHAR(255),
    Address TEXT
);

Next, create a DataSet,then add a customers table.

When the form is loaded, we retrieve the data from the customers table and bind it to the customersBindingSource.

private void Form1_Load(object sender, EventArgs e)
{
    // TODO: This line of code loads data into the 'appData.customers' table. You can move, or remove it, as needed.
    this.customersTableAdapter.Fill(this.appData.customers);
    customersBindingSource.DataSource = this.appData.customers;
}

The txtSearch_KeyPress method listens for a key press event. When the Enter key is pressed, it performs a search using LINQ based on the customer's full name, email, or address.

private void txtSearch_KeyPress(object sender, KeyPressEventArgs e)
{
    if (e.KeyChar == (char)13) // Enter key
    {
        if (string.IsNullOrEmpty(txtSearch.Text))
        {
            // Load all customer data if search box is empty
            this.customersTableAdapter.Fill(this.appData.customers);
            customersBindingSource.DataSource = this.appData.customers;
        }
        else
        {
            // Use LINQ to filter data based on search text
            var query = from o in this.appData.customers
                        where o.FullName.Contains(txtSearch.Text) || o.Email == txtSearch.Text || o.Address.Contains(txtSearch.Text)
                        select o;
            customersBindingSource.DataSource = query.ToList();
        }
    }
}

When the user clicks the "New" button, we enable the data entry panel and allow the user to input new data.

private void btnNew_Click(object sender, EventArgs e)
{
    try
    {
        // Enable data entry panel
        panel.Enabled = true;
        txtFullname.Focus(); // Set focus to Full Name textbox
        // Add a new row to the customers table
        this.appData.customers.AddcustomersRow(this.appData.customers.NewcustomersRow());
        customersBindingSource.MoveLast(); // Move to the new row
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Clicking the "Edit" button enables the panel for editing existing customer records.

private void btnEdit_Click(object sender, EventArgs e)
{
    panel.Enabled = true; // Enable the panel for editing
    txtFullname.Focus(); // Set focus to Full Name textbox
}

When the "Save" button is clicked, changes made to the customer data are saved to the MySQL database.

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        // End the edit and update the database
        customersBindingSource.EndEdit();
        customersTableAdapter.Update(this.appData.customers);
        panel.Enabled = false; // Disable the panel after saving
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Clicking the "Cancel" button will reset the bindings and disable the data entry panel.

private void btnCancel_Click(object sender, EventArgs e)
{
    // Disable data entry panel
    panel.Enabled = false;
    customersBindingSource.ResetBindings(false); // Reset data bindings
}

If the user presses the Delete key in the DataGridView, a confirmation dialog will appear, and if confirmed, the selected record will be removed from the list.

private void dataGridView_KeyDown(object sender, KeyEventArgs e)
{
    if (e.KeyCode == Keys.Delete)
    {
        if (MessageBox.Show("Are you sure want to delete this record ?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            customersBindingSource.RemoveCurrent(); // Remove the selected record
    }
}

This simple C# CRUD application provides a complete example of how to manage customer data using MySQL.

VIDEO TUTORIAL