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
MultiQuote
| 