C# MS Access => ListView – INSERT SELECT UPDATE DELETE

C# MS Access ListView crud example. Lets see how to work with MS Access database today.We shall perform all the CRUD operations.First we insert data into the database from textboxes,then we retrieve the data from the MS Access database and bind it to our datagridview.We update our data with what the user has typed in textbox on button click.Finally we delete data from database on delete button click.

Source code

Here's the source code :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CS_ListView_Add_Update_Delete
{
  public partial class Form1 : Form
  {

    static string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Sedan/Documents/DataBases/peopleDB.mdb;";
    OleDbConnection con = new OleDbConnection(conString);
    OleDbCommand cmd;
    OleDbDataAdapter adapter;
    DataTable dt = new DataTable();

    public Form1()
    {
      InitializeComponent();

     //LISTVIEW PROPERTIES
      listView1.View = View.Details;
      listView1.FullRowSelect = true;

      //ADD COLUMNS
      listView1.Columns.Add("ID", 50);
      listView1.Columns.Add("Name", 150);
      listView1.Columns.Add("Position", 150);
      listView1.Columns.Add("Team", 150);

    }

   //INSERT INTO DB
    private void add(String name,string pos,string team)
    {
      //SQL STMT
      string sql = "INSERT INTO peopleTB(N,P,T) VALUES(@PNAME,@POSITION,@TEAM)";
      cmd = new OleDbCommand(sql, con);

      //ADD PARAMS
      cmd.Parameters.AddWithValue("@PNAME", name);
      cmd.Parameters.AddWithValue("@POSITION", pos);
      cmd.Parameters.AddWithValue("@TEAM", team);

      //OPEN CON AND EXEC
      try
      {
        con.Open();

        if(cmd.ExecuteNonQuery()>0)
        {
          clearTxts();
          MessageBox.Show("Successfully Inserted");

        }

        con.Close();

        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
      }

    }

    //FILL LISTVIUEW
    private void populate(String id,String name,String pos,String team)
    {
      //ROW
      String[] row = { id,name,pos,team};

      ListViewItem item = new ListViewItem(row);

      listView1.Items.Add(item);
    }

    //FETCH FROM DB
    private void retrieve()
    {
      listView1.Items.Clear();

      //SQL STMT
      string sql="SELECT * FROM peopleTB ";
      cmd = new OleDbCommand(sql, con);

      //OPEN CON,RETRIEVE,FILL LISTVIEW
      try
      {
        con.Open();
        adapter = new OleDbDataAdapter(cmd);

        adapter.Fill(dt);

        //LOOP THRU DT
        foreach(DataRow row in dt.Rows)
        {
          populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString());
        }

        con.Close();

        //CLEAR DT
        dt.Rows.Clear();
      }
      catch (Exception ex)
      {

        MessageBox.Show(ex.Message);
        con.Close();
      }
    }

    //UPDATE DB
    private void update(int id,String name,String pos,String team)
    {
      //SQL STMT
      string sql = "UPDATE peopleTB SET N='" + name + "',P='" + pos + "',T='" + team + "' WHERE ID=" + id + "";
      cmd = new OleDbCommand(sql, con);

      //OPEN CON,UPDATE,RETRIEVE LISTVIEW
      try
      {
        con.Open();
        adapter = new OleDbDataAdapter(cmd);

        adapter.UpdateCommand = con.CreateCommand();
        adapter.UpdateCommand.CommandText = sql;

        if(adapter.UpdateCommand.ExecuteNonQuery()>0)
        {
          clearTxts();
          MessageBox.Show("Successfully Updated");
        }

        con.Close();

        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }

    }

    //DELETE FROM DB
    private void delete(int id)
    {
      //SQL STMT

      string sql = "DELETE FROM peopleTB WHERE ID=" + id + "";
      cmd = new OleDbCommand(sql, con);

      //'OPEN CON,EXECUTE DELETE,CLOSE CON
      try
      {
        con.Open();
        adapter = new OleDbDataAdapter(cmd);

        adapter.DeleteCommand = con.CreateCommand();
        adapter.DeleteCommand.CommandText = sql;

        //PROMPT FOR CONFIRMATION
        if(MessageBox.Show("Sure ??","DELETE",MessageBoxButtons.OKCancel,MessageBoxIcon.Warning)==DialogResult.OK)
        {
          if(cmd.ExecuteNonQuery()>0)
          {
            clearTxts();
            MessageBox.Show("Successfully deleted");
          }
        }

        con.Close();

        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }

    }

    //CLEAR TXT
    private void clearTxts()
    {
      nameTxt.Text = "";
      posTxt.Text = "";
      teamtxt.Text = "";

    }

    private void listView1_MouseClick_1(object sender, MouseEventArgs e)
    {
      nameTxt.Text = listView1.SelectedItems[0].SubItems[1].Text;
      posTxt.Text = listView1.SelectedItems[0].SubItems[2].Text;
      teamtxt.Text = listView1.SelectedItems[0].SubItems[3].Text;
    }

    private void addBtn_Click_1(object sender, EventArgs e)
    {
      add(nameTxt.Text, posTxt.Text, teamtxt.Text);
    }

    private void retrieveBtn_Click_1(object sender, EventArgs e)
    {
      retrieve();
    }

    private void updateBtn_Click_1(object sender, EventArgs e)
    {
      String selected = listView1.SelectedItems[0].SubItems[0].Text;
      int id = Convert.ToInt32(selected);

      update(id, nameTxt.Text, posTxt.Text, teamtxt.Text);
    }

    private void deleteBtn_Click_1(object sender, EventArgs e)
    {
      String selected = listView1.SelectedItems[0].SubItems[0].Text;
      int id = Convert.ToInt32(selected);

      delete(id);
    }

    private void clearBtn_Click_1(object sender, EventArgs e)
    {
      listView1.Items.Clear();
      clearTxts();
    }
  }

}

Conclusion

We have seen how to select data from Microsoft Access Database and bind this data to our ListView.We used OleDbConnectiona to establish our connection.

Best Regards.