C# MS Access CRUD => DataGridView – INSERT,SELECT,UPDATE,DELETE

DataGridView is one of the most popular and powerful winform components out there. Not only can render textboxes but also checkboxes, images as well as combobox.

This makes it quite suitable to display database data and perform CRUD operations against that data. You can then sort, filter and search data.

However, today we are only concerned with one thing: perform CRUD operations with MS Access database. We see how to save/insert data into MS Access database, retrieve/select that data and display in datagridview, update/edit selected database data and persist changes and finally delete row by row.

Overview

The dataGridView component allows us display data in Grids that are very flexible. The class itself belongs to System.Windows.Forms namespace, meaning it’s a winform widget. 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 datagridview 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 datagridview.
  • How to update/edit ms access database via textbox and refresh datagridview changes.
  • How to dlete/remove ms access database data row by row in a datagridview.
Asssumptions.

We assume that you can drag a datagridview 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

MS Access Table

MS Access Table

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.

Source Code

Lets have a look at the source code.

Form1.cs

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using MetroFramework.Forms;

namespace DGView_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();
            //DATAGRIDVIEW PROPERTIES
            dataGridView1.ColumnCount = 4;
            dataGridView1.Columns[0].Name = "ID";
            dataGridView1.Columns[1].Name = "Name";
            dataGridView1.Columns[2].Name = "Propellant";
            dataGridView1.Columns[3].Name = "Destination";
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            //SELECTION MODE
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.MultiSelect = false;
        }

        /*
         * 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 DATAGRIDVIEW
         */
        private void populate(string id, string name, string propellant, string destination)
        {
            dataGridView1.Rows.Add(id, name, propellant, destination);
        }

        /*
         * RETRIEVAL OF DATA
         */
        private void retrieve()
        {
            dataGridView1.Rows.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)
                    {
                        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 = dataGridView1.SelectedRows[0].Index;
            if (selectedIndex != -1)
            {

                String selected = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                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 = dataGridView1.SelectedRows[0].Index;
            if (selectedIndex != -1)
            {
                String selected = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                int id = Convert.ToInt32(selected);
                delete(id);
            }

        }
        /*
         * CLEAR BUTTON CLICKED
         */
        private void clearBtn_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.Clear();
            clearTxts();
        }

        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                int selectedIndex = dataGridView1.SelectedRows[0].Index;
                if (selectedIndex != -1)
                {
                    if (dataGridView1.SelectedRows[0].Cells[0].Value != null)
                    {
                        string name = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                        string propellant = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                        string destination = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();

                        nameTxt.Text = name;
                        propellantTxt.Text = propellant;
                        destinationTxt.Text = destination;
                    }

                }
            }
            catch (ArgumentOutOfRangeException)
            {

            }

        }
    }
}

Download

Download the full project below: id=”4881″] Download

How To Run

  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 datagridview.

More

YouTube
  • Visit our channel for more examples like these.
Facebook

Cheers.

Share

1 Example

  1. Great and usefull article ! just need to upload the project into the solution and change the path of the .mdb 🙂 thanks !




Share an Example

Share an Example

Browse
What is the capital of Egypt? ( Cairo )