Insert, Delete, Update in DataGridView with DataTable in C#
Hi guys, there is a very simple way to insert, delete, update data in DataGridVifew with the help of using DataTable. Steps are given below…
Step 1:-
Make a table in the database
create table gridview
(
id intprimarykey,
name varchar(50)NOTNULL,
age intNOTNULL,
salary floatNOTNULL,
country varchar(50)NOTNULL,
city varchar(50)NOTNULL
)
Step 2:-
Take datagridview and button on a windows form and disable checkbox of Adding, Editing, Deleting, Column Reordering of DataGridview
Step 3:-
Add a new Class for global use of datatable and SqlAddapter ‘s objects
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace datagridview
{
class GlobalClass
{
public static SqlDataAdapter adap;
public static DataTable dt;
}
}
Step 4:- Write code on form’s cs file
using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace datagridview
{
public partial class DataGridView : Form
{
SqlConnection con;
SqlCommandBuilder bui;
string str;
int i;
public DataGridView()
{
InitializeComponent();
}
/// <summary>
/// gridfill method used for fill record in datagridview
/// </summary>
void gridfill()
{
string cnString = System.Configuration.ConfigurationManager.ConnectionStrings['dbconnection'].ConnectionString;
con= new SqlConnection(cnString);
GlobalClass.adap = new SqlDataAdapter('select * from gridview', con);
bui= new SqlCommandBuilder(GlobalClass.adap);
GlobalClass.dt = new DataTable();
GlobalClass.adap.Fill(GlobalClass.dt);
dataGridView1.DataSource= GlobalClass.dt;
dataGridView1.ReadOnly= true;
}
private void DataGridView_Load(object sender, EventArgs e)
{
try
{
gridfill();
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// selected rows index and values passed to OperationForm
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
str= dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString(); i = dataGridView1.Rows[e.RowIndex].Index;
Opereation op = new Opereation(str,i);
op.Show();
}
private void button1_Click(object sender, EventArgs e)
{
str= '';
Opereation op = new Opereation(str, i);
op.Show();
}
}
}
Step 5:- Make the second form for display, insertion, deletion and updating data into datagridview
Step 6:-
write code on form’s cs file
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace datagridview
{
public partial class Opereation : Form
{
string userid;
int rowId;
public Opereation(string id,int i)
{
InitializeComponent();
userid = id;
rowId = i;
}
/// <summary>
/// auto gererate column in datatable
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void Opereation_Load(object sender, EventArgs e)
{
int rowcount = GlobalClass.dt.Rows.Count;
if (GlobalClass.dt.Rows.Count == 0)
{
int set = 100;
txtId.Text = set.ToString();
btnUpdate.Visible = false;
btnInsert.Visible = true;
btnDelete.Enabled = false;
}
else if(userid == '')
{
int maxId = Convert.ToInt16(GlobalClass.dt.Compute('MAX(id)', string.Empty));
maxId = maxId + 1;
txtId.Text = maxId.ToString();
btnUpdate.Visible = false;
btnInsert.Visible = true;
btnDelete.Enabled = false;
}
else
{
try
{
txtId.Text = GlobalClass.dt.Rows[rowId]['id'].ToString();
txtName.Text = GlobalClass.dt.Rows[rowId]['name'].ToString();
txtAge.Text = GlobalClass.dt.Rows[rowId]['age'].ToString();
txtSalary.Text = GlobalClass.dt.Rows[rowId]['salary'].ToString();
txtCountry.Text = GlobalClass.dt.Rows[rowId]['country'].ToString();
txtCity.Text = GlobalClass.dt.Rows[rowId]['city'].ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
/// <summary>
/// delete record from datatable
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
GlobalClass.dt.Rows[rowId].Delete();
GlobalClass.adap.Update(GlobalClass.dt);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// update record in datatable
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
GlobalClass.dt.Rows[rowId]['name'] = txtName.Text.Trim();
GlobalClass.dt.Rows[rowId]['age'] = txtAge.Text.Trim();
GlobalClass.dt.Rows[rowId]['salary'] = txtSalary.Text.Trim();
GlobalClass.dt.Rows[rowId]['country'] = txtCountry.Text.Trim();
GlobalClass.dt.Rows[rowId]['city'] = txtCity.Text.Trim();
GlobalClass.adap.Update(GlobalClass.dt);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// Insert record in datatable
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void btnInsert_Click(object sender, EventArgs e)
{
try
{
if (txtId.Text.Trim() == '' || txtName.Text.Trim() == '' || txtAge.Text.Trim() == '' || txtSalary.Text.Trim() == '' || txtCountry.Text.Trim() == '' || txtCity.Text.Trim() == '')
{
MessageBox.Show('Please fill all the entry....');
}
else
{
DataRow dr;
dr = GlobalClass.dt.NewRow();
dr['id'] = txtId.Text.Trim();
dr['name'] = txtName.Text.Trim();
dr['salary'] = txtSalary.Text.Trim();
dr['age'] = txtAge.Text.Trim();
dr['country'] = txtCountry.Text.Trim();
dr['city'] = txtCity.Text.Trim();
GlobalClass.dt.Rows.Add(dr);
GlobalClass.adap.Update(GlobalClass.dt);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// Name Validatoin
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void txtName_KeyPress(object sender, KeyPressEventArgs e)
{
e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back || e.KeyChar == (char)Keys.Space);
}
/// <summary>
/// txtAge Validation
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void txtAge_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
e.Handled = !(char.IsNumber(e.KeyChar) || e.KeyChar == (char)Keys.Back);
}
/// <summary>
/// Salary Validatoin
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void txtSalary_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
e.Handled = !(char.IsNumber(e.KeyChar) || e.KeyChar == '.' || e.KeyChar == (char)Keys.Back);
}
/// <summary>
/// Country Validatoin
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void txtCountry_KeyPress(object sender, KeyPressEventArgs e)
{
e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back || e.KeyChar == (char)Keys.Space);
}
/// <summary>
/// city Validatoin
/// </summary>
/// <param name='sender'></param>
/// <param name='e'></param>
private void txtCity_KeyPress(object sender, KeyPressEventArgs e)
{
e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back || e.KeyChar == (char)Keys.Space);
}
}
}
Step 6:- Execute the program and click button ‘Add Record’ for adding a new record.
Step7:- Fill all appropriate record in text fields and then click button ‘Save’
Step 8:- And then double click on datagridview row for updating record and make some changes whatever you want for example, here you want to change name from “Avadhesh Patel” to “Avadhesh Singh” then you have to replace name from “Avadhesh Patel” to “Avadhesh Singh” in Name text field and after that click on button ‘Update’. Now updated records will visible in datagridview.
Step9:- Double click on datagridview row and press button ‘Delete’ to deleting data.
I hope this article might be helpful to you.
You should also read this Article - Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4
Jaya kumar
26-Aug-2015Chishambo Nzui
30-Jan-2015i.e GlobalClass.dt.Rows[0].Delete();
instead of
GlobalClass.dt.Rows[rowId].Delete();
Deepak Tyagi
29-Jan-2015AVADHESH PATEL
14-Jun-2013Virak Sim
13-Jun-2013Please help me out of this trouble.
Thank!
AVADHESH PATEL
01-Apr-2013Hi Horas!
try this line of code
// Get current date time
DateTime d = DateTime.Now;
// Changed MM/dd/yyyy format to dd/MM/yyyy fromat
string s = d.ToString('dd/MM/yyyy');
// Convert date time in string (s) to DateTime (Todate) data type
DateTime Todate = DateTime.Parse(s, CultureInfo.CreateSpecificCulture('en-IA'));
// Convert entered date string (TextBox1.Text) to DateTime (givenDate) Data Type
DateTime givenDate = DateTime.Parse(TextBox1.Text);
// Count total days
double days = Todate.Subtract(givenDate).Days;
// Convert days to year and display into Label.Text
txtAge.Text = Math.Floor(days / 365.24219).ToString();
Calculate Age
get more help from below link
Guest Post
Horas Panjaitan
31-Mar-2013want to getthe ageofthe following dates 06/04/1984 format date indonesia:(dd/MM/yyyy)
textAge.tex= here I
how tocode /syntakinthisasp.net
please help me.
Horas Panjaitan
31-Mar-2013want to getthe ageofthe following dates 06/04/1984 format date indonesia:(dd/MM/yyyy)
textAge.tex= here I
how tocode /syntakinthisasp.net
please help me.
Horas Panjaitan
22-Mar-2013god bless us
AVADHESH PATEL
15-Mar-2013AVADHESH PATEL
15-Mar-2013John fugio
10-Mar-2013I open fEmployee before Then open to fCustomer, back fEmployee is faulty.
when using the event DataGridView1_CellDoubleClick
Horas Panjaitan
08-Mar-2013This My database
Name database = dbbarang
Culum Name data type lenght
Kode_barang ntext 16
Nama_barang ntext 59
Harga numeric 9
after I clickbuttungetthe code,a messagesaying=if (String.IsNullOrEmpty(sId.ToString()))
this code
public string getcodeid()
{
object sId = null;
try
{
using (SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog=db; User Id =userid;Password=password"))
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select max(Id) from Employee";
cn.Open();
sId = cmd.ExecuteScalar();
}
}
}
catch { }
if (string.IsNullOrEmpty(sId.ToString()))
{
sId = "K001";
}
else
{
int id = Convert.ToInt32(sId.ToString().Substring(1)); this source error
id++;
if (id >= 1 && id <= 9)
sId = "K00" + id;
else if (id >= 10 && id <= 99)
sId = "K0" + id;
else if (id >= 100 && id <= 999)
sId = "K" + id;
}
return sId.ToString();
}
AVADHESH PATEL
08-Mar-2013Horas Panjaitan
06-Mar-2013The code Icobinasp.netwhydoes not work,I askyou pleasetoexplainit?
public string GetEmployeeId()
{
object sId = null;
try
{
using (SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog=db; User Id =userid;Password=password"))
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select max(Id) from Employee";
cn.Open();
sId = cmd.ExecuteScalar();
}
}
}
catch { }
if (string.IsNullOrEmpty(sId.ToString()))
{
sId = "K001";
}
else
{
int id = Convert.ToInt32(sId.ToString().Substring(1));
id++;
if (id >= 1 && id <= 9)
sId = "K00" + id;
else if (id >= 10 && id <= 99)
sId = "K0" + id;
else if (id >= 100 && id <= 999)
sId = "K" + id;
}
return sId.ToString();
}
Horas Panjaitan
22-Dec-2012god bless us all
Chris Anderson
21-Dec-2012you can do like this:
public string GetEmployeeId()
{
object sId = null;
try
{
using (SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog=db; User Id =userid;Password=password"))
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select max(Id) from Employee";
cn.Open();
sId = cmd.ExecuteScalar();
}
}
}
catch { }
if (string.IsNullOrEmpty(sId.ToString()))
{
sId = "K001";
}
else
{
int id = Convert.ToInt32(sId.ToString().Substring(1));
id++;
if (id >= 1 && id <= 9)
sId = "K00" + id;
else if (id >= 10 && id <= 99)
sId = "K0" + id;
else if (id >= 100 && id <= 999)
sId = "K" + id;
}
return sId.ToString();
}
after creating the above method, you can call and set the value whereever you want:
for eg. txtEmployeeId.Text = GetEmployeeId();
Horas Panjaitan
19-Dec-2012table student
nim varchar(10) noot null, Primary key Information nim = studentidentification number
name varchar(50) noot null,
class varchar (35) noot null,
table quiz
code_quiz varchar(10) noot null, Primary key
semester char(4) noot null,
nim varchar(10) noot null, FK
thank you in advance
AVADHESH PATEL
17-Dec-2012provide more information related your question with table structure!
Horas Panjaitan
14-Dec-2012How doI replacethe followingcode"1001" to"K001"
thanks
SqlCommand cmd = new SqlCommand("Select max(kode_barang) from tblbarang", myConnection);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string d = dr[0].ToString();
if (d == "")
{
txtkode.Text = "1001"; // this may code........<<<
}
else
{
r = Convert.ToInt32(dr[0].ToString());
r = r + 1;
txtkode.Text = r.ToString();
}
}
myConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
myConnection.Close();
}