Insert, Update & Delete Table from DataGridView in C#.Net

See more:

C#2.0

C#

C#3.5

C#5.0

i am having problem with this code here the update command is not working i google this code and it is not given completely please help me with this

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.

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); }
}

here what i m getting the problem is the update thing not working and im really confused really need help with this code