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
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
- Drag Drop a datagridview in your Form in visual studio.
- Copy the Form1 class above into your Form1.
- Change the connection string path.
- 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.
