Insert, Update & Delete Table from DataGridView in C#.Net
Details:
Topic: Insert, Update & Delete Table from DataGridView
Language: C#.Net 2.0
Level: Intermediate
Today I will show you how to Insert, Update & Delete table with DataGridView in C#.Net.
Create a database EmployeesDB and create a table Employees
CREATE TABLE Employees ( EmployeeID numeric(9) IDENTITY (1, 1) PRIMARY KEY NOT NULL , LastName nvarchar(20)NOT NULL , FirstName nvarchar(10) NOT NULL , Title nvarchar(30) NULL , HireDate datetime NULL , PostalCode nvarchar(10) NULL ) GO
and then create a form in your solution add a DataGridView, name it dgvEmployee.
Our code starts here
global variable
We will use these variable when we are updating table.
SqlConnection sqlCon = new SqlConnection("Server=(local); Database=EmployeeDB; Integrated Security=TRUE"); SqlCommandBuilder sqlCommand = null; SqlDataAdapter sqlAdapter = null; DataSet dataset = null;
Load data to DataSet & DataGridView
Here we used SqlCommandBuilder to create SQL commands (Insert, Update, Delete) and assign to SqlDataAdapter.
I added another column in our sql select query for delete button
"SELECT *, 'Delete' AS [Delete] FROM Employees".
private void LoadData() { try { sqlAdapter = new SqlDataAdapter("SELECT *, 'Delete' AS [Delete] FROM Employees", sqlCon); sqlCommand = new SqlCommandBuilder(sqlAdapter); sqlAdapter.InsertCommand = sqlCommand.GetInsertCommand(); sqlAdapter.UpdateCommand = sqlCommand.GetUpdateCommand(); sqlAdapter.DeleteCommand = sqlCommand.GetDeleteCommand(); dataset = new DataSet(); sqlAdapter.Fill(dataset, "Employees"); dgvEmployee.DataSource = null; dgvEmployee.DataSource = dataset.Tables["Employees"]; for (int i = 0; i < dgvEmployee.Rows.Count; i++) { DataGridViewLinkCell linkCell = new DataGridViewLinkCell(); dgvEmployee[6, i] = linkCell; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
After loading data to DGV, run a loop and change Delete cell type to link.
for (int i = 0; i < dgvEmployee.Rows.Count; i++) { DataGridViewLinkCell linkCell = new DataGridViewLinkCell(); dgvEmployee[6, i] = linkCell; }
First we will add a new record to table
Add New Record
We will use UserAddedRow event when user added a new row in DGV.
private void dgvEmployee_UserAddedRow(object sender, DataGridViewRowEventArgs e) { try { int lastRow = dgvEmployee.Rows.Count - 2; DataGridViewRow nRow = dgvEmployee.Rows[lastRow]; DataGridViewLinkCell linkCell = new DataGridViewLinkCell(); dgvEmployee[6, lastRow] = linkCell; nRow.Cells["Delete"].Value = "Insert"; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
Now you can see the value of Delete column is Insert after clicking insert it will update the table and change column value to Delete]
And here is our delete and update code
In this we used to check if index of clicked cell is 6 or is it Delete column, if yes then it will check for the value Insert & Delete if it is Insert then new row will be add to dataset & DGV and update the table if it is Delete then delete command will excute after a confirmation.
Insert, Update & Delete Record
private void dgvEmployee_CellContentClick(object sender, DataGridViewCellEventArgs e) { try { if (e.ColumnIndex == 6) { string Task = dgvEmployee.Rows[e.RowIndex].Cells[6].Value.ToString(); if ( Task == "Delete") { if (MessageBox.Show("Are you sure to delete?", "Deleting...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { int rowIndex = e.RowIndex; dgvEmployee.Rows.RemoveAt(rowIndex); dataset.Tables["Employees"].Rows[rowIndex].Delete(); sqlAdapter.Update(dataset, "Employees"); } } else if(Task == "Insert") { int row = dgvEmployee.Rows.Count - 2; DataRow dr = dataset.Tables["Employees"].NewRow(); dr["LastName"] = dgvEmployee.Rows[row].Cells["LastName"].Value; dr["FirstName"] = dgvEmployee.Rows[row].Cells["FirstName"].Value; dr["Title"] = dgvEmployee.Rows[row].Cells["Title"].Value; dr["HireDate"] = dgvEmployee.Rows[row].Cells["HireDate"].Value; dr["PostalCode"] = dgvEmployee.Rows[row].Cells["PostalCode"].Value; dataset.Tables["Employees"].Rows.Add(dr); dataset.Tables["Employees"].Rows.RemoveAt(dataset.Tables["Employees"].Rows.Count -1); dgvEmployee.Rows.RemoveAt(dgvEmployee.Rows.Count - 2); dgvEmployee.Rows[e.RowIndex].Cells[6].Value = "Delete"; sqlAdapter.Update(dataset, "Employees"); } else if (Task == "Update") { int r = e.RowIndex; dataset.Tables["Employees"].Rows[r]["LastName"] = dgvEmployee.Rows[r].Cells["LastName"].Value; dataset.Tables["Employees"].Rows[r]["FirstName"] = dgvEmployee.Rows[r].Cells["FirstName"].Value; dataset.Tables["Employees"].Rows[r]["Title"] = dgvEmployee.Rows[r].Cells["Title"].Value; dataset.Tables["Employees"].Rows[r]["HireDate"] = dgvEmployee.Rows[r].Cells["HireDate"].Value; dataset.Tables["Employees"].Rows[r]["PostalCode"] = dgvEmployee.Rows[r].Cells["PostalCode"].Value; sqlAdapter.Update(dataset, "Employees"); dgvEmployee.Rows[e.RowIndex].Cells[6].Value = "Delete"; } } } catch (Exception ex) { }
Don't forget to open the connection before loading data.
private void Form1_Load(object sender, EventArgs e) { try { sqlCon.Open(); LoadData(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
Soon I will post one for vb.net
Thanks for reading...
This post has been edited by noorahmad: 09 July 2011 - 09:04 PM