Insert, Update, Delete in Access Database using C#

This article explains using Insert, Update and Delete statements in Access database.
First we display the Datatable Data in a Datagridview:

private void button1_Click(object sender, EventArgs e)
{
    if (con.State != ConnectionState.Open)
    {
        con.Open();
    }
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM Employee", con);
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    dataGridView1.DataSource = dt;
    dataGridView1.Columns["CodeEmployee"].DisplayIndex = 0;
    dataGridView1.Columns["Name"].DisplayIndex = 1;
    dataGridView1.Columns["BirthDate"].DisplayIndex = 2;
    dataGridView1.Columns["Profession"].DisplayIndex = 3; 
}

Then we will insert a new Employee in the table. In  AddEmployee button click event we add this code to display a new Form containing textBoxes to register Employee details:

private void button2_Click(object sender, EventArgs e)
{
    Form2 f2 = new Form2(this);
    f2.ShowDialog();
}

To enable displaying the added record in the Datagridview after closing Form2,  modify Form2 code by adding:

Form1 f1;

public Form2(Form1 frm1)
{
   InitializeComponent();
   this.f1 = frm1;
}

And in Add button Click event (in Form2) add this code:

private void AddButton_Click(object sender, EventArgs e)
{
    if (con.State != ConnectionState.Open)
    {
        con.Open();
    }
    OleDbCommand cmd = new OleDbCommand("INSERT INTO Employee (CodeEmployee,Name,BirthDate,Profession) VALUES (@CodeEmployee,@Name,@BirthDate,@Profession)", con);
    cmd.Parameters.AddWithValue("@CodeEmployee", codetextBox.Text);
    cmd.Parameters.AddWithValue("@Name", nametextBox.Text);
    cmd.Parameters.AddWithValue("@BirthDate",Convert.ToDateTime(dateTimePicker1.Text));
    cmd.Parameters.AddWithValue("@Profession",professiontextBox.Text);
    cmd.ExecuteNonQuery();
    MessageBox.Show("Employee added successefully");

    OleDbCommand refreshCmd = new OleDbCommand("SELECT * FROM Employee", con);
    DataTable dt = new DataTable();
    dt.Load(refreshCmd.ExecuteReader());
    f1.dataGridView1.DataSource = dt;
    f1.dataGridView1.DataSource = dt;
    f1.dataGridView1.Columns["CodeEmployee"].DisplayIndex = 0;
    f1.dataGridView1.Columns["Name"].DisplayIndex = 1;
    f1.dataGridView1.Columns["BirthDate"].DisplayIndex = 2;
    f1.dataGridView1.Columns["Profession"].DisplayIndex = 3;
            
    this.Close();
}

Now, we turn to Update and Delete statements. We added two DatagridviewImageColumns to the Datagridview, one for Update record and the second for Delete record.
In CellContentClick event of the Datagridview add this code:

For Delete statement:

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    switch (dataGridView1.Columns[e.ColumnIndex].Name)
    {
        case "delete":
             if (con.State != ConnectionState.Open)
             {
                 con.Open();
             }
             OleDbCommand deleteCmd = new OleDbCommand("delete from Employee where CodeEmployee = @codeEmp", con);
             deleteCmd.Parameters.AddWithValue("codeEmp", dataGridView1.CurrentRow.Cells["CodeEmployee"].Value.ToString());
             DialogResult result = MessageBox.Show("DELETE Employee?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
             if (result == DialogResult.Yes)
             {
                 deleteCmd.ExecuteNonQuery();
                 MessageBox.Show("Successeful delete");
             }

             OleDbCommand cmd2 = new OleDbCommand("SELECT * FROM Employee", con);
             DataTable dt = new DataTable();
             dt.Load(cmd2.ExecuteReader());
             dataGridView1.DataSource = dt;
             dataGridView1.Columns["CodeEmployee"].DisplayIndex = 0;
             dataGridView1.Columns["Name"].DisplayIndex = 1;
             dataGridView1.Columns["BirthDate"].DisplayIndex = 2;
             dataGridView1.Columns["Profession"].DisplayIndex = 3;

             break;
     }
}

And for Update statement add this code to CellContentClick to display the record details in Form2:

case "edit":
     Form2 f2 = new Form2(this);
                    
     if (con.State != ConnectionState.Open)
     {
          con.Open();
     }
     OleDbCommand cmd = new OleDbCommand("SELECT * FROM Employee WHERE CodeEmployee = @code", con);
     cmd.Parameters.AddWithValue("@code", dataGridView1.Rows[e.RowIndex].Cells["CodeEmployee"].Value.ToString());
     OleDbDataReader reader = cmd.ExecuteReader();
     if (reader.Read())
     {
          f2.codetextBox.Text = reader["CodeEmployee"].ToString();
          f2.nametextBox.Text = reader["Name"].ToString();
          f2.dateTimePicker1.Value = Convert.ToDateTime(reader["BirthDate"]);
          f2.professiontextBox.Text = reader["Profession"].ToString();
     }
     reader.Close();
     f2.ShowDialog();
     Break;

Then in Form2 Update button Click event add this code:

private void updateButton_Click(object sender, EventArgs e)
{
    if (con.State != ConnectionState.Open)
    {
        con.Open();
    }
    OleDbCommand updateCmd = new OleDbCommand("UPDATE Employee SET Name = @name, BirthDate=@date, Profession=@profession WHERE CodeEmployee = @CodeEmp", con);
    updateCmd.Parameters.AddWithValue("@code", codetextBox.Text);
    updateCmd.Parameters.AddWithValue("@name", nametextBox.Text);
    updateCmd.Parameters.AddWithValue("@date",Convert.ToDateTime(dateTimePicker1.Text));
    updateCmd.Parameters.AddWithValue("@profession", professiontextBox.Text);
    updateCmd.Parameters.AddWithValue("@CodeEmp", codetextBox.Text);
    updateCmd.ExecuteNonQuery();
    MessageBox.Show("Data updated successfully");
    this.Close();
}