CRUD Operation

The Definition of CRUD

                              The acronym CRUD stands for create, read, update and delete.

• CREATE procedures: Performs the INSERT statement to create a new record.
• READ procedures: Reads the table records based on the primary keynoted within the input parameter.
• UPDATE procedures: Executes an UPDATE statement on the table based on the specified primary key for a record within the WHERE clause of the statement.
• DELETE procedures: Deletes a specified row in the WHERE clause.

Here, I am using SQL database to insert, update and delete operation. Before starting, you should add DLL and afterwards, you should add namespace under it.

STEP 1

Create a database Newdb and name Table as CityInfo.

STEP 2 


             Design your Form.

STEP 3


Context Menu Strip

 

                      

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CrudOperation
{
    public partial class Form1 : Form
    {
//Declare Connection String

        string conStr = @"Data Source=. ; Initial Catalog=Newdb; Integrated Security=true";
        SqlConnection con;

        private DataTable CountryTable()
        {
            DataTable dt = new DataTable();
            string query = @"SELECT id AS [Serial No],CountryName As [Country Name] FROM cityinfo";
            SqlCommand cmd = new SqlCommand(query,con);
            con.Close();
            con.Open();
            dt.Load(cmd.ExecuteReader());
            con.Close();
            return dt;          
        }

        void DisplayRecord()
        {
            DataTable dt = CountryTable();
            dataGridView1.DataSource = dt;
            textBoxSrNo.Text = (dt.Rows.Count + 1).ToString();
        }

        public Form1()
        {
            InitializeComponent();
            con = new SqlConnection(conStr);
        }

        private void BtnAdd_Click(object sender, EventArgs e)
        {
            string query = @"INSERT INTO cityinfo VALUES ('"+textBoxCountry.Text+"')";
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            ResetId();
            textBoxCountry.Clear();
        }
//AutoIncrement Sr. No
        void ResetId()
        {
            string query1 = @"ALTER TABLE [cityinfo] DROP COLUMN id";
            string query2 = @"ALTER TABLE [cityinfo] Add id int identity";
            SqlCommand cmd1 = new SqlCommand(query1, con);
            SqlCommand cmd2 = new SqlCommand(query2, con);
            con.Close();
            con.Open();
            cmd1.ExecuteNonQuery();
            cmd2.ExecuteNonQuery();
            con.Close();
            DisplayRecord();
        }

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

        private void BtnClear_Click(object sender, EventArgs e)
        {
            textBoxCountry.Clear();
        }

        private void DeleteToolStripMenuItem_Click(object sender, EventArgs e)
        {

            string query = @"DELETE FROM cityinfo WHERE id='"+dataGridView1.SelectedRows[0].Cells[0].Value.ToString()+"'";
            SqlCommand cmd = new SqlCommand(query,con);
            con.Close();
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            ResetId();
        }

        private void BtnUpdate_Click(object sender, EventArgs e)
        {
            string query = @"UPDATE cityinfo SET Countryname='"+textBoxCountry.Text+"' WHERE id='" + textBoxSrNo.Text + "' ";
            SqlCommand cmd = new SqlCommand(query, con);
            con.Close();
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            ResetId();
            btnUpdate.Visible = false;
        }


        private void EditToolStripMenuItem_Click(object sender, EventArgs e)
        {
            textBoxSrNo.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
            textBoxCountry.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
            btnUpdate.Visible = true;
        }

       
    }

}