Page 1 of 1

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

#1 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Posted 09 July 2011 - 04:33 AM

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


Is This A Good Question/Topic? 1
  • +

Replies To: Insert, Update and Delete records in table with datagridview using C#

#2 Joe Klemmer  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • View blog
  • Posts: 15
  • Joined: 02-April 10

Posted 10 July 2011 - 10:24 AM

Quick question; For which DBMS does this tutorial use? MySQL, MSSQL, Access, SQLite? I'm guessing MSSQL but my WinXX isn't akll that strong (said the Linux guy).
Was This Post Helpful? 0
  • +
  • -

#3 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Posted 10 July 2011 - 08:37 PM

Form MS SQL Server!
Was This Post Helpful? 0
  • +
  • -

#4 poptcat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-July 11

Posted 05 August 2011 - 10:59 PM

Hi. I made a 3 steps tutorial - c# with oracle this time. Maybe someone will find it helpfull.

DataGridView programatically binded to Oracle data source

Updating Oracle table through OleDb

Basic Oracle C# application - insert,update,delete operations

The last article shows how to refresh datasource for DatagridView, how to retrieve primary key for a newly inserted row - things you may find hard on the net.

Should I make a new tutorial on this site with this samples?

This post has been edited by poptcat: 05 August 2011 - 11:07 PM

Was This Post Helpful? 0
  • +
  • -

#5 junharefa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 30-August 11

Posted 30 August 2011 - 08:10 PM

Quote

"SELECT *, 'Delete' AS [Delete] FROM Employees"


I'm still confuse with this code since you don't have create the "Delete" table in your database. what do you mean with the code. Pls tell me how your Windows Form look like using this code?

Thanks in advance.
Was This Post Helpful? 0
  • +
  • -

#6 ufrao98  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-September 11

Posted 11 September 2011 - 11:01 AM

First of all thanks for the great explanation.

But I'm not seeing the Update part on the code. Just the Insert part, which is:

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

I have an idea on how to do it, but I'm not quite sure in which event of the dgvEmployee should I put the code.

Thanks.
Was This Post Helpful? 0
  • +
  • -

#7 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 352
  • Joined: 22-September 10

Posted 19 September 2011 - 01:28 AM

it would be great to see this on relational database...
Was This Post Helpful? 0
  • +
  • -

#8 urwithmi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-September 10

Posted 18 February 2012 - 04:29 AM

Hi,

Nice explanation and easy enough to understand.

But I am facing a problem for update statements for this code.
sqlAdapter.Update() is not working when i want to update contents of a row.

I filled the dataGridView as select *,'Update' as [Update] from table_name ;

I created a very small test table Student.
Thus i have my datagridview as

+++++++++++++++++++++++++++++++++
studentId , studentName , Update


+++++++++++++++++++++++++++++++++

studentId is the primary key and auto increments.

The insert and delete code has worked fine for me. But for Update the changes were not reflected in the tables,
although the value in the dataset was changing.

Could anyone shed light on this.

The code :

if(Task=="Update")
{
  int r = e.RowIndex;
  dataset.Tables["Student"].Rows[r]["studentName"] =
  dataGridStudents.Rows[r].Cells["studentName"].Value;
  sqlAdapter.Update(dataset, "Student");
}



This code is getting called properly. However sqlAdapter.Update(dataset, "Student") returns 0 ;
Also I have tried populating studentId in the dataset along with the studentName yet the changes werent reflected.

Thanks
Was This Post Helpful? 0
  • +
  • -

#9 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Posted 02 March 2012 - 08:55 PM

when will the vb,net version b available

thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1