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

By FoxLearn 12/3/2024 3:08:14 PM   20.7K
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.

access database c#

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

dataset c#

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