Insert Update and Delete in Data through one Windows Form

Raj.Trivedi
Posted by in C# category on for Intermediate level | Points: 250 | Views : 47118 red flag
Rating: 5 out of 5  
 1 vote(s)

Hello Team,

Over here we will see how we can Insert Update and Delete Data in one Windows Form

Introduction

In real world application development we have need in inserting,updating and deleting data using one GUI.User's hate to go to different forms to manage data.

Over here we will see how we can manage data and do crud operations in one Form itself



Objective

  • Inserting,Updating data through the same Interface
  • Deleting the data directly from the data-grid.



Using the code


  1. Create a New Windows Application project in Visual Studio 2010.
  2. Drage and Drop 2 Textboxes and One Button and 1 DataGridView
  3. Name the button as btnSave
  4. Now click on the Arrow at the right corner of the gridview as shown in the screen




  1. Now click on Add Columns and add the following Column as shown in the figure below and click on Add
  2. Similarly add the Name column as well
  3. Note :- The name of the column should be same as that of the table in the database that too Case sensitive




  1. Now we will be adding edit and delete column in the Data Grid
  2. The process of adding the column is same as the above step but the type of the column will be of Image type check the Screen below
  3. Change the type from the drop down to Image type and click Add
  4. Same way add the delete column as well



  1. Now we will assign the Image to that column since we have set the type of the column to imagetype as seen in the screen above
  2. Once you click add you will again get the dialog box Edit column that select the Edit Column from the Left Side and go to the image property and click on it and browse the image and set the image for the column.Check the Screen below.





  1. Do it for the delete column as well.
  2. Now we will go to the code behind and write the code for Save and Update
  3. Insert and Update will be done on the same button.
  4. To Update the data we will click the edit column of the grid and the data related on that row will be shown in the text boxes above and the button text will change to Update.
  5. Once you change the data and click the button it will update the data.
  6. Once the Data is updated it the text box will become blank and the mode of saving the data will change to Insert not update.
  7. To distinguish when the data will be inserted or updated we will use a string known as Save Mode and if the Save Mode is = 0 then it will Insert the data.
  8. If the Save mode = 1 then the update query will fire.
  9. The Save mode will be set to 1 when you click the edit column in the Data Grid.

Check the Code for Code behind


Table and Stored Procedure for the article

Table : Crud

CREATE TABLE [dbo].[Crud](
[ID] [int] NULL,
[Names] [varchar](20) NULL
)


Stored Procedure

This is to Save and Update data

create proc [dbo].[INS_UPD_Crud]
(
@mode char(1),
@id int,
@Name varchar(20)
)
as
begin
if(@mode='0')
begin
insert into Crud(id,Names) values (@id,@Name) 
end
else if(@mode='1')
begin
update Crud set Names = @Name where id = @id
end
end



This proc is used to fill grid

create proc [dbo].[Getdata]
as
begin
select * from Crud
end



//This will fetch data when clicked on edit column in the application
create proc [dbo].[Getdatatotextbox]
(@id int)
as
begin
select * from Crud where ID = @id
end


// This will delete the data

create proc [dbo].[deleteCrud]
(
@id int
)
as
begin
delete from Crud where id = @id
end





// Code Behind
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.Data.SqlClient;

namespace Crud
{
    public partial class Form1 : Form
    {
       
        string GlobalMode = "0"; // This is the Mode to Passed as Parameter to SaveData function
        public Form1()
        {
            InitializeComponent();
        }


        // This event sets the mode for edit i.e. GlobalMode = 1 and also deletes the data by identifying the column name
        
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        { try
	    {
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
            if (gvDetails.Columns[e.ColumnIndex].Name == "Edit")
            {
                GetDataforUpdate(getdatatoTextbox(Convert.ToInt32(gvDetails.Rows[e.RowIndex].Cells[0].Value.ToString())));
                btnSave.Text = "UPDATE";
                GlobalMode = "1";
              
            }
            else if (gvDetails.Columns[e.ColumnIndex].Name == "Delete")
            {
                DialogResult result1 = MessageBox.Show("Are you sure you want to delete\nrecord of " + gvDetails.Rows[e.RowIndex].Cells[1].Value.ToString() + " ?", "Warning", MessageBoxButtons.YesNo);
                if (result1 == DialogResult.Yes)
                con.Open();
                SqlCommand sqlcmd = new SqlCommand("deleteCrud", con);
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = gvDetails.Rows[e.RowIndex].Cells[0].Value.ToString();
                int result = sqlcmd.ExecuteNonQuery();
                if (result == 1)
                {
                    MessageBox.Show("Record Deleted");
                    BindGrid();
                }
            }
            catch(Exception ex)
		{
		
		}
		
	finally
	{

		con.dispose();	
	
	}
        																																						}

        // This function gets the data into datatable 

        private void GetDataforUpdate(DataTable dtdata)
        {
            textBox1.Text = dtdata.Rows[0]["ID"].ToString();
            textBox2.Text = dtdata.Rows[0]["Names"].ToString();

        }


        // This function actually passes the data to textbox
        private DataTable getdatatoTextbox(int id)
        {
		
	try
	{	
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
            con.Open();
            SqlCommand sqlcmd = new SqlCommand("Getdatatotextbox", con);
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = id;
            DataTable dtdatanew = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
            da.Fill(dtdatanew);
            con.Close();
            return dtdatanew;
            
        }

	catch (exception ex)
	{

	}
	finally
	{

		con.dispose();	

	}
	}


        private void btnSave_Click(object sender, EventArgs e)
        {
            SaveData(GlobalMode);
            btnSave.Text = "Save";
            GlobalMode = "0";
            Clear();
        }

        // Save Function for Inserting and Updating Data

        private void SaveData(string InsertMode) // Insert Mode will check whether to Insert New Data or to Update
        {


	try

	{
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
            con.Open();
            SqlCommand sqlcmd = new SqlCommand("INS_UPD_Crud", con);
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.AddWithValue("@mode", SqlDbType.Char).Value = InsertMode; // This mode is checked in the stored procedure...If the mode is 0 then new data is inserted if mode is 1 the data is updated on the where clause of the id.
            sqlcmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = textBox1.Text.Trim();
            sqlcmd.Parameters.AddWithValue("@Name", SqlDbType.VarChar).Value = textBox2.Text.Trim();
            int result = sqlcmd.ExecuteNonQuery();
            if (result == 1)
            {
                MessageBox.Show("Data Saved");
                con.Close();
                BindGrid();
                con.Close();
               

            }
            else
            {
                MessageBox.Show("OOPS!!! Something went wrong");
            }
	}
	catch (exception ex)
	{

	}
	finally
	{
	
		con.dispose();

	}
           
        }



        private void BindGrid()
        {
		
	try
	{
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
            gvDetails.Rows.Clear();
            int Snu = 0;
            DataSet ds = new DataSet();
            con.Open();
            SqlCommand cmd = new SqlCommand("Getdata", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);

            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    gvDetails.Rows.Add();
                    gvDetails.Rows[Snu].Cells[0].Value = Convert.ToString(row["id"]);
                    gvDetails.Rows[Snu].Cells[1].Value = Convert.ToString(row["Names"]);
                    ++Snu;
                    con.Close();
                }
            }

	}

	catch(exception ex)
	{

	}

	finally
	{
		con.dispose();
	}	

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            BindGrid();
        }

        private void Clear()
        {
            textBox1.Text = "";
            textBox2.Text = "";
        }
    }
}  
Explanation for Inserting and Updating and deleting the data

Function Name:-  private void SaveData(string InsertMode)

  1. As the form Loads the Insert Mode is set to 0.
  2. This means that the data that you enter and when you save the data it will be a new entry because in the stored proc we have a if condition where it checks the mode.
  3. If the mode is 0 then it will execute the Insert Query.
  4. Now when we click on the edit button in the grid the insert mode is set to 1 and the id is captured and the data related to that id is fetched from the database in the data table and assigned to the text boxes and the button text is changed to Update.
  5. Now we can make changes to the data and save it.Over here it will not fire the insert query rather it will fire the update query as we have set the Insert Mode to 1 and in the Stored Procedure the mode = 1 is checked for the update Query.
  6. To delete the data we will click on the Delete button in the grid over here we will again capture the id and execute the delete SQL Command and the command type is stored procedure where we are passing the id as parameter to the Store Procedure.




Output Screen










Conclusion

Hope this will be useful........


Page copy protected against web site content infringement by Copyscape

About the Author

Raj.Trivedi
Full Name: Raj Trivedi
Member Level:
Member Status: Member,MVP
Member Since: 6/16/2012 2:04:41 AM
Country: India
Regard's Raj.Trivedi "Sharing is Caring" Please mark as answer if your Query is resolved
http://www.dotnetfunda.com/profile/raj.trivedi.aspx
Raj Trivedi i.e. me started my career as Support Professional and then moved on the Software development eventually reached at these skills Software Development | Enthusiastic Blogger | Content Writer | Technical Writer | Problem Solver | Lecturer on Technology Subjects | Runnerup Award Winner on www.dotnetfunda.com and firm believer in Sharing as a way of Caring Yet this much achieved its still a long way to go and there is biggest dream lying to be one of the best entrepreneurs of India in Technology Department. The Dream has just started and i hope it follows. Highlights are mentioned in details in my profile at http://in.linkedin.com/pub/raj-trivedi/30/61/b30/

Login to vote for this post.

Comments or Responses

Posted by: jnrprogrammer-22113 on: 5/8/2013 | Points: 25
Thanks man this is awesome..................Don't you have the same article as this one but for web applications not windows?

Login to post response

Comment using Facebook(Author doesn't get notification)