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

This article demonstrates how to create a CRUD (Create, Read, Update, Delete) application using Windows Forms and Microsoft Access in C#.

How to Insert Update Delete View and Search data from MS Access 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 "CRUDMsAccess" and then click OK

Design your form as shown below.

Create an employee table, then create a dataset and add an employee table to your dataset as below

The Form1 constructor initializes the application. Upon form load, the application populates the DataGridView with data from the Access database:

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

Users can search employee records by typing into the search textbox. Pressing Enter triggers a LINQ query to filter data:

private void txtSearch_KeyPress(object sender, KeyPressEventArgs e)
{
    if (e.KeyChar == (char)13)
    {
        if (string.IsNullOrEmpty(txtSearch.Text))
        {
            //Fill data to datatable
            this.employeesTableAdapter.Fill(this.appData.Employees);
            employeesBindingSource.DataSource = this.appData.Employees;
            //dataGridView.DataSource = employeesBindingSource;
        }
        else
        {
            //using linq to query data
            var query = from o in this.appData.Employees
                        where o.FullName.Contains(txtSearch.Text) || o.PhoneNumber == txtSearch.Text || o.Email == txtSearch.Text || o.Address.Contains(txtSearch.Text)
                        select o;
            employeesBindingSource.DataSource = query.ToList();
            //dataGridView.DataSource = query.ToList();
        }
    }
}

The btnNew_Click event creates a new row in the dataset and prepares the panel for input:

private void btnNew_Click(object sender, EventArgs e)
{
    try
    {
        panel.Enabled = true;
        txtFullName.Focus();
        //Add new row
        this.appData.Employees.AddEmployeesRow(this.appData.Employees.NewEmployeesRow());
        employeesBindingSource.MoveLast();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
        employeesBindingSource.ResetBindings(false);
    }
}

The btnEdit_Click event allows editing existing records:

private void btnEdit_Click(object sender, EventArgs e)
{
    panel.Enabled = true;
    txtFullName.Focus();
}

The btnCancel_Click event discards any changes:

private void btnCancel_Click(object sender, EventArgs e)
{
    panel.Enabled = false;
    employeesBindingSource.ResetBindings(false);
}

The btnSave_Click event commits changes to the database:

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        employeesBindingSource.EndEdit();
        employeesTableAdapter.Update(this.appData.Employees);
        panel.Enabled = false;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
        employeesBindingSource.ResetBindings(false);
    }
}

Users can delete records using the Delete key. A confirmation dialog ensures accidental deletions are avoided:

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)
            employeesBindingSource.RemoveCurrent();
    }
}

The btnBrowse_Click event allows attaching an image to a record via an OpenFileDialog:

private void btnBrowse_Click(object sender, EventArgs e)
{
    try
    {
        using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "JPEG|*.jpg", ValidateNames = true, Multiselect = false })
        {
            if (ofd.ShowDialog() == DialogResult.OK)
                pictureBox.Image = Image.FromFile(ofd.FileName);//Load image from file to picturebox
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

This example demonstrates the implementation of essential CRUD operations in a Windows Forms application using MS Access.

VIDEO TUTORIAL