Subscribe to Recent Solutions        RSS Feed
-----

Insert, Update and Delete records in table with datagridview using C#.net

Icon Leave Comment
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...

0 Comments On This Entry

 

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

Search My Blog

0 user(s) viewing

0 Guests
0 member(s)
0 anonymous member(s)