C# MS Access + ListView – INSERT SELECT UPDATE CLEAR

C# MS Access + ListView – INSERT SELECT UPDATE CLEAR



Rating : 5/5 (2)




Introduction

C# ListView are quite powerful. They enable us display data in multiple columns in a very easy way. They are quite elegant too. When combined with a database program llike MS Access, it can give us a powerful way of displaying and manipulating our data. In this example, we look at how to perform CRUD operations with MS Access database and ListView as our component. Users can INSERT data to MS Access from TextBox. They can also RETRIEVE on button click. They can UPDATE existing rows in database.First they select the appropriate column to update. Finally they can DELETE the selected row from database.

Overview

The ListView component allows us display multi-column data in Lists that are very flexible. The class itself belongs to System.Windows.Forms namespace, meaning it's a winform component. Futhermore, it resides inside the System.Windows.Forms.dll assembly. It's a component that exists for C#, VB.NET, C++ and F#.

Questions this Examples helps answer.

  • How to use ListView component with ms access database?
  • How to insert/save data from textbox to ms access database.
  • How to select/retrieve ms access data to a ListView.
  • How to update/edit ms access database via textbox and refresh ListView changes.
  • How to delete/remove ms access database data row by row in a ListView.
  • How to set the selected ListView row to corresponding textboxes.
  • How to clear a ListView component on button click.

Asssumptions.

We assume that you can drag a ListView component in visual studio onto your winforms. Note that We used metro theme to theme our winforms. You don't have to do so. However, if you would want to install metro theme look at this link in youtube. For this project, simply extend the System.Windows.Form instead of MetroForm.
We also assume that you ms office access software installed in your machine and can create a database project in it with three 4 columns as shown below.

MS Database

Below is the overview of our MS Access database table.
MS Access Database

Database Name : spacecraftsDB
Database Table : spacecraftsTB

Columns: Below are our columns with database data types.
ID => Integer => Autoincrements
S_Name => Short Text
S_Propellant => Short Text
S_Destination Short Text

We have attached the MS Access database with the project, it's in the mdb format. Just copy it to some directory and then copy the path of the mdb file you'll use it in the connection string.

Screenshot

  • Here's the screenshot of the project.

ListView MS Access

Source Code

Lets have a look at the project's source code.

Form1.csVideo/DemoDownload
  • This is where we INSERT,SELECT,UPDATE,DELETE data to and from MS Access database.
using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using MetroFramework.Forms;
namespace ListView_Access
{
    public partial class Form1 : MetroForm
    {
        private const string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Hp/Documents/DataBases/spacecraftsDB.mdb;";
        readonly OleDbConnection con = new OleDbConnection(conString);
        OleDbCommand cmd;
        OleDbDataAdapter adapter;
        readonly DataTable dt = new DataTable();
        /*
       * CONSTRUCTOR
       */
        public Form1()
        {
            InitializeComponent();
            this.setupListView();
           
        }
        /*
         * SETUP LISTVIEW COLUMNS AND PRPERTIES
         */
        private void setupListView()
        {
            mListView.View = View.Details;
            mListView.FullRowSelect = true;
            mListView.MultiSelect = false;
            mListView.Columns.Add("ID", 30);
            mListView.Columns.Add("Name", 150);
            mListView.Columns.Add("Propellant", 150);
            mListView.Columns.Add("Destination", 150);
        }
        /*
         * INSERT INTO DB
         */
        private void add(string name, string propellant, string destination)
        {
            //SQL STMT
            const string sql = "INSERT INTO spacecraftsTB(S_Name,S_Propellant,S_Destination) VALUES(@NAME,@PROPELLANT,@DESTINATION)";
            cmd = new OleDbCommand(sql, con);
            //ADD PARAMS
            cmd.Parameters.AddWithValue("@NAME", name);
            cmd.Parameters.AddWithValue("@PROPELLANT", propellant);
            cmd.Parameters.AddWithValue("@DESTINATION", destination);
            //OPEN CON AND EXEC INSERT
            try
            {
                con.Open();
                if (cmd.ExecuteNonQuery() > 0)
                {
                    clearTxts();
                    MessageBox.Show(@"Successfully Inserted");
                }
                con.Close();
                retrieve();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }
        /*
         * FILL LISTVIEW
         */
        private void populate(string id, string name, string propellant, string destination)
        {
           //POPULATE SPACECRAFTS
             mListView.Items.Add(new ListViewItem(new[] { id,name,propellant,destination }));
        }
        /*
         * RETRIEVAL OF DATA
         */
        private void retrieve()
        {
            mListView.Items.Clear();
            //SQL STATEMENT
            String sql = "SELECT * FROM spacecraftsTB ";
            cmd = new OleDbCommand(sql, con);
            try
            {
                con.Open();
                adapter = new OleDbDataAdapter(cmd);
                adapter.Fill(dt);
                //LOOP THROUGH DATATABLE
                foreach (DataRow row in dt.Rows)
                {
                    populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString());
                }
                con.Close();
                //CLEAR DATATABLE 
                dt.Rows.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }
        /*
          *  UPDATE DATABASE
         */
        private void update(int id, string name, string propellant, string destination)
        {
            //SQL STATEMENT
            string sql = "UPDATE spacecraftsTB SET S_Name='" + name + "',S_Propellant='" + propellant + "',S_Destination='" + destination + "' WHERE ID=" + id + "";
            cmd = new OleDbCommand(sql, con);
            //OPEN CONNECTION,UPDATE,RETRIEVE DATAGRIDVIEW
            try
            {
                con.Open();
                adapter = new OleDbDataAdapter(cmd)
                {
                    UpdateCommand = con.CreateCommand()
                };
                adapter.UpdateCommand.CommandText = sql;
                if (adapter.UpdateCommand.ExecuteNonQuery() > 0)
                {
                    clearTxts();
                    MessageBox.Show(@"Successfully Updated");
                }
                con.Close();
                //REFRESH DATA
                retrieve();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }
        /*
         * DELETE FROM DATABASE
         */
        private void delete(int id)
        {
            //SQL STATEMENTT
            String sql = "DELETE FROM spacecraftsTB WHERE ID=" + id + "";
            cmd = new OleDbCommand(sql, con);
            //'OPEN CONNECTION,EXECUTE DELETE,CLOSE CONNECTION
            try
            {
                con.Open();
                adapter = new OleDbDataAdapter(cmd);
                adapter.DeleteCommand = con.CreateCommand();
                adapter.DeleteCommand.CommandText = sql;
                //PROMPT FOR CONFIRMATION BEFORE DELETING
                if (MessageBox.Show(@"Are you sure to permanently delete this?", @"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 TEXTBOXES
         */
        private void clearTxts()
        {
            nameTxt.Text = "";
            propellantTxt.Text = "";
            destinationTxt.Text = "";
        }
        /*
         * ADD BUTTON CLICKED
         */
        private void addBtn_Click(object sender, EventArgs e)
        {
            add(nameTxt.Text, propellantTxt.Text, destinationTxt.Text);
        }
        /*
       * RETRIEVE BUTTON CLICKED
       */
        private void retrieveBtn_Click(object sender, EventArgs e)
        {
            retrieve();
        }
        /*
         * UPDATE BUTTON CLICKED
         */
        private void updateBtn_Click(object sender, EventArgs e)
        {
            int selectedIndex = mListView.SelectedIndices[0];
            if (selectedIndex != -1)
            {
                String selected = mListView.SelectedItems[0].SubItems[0].Text;
                int id = Convert.ToInt32(selected);
                update(id, nameTxt.Text, propellantTxt.Text, destinationTxt.Text);
            }
        }
        /*
        * DELETE BUTTON CLICKED
        */
        private void deleteBtn_Click(object sender, EventArgs e)
        {
            int selectedIndex = mListView.SelectedIndices[0];
            if (selectedIndex != -1)
            {
                String selected = mListView.SelectedItems[0].SubItems[0].Text;
                int id = Convert.ToInt32(selected);
                delete(id);
            }
        }
        /*
         * CLEAR BUTTON CLICKED
         */
        private void clearBtn_Click(object sender, EventArgs e)
        {
            mListView.Items.Clear();
            clearTxts();
        }
        /*
         * LISTVIEW SELECTION CHANGED
         */
        private void mListView_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                int selectedIndex = mListView.SelectedIndices[0];
                if (selectedIndex != -1)
                {
                    if (mListView.SelectedItems[0].SubItems[0].Text != null)
                    {
                        string name = mListView.SelectedItems[0].SubItems[1].Text;
                        string propellant = mListView.SelectedItems[0].SubItems[2].Text;
                        string destination = mListView.SelectedItems[0].SubItems[3].Text;
                        
                        nameTxt.Text = name;
                        propellantTxt.Text = propellant;
                        destinationTxt.Text = destination;
                    }
                }
            }
            catch (ArgumentOutOfRangeException)
            {
            }
        }
    }
}

 

Below is the video tutorial for this project.

C# Examples S1E3 : MS Access ListView - INSERT SELECT UPDATE DELETE

Download the full project below:
Coming soon.

How To Run

  1. Download the source code above.
  2. Extract it.
  3. In your Visual Studio: File -> Open ->Project/Solution.
  4. Choose the Solution location.
  5. Open
  6. That's it, you've imported the project to your visual studio.

OR

  1. Drag Drop a datagridview in your Form in visual studio.
  2. Copy the Form1 class above into your Form1.
  3. Change the connection string path.
  4. Change database details.

Conclusion

We've seen how to insert, select, update and delete data to and from microsoft access database. Our component is ListView.

More

YouTube

  • Visit our channel for more examples like these.

Facebook

Oclemy,Cheers.



Rating :

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    6 + 4 =

    COMMENTS