Insert, Update & Delete Table from 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 valueInsert & 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); }
}

ADO.NET

http://www.codeproject.com/Articles/8477/Using-ADO-NET-for-beginners

http://www.csharp-station.com/Tutorial/AdoDotNet/lesson01

C#.NET Blogs and WebSites

http://nareshkamuni.blogspot.in/2012/04/how-to-insert-edit-update-and-delete.html

http://www.codeguru.com/csharp/.net/net_data/datagrid/article.php/c13041/Add-Edit-and-Delete-in-DataGridView.htm

http://social.msdn.microsoft.com/Forums/pl-PL/csharplanguage/thread/8db841fc-ffa7-4519-b6f5-d054c7190948

http://csharpdotnetfreak.blogspot.com/2009/02/ajax-cascading-dropdownlist-database.html

http://khanrahim.wordpress.com/2010/04/10/insert-update-delete-with-datagridview-control-in-c-windows-application/

http://visualstudiolearn.blogspot.in/2011/06/using-datatable-rowfilter-property-in-c.html

http://www.c-sharpcorner.com/uploadfile/ea36c5/combobox-datagridview-sample/

http://www.yevol.com/en/vcsharp/applicationdesign/Lesson27.htm

http://codespecs.blogspot.in/2011/12/insert-update-delete-table-from.html

http://demos.devexpress.com/ASPxGridViewDemos/Columns/CustomizationWindow.aspx

http://arsalantamiz.blogspot.in/2008/09/binding-datagridview-combobox-column.html

http://csharp.net-informations.com/datagridview/csharp-datagridview-printing.htm

http://www.codeproject.com/Articles/33786/DataGridView-Filter-Popup

http://www.codeproject.com/Articles/28046/Printing-of-DataGridView

http://www.codestructs.com/

http://niitdeveloper.blogspot.in/2010/12/update-database-from-datagridview.html
Mini Projects
http://programmingpalace.wordpress.com/2012/04/16/connecting-windows-forms-to-databasesql-server/

http://abhijitjana.net/

http://www.learnvisualstudio.net/lvs-001-landing-v1/

http://dailydotnettips.com/2010/12/31/calling-methods-from-watch-window/

How to Insert, Edit, Update and Delete Data with DataGridView in Windows Form C#.net ||Inserting , Updating and Deleting with DataGridView in Windows forms C#.net

In this article I am showing to Inserting , Editing , Updating and Deleting options with DataGridview.

For that I am Designing form with two textboxes with Name and Location ,DataGridview to display data and four buttons to Save , Edit , Update and Delete.

To do this just follow below steps:

·         In form load I am binding the data from database.

·         In save button click event saving data to database which are inserted into the name and location textboxes.

·         In Delete button click event  Deleting the selected row data in DataGridview from database.

·         In Edit button Click event filling the selected data from Gridview into Name and location textboxes.

·         In Update Button click event updating data which are edited the name and location textboxes.

Write the following code in Form.cs :

Form.cs Code :

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;

using System.Configuration;

using System.Data.SqlClient;

namespace savedata

{

    public partial class Form1 : Form

    {

        SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["Sqlcon"].ConnectionString);

        public Form1()

        {

            InitializeComponent();

            Bind();

        }

        private void Clear()

        {

            txtName.Text = string.Empty;

            txtLocation.Text = string.Empty;

        }

        private void btnSave_Click(object sender, EventArgs e)

        {

            con.Open();

            SqlCommand cmd = new SqlCommand("Insert Into Test_Data(Name,Location) Values (@Name,@Location)", con);

            cmd.Parameters.AddWithValue("Name", txtName.Text);

            cmd.Parameters.AddWithValue("Location", txtLocation.Text);

            cmd.ExecuteNonQuery();

            con.Close();

            MessageBox.Show("Inserted sucessfully");

            Bind();

            Clear();

        }

        private void Bind()

        {

            con.Open();

            SqlDataAdapter da = new SqlDataAdapter("select * from Test_Data", con);

            DataTable dt = new DataTable();

            da.Fill(dt);

            dataGridView1.DataSource = dt;

            con.Close();

        }

        private void btnDelete_Click(object sender, EventArgs e)

        {

            SqlCommand delcmd = new SqlCommand();

            if (dataGridView1.Rows.Count > 1 && dataGridView1.SelectedRows[0].Index != dataGridView1.Rows.Count - 1)

            {

                delcmd.CommandText = "DELETE FROM Test_Data WHERE ID=" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";

                con.Open();

                delcmd.Connection = con;

                delcmd.ExecuteNonQuery();

                con.Close();

                dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);

                MessageBox.Show("Row Deleted");

            }

            Bind();

        }

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            con.Open();

            SqlCommand cmd = new SqlCommand("Update Test_Data set Name=@Name,Location=@Location Where(Name=@Name)", con);

            cmd.Parameters.AddWithValue("@Name", txtName.Text);

            cmd.Parameters.AddWithValue("@Location", txtLocation.Text);

            cmd.ExecuteNonQuery();

            MessageBox.Show("updated......");

            con.Close();

            Bind();

            Clear();

        }

        private void btnEdit_Click_1(object sender, EventArgs e)

        {

            int i;

            i = dataGridView1.SelectedCells[0].RowIndex;

            txtName.Text = dataGridView1.Rows[i].Cells[1].Value.ToString();

            txtLocation.Text = dataGridView1.Rows[i].Cells[2].Value.ToString();

        }

    }

}

Then run the application you will get output like below: