C# tutorial for beginners : How to create simple applications CRUD operation example Insert, Update, Delete in DataGridView C# with MySQL Database.
C# (Csharp) Tutorial for beginners - How to Edit, Update, Delete DataGridview in C# windows form? how to make CRUD example using MySQL database with ODBC connection in C# Windows Form programming languages? Today i will show you how to make simple applications using C# and MySQL Database and sure you can download full source code made from visual studio 2015.
What we needs to be prepared before you start making this application? before you must have a database (In this tutorial using MySQL database), make a connection to the database, so please read :
How to Create database with MySQL?
How to create connection using MySQL Database?
After design our Form1.cs, we will start write line by line our code to create simple CRUD operations MySQL Database, Double click on the Form1.cs and first, we will import ODBC namespaces to our Project.
Time to Debug your simple application, press "F5" and let me know what happening to our project with writing your comment on the Comment box bellow.
What we needs to be prepared before you start making this application? before you must have a database (In this tutorial using MySQL database), make a connection to the database, so please read :
How to Create database with MySQL?
How to create connection using MySQL Database?
Create CRUD Operations Project
Create new project and make name with "SimpleCrudCsharp", then at the form1.cs just design as needed look like this image :
After design our Form1.cs, we will start write line by line our code to create simple CRUD operations MySQL Database, Double click on the Form1.cs and first, we will import ODBC namespaces to our Project.
// we will create a connection //to our project using ODBC class using System.Data.Odbc;
Declaration Our Connection and new data in the bottom of Project Class
// its for our connection
public OdbcConnection connection = new OdbcConnection("DSN=java_db;MultipleActiveResultSets=True;");
// declaration for NewData
public Boolean NewData;
Bind Data Into DataGridView
private void LoadData() {
// create connection before
//open our connection
connection.Open();
// query using dataadapter into our database
OdbcDataAdapter da = new OdbcDataAdapter("SELECT * FROM biodata order by id",connection);
// we will using datatable to bing data into datagridview
DataTable dt = new DataTable();
da.Fill(dt);
// bind data into gridview
dataGridView1.DataSource = dt;
// close connections
connection.Close();
da.Dispose();
dt.Dispose();
}
Set TextBox Value from Datagridview Selected Value
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e) {
// value from datagrid where clicked cells is same in the textbox
DataGridViewRow rows = dataGridView1.Rows[e.RowIndex];
textBox1.Text = rows.Cells[0].Value.ToString();
textBox2.Text = rows.Cells[1].Value.ToString();
textBox3.Text = rows.Cells[2].Value.ToString();
textBox4.Text = rows.Cells[3].Value.ToString();
textBox5.Text = rows.Cells[4].Value.ToString();
// new data is false if textbox not null
NewData = false;
}
Clear TextBox
private void ClearText() {
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox2.Focus();
}
Function for Insert, Update and Delete Data
private void UpdateData(string sql) {
try {
// open connection
connection.Open();
// we will using OdbC command
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = connection;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
// execute
cmd.ExecuteNonQuery();
// show message if update data is success
MessageBox.Show("Data Hasbeen Updated!","Informations");
connection.Close();
cmd.Dispose();
} catch(Exception e) {
MessageBox.Show(e.ToString());
}
}
Source Code Start Up Project
private void Form1_Load(object sender, EventArgs e) {
// declaration for newdata is true
// if newData is true, we will Insert new data to database
// if newdata is false, so we will Update data into database while data is eksisting
NewData = true;
LoadData();
textBox1.Enabled = false;
}
Source Code New Button (Button1)
private void button1_Click(object sender, EventArgs e) {
// its method for add new data,
// we will declaration NewData to true if we want to add new data
// if newdata= false, so we will Update while eksisting data
NewData = true;
ClearText();
}
Source Code Save Button (Button 2)
// its method for save or update data into database
private void button2_Click(object sender, EventArgs e) {
DialogResult Message;
string SaveData = "";
// if Newdata is True, so we will create query "UPDATE"
// if newdata is False, we will create wuery "INSERT"
if(NewData == true) {
Message = MessageBox.Show("Are you sure to add new data into database?","Informations",MessageBoxButtons.YesNo);
if(Message == DialogResult.No) {
return;
} // SAVE DATA
SaveData = "INSERT INTO biodata(nama,nis,kelas,alamat)VALUES('"+ textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "')";
} else {
// UPDATE DATA
SaveData = "UPDATE biodata SET nama='"+ textBox2.Text + "',nis='" + textBox3.Text + "',kelas='" + textBox4.Text + "',alamat='" + textBox5.Text + "' WHERE id='"+ textBox1.Text +"'";
}
// call functions to update or insert new data
UpdateData(SaveData);
// load datagridview with new data
LoadData();
}
Source Code Delete Button (Button 3)
private void button3_Click(object sender, EventArgs e) {
DialogResult Message;
string delete = "";
Message = MessageBox.Show("Are you sure to delete this data?","Warning",MessageBoxButtons.YesNo);
if(Message == DialogResult.No) {
// if users klick "NO" dialog, will exit the method and do nothing
return;
} else {
// else, we will delete all data from selected id in TextBox1
delete = "DELETE from biodata WHERE id='"+ textBox1.Text +"'";
// call functions update data to execute the string query
UpdateData(delete);
LoadData();
}
}
Source Code Exit Button (Button 4)
private void button4_Click(object sender, EventArgs e) {
this.Close();
}
Complete Source Code CRUD Example Project
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
// we will create a connection
//to our project using ODBC class
using System.Data.Odbc;
namespace SimpleCsharpCRUD {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
// its for our connection
public OdbcConnection connection = new OdbcConnection("DSN=java_db;MultipleActiveResultSets=True;");
// declaration for NewData
public Boolean NewData;
private void Form1_Load(object sender, EventArgs e) {
// declaration for newdata is true
// if newData is true, we will Insert new data to database
// if newdata is false, so we will Update data into database while data is eksisting
NewData = true;
LoadData();
textBox1.Enabled = false;
}
// load data from a datatable
// you must have a database (MySQL Database) before.
// i was have a database in my localhost
// if you don't know how to create database? how to create connection with C#
// link available in descriptions
private void LoadData() {
// create connection before
//open our connection
connection.Open();
// query using dataadapter into our database
OdbcDataAdapter da = new OdbcDataAdapter("SELECT * FROM biodata order by id",connection);
// we will using datatable to bing data into datagridview
DataTable dt = new DataTable();
da.Fill(dt);
// bind data into gridview
dataGridView1.DataSource = dt;
// close connections
connection.Close();
da.Dispose();
dt.Dispose();
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e) {
// value from datagrid where clicked cells is same in the textbox
DataGridViewRow rows = dataGridView1.Rows[e.RowIndex];
textBox1.Text = rows.Cells[0].Value.ToString();
textBox2.Text = rows.Cells[1].Value.ToString();
textBox3.Text = rows.Cells[2].Value.ToString();
textBox4.Text = rows.Cells[3].Value.ToString();
textBox5.Text = rows.Cells[4].Value.ToString();
// new data is false if textbox not null
NewData = false;
}
private void ClearText() {
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox2.Focus();
}
private void button1_Click(object sender, EventArgs e) {
// its method for add new data,
// we will declaration NewData to true if we want to add new data
// if newdata= false, so we will Update while eksisting data
NewData = true;
ClearText();
}
// Functions for SAVE,UPDATE,DELETE data into database
private void UpdateData(string sql) {
try {
// open connection
connection.Open();
// we will using OdbC command
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = connection;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
// execute
cmd.ExecuteNonQuery();
// show message if update data is success
MessageBox.Show("Data Hasbeen Updated!","Informations");
connection.Close();
cmd.Dispose();
} catch(Exception e) {
MessageBox.Show(e.ToString());
}
}
// its method for save or update data into database
private void button2_Click(object sender, EventArgs e) {
DialogResult Message;
string SaveData = "";
// if Newdata is True, so we will create query "UPDATE"
// if newdata is False, we will create wuery "INSERT"
if(NewData == true) {
Message = MessageBox.Show("Are you sure to add new data into database?","Informations",MessageBoxButtons.YesNo);
if(Message == DialogResult.No) {
return;
} // SAVE DATA
SaveData = "INSERT INTO biodata(nama,nis,kelas,alamat)VALUES('"+ textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "')";
} else {
// UPDATE DATA
SaveData = "UPDATE biodata SET nama='"+ textBox2.Text + "',nis='" + textBox3.Text + "',kelas='" + textBox4.Text + "',alamat='" + textBox5.Text + "' WHERE id='"+ textBox1.Text +"'";
}
// call functions to update or insert new data
UpdateData(SaveData);
// load datagridview with new data
LoadData();
}
private void button3_Click(object sender, EventArgs e) {
DialogResult Message;
string delete = "";
Message = MessageBox.Show("Are you sure to delete this data?","Warning",MessageBoxButtons.YesNo);
if(Message == DialogResult.No) {
// if users klick "NO" dialog, will exit the method and do nothing
return;
} else {
// else, we will delete all data from selected id in TextBox1
delete = "DELETE from biodata WHERE id='"+ textBox1.Text +"'";
// call functions update data to execute the string query
UpdateData(delete);
LoadData();
}
}
private void button4_Click(object sender, EventArgs e) {
this.Close();
}
}
}
Video C# CRUD MySQL Database Tutorials
Time to Debug your simple application, press "F5" and let me know what happening to our project with writing your comment on the Comment box bellow.
COMMENTS