Insert, Update, Delete and Search Record by SqlDataReader

Insert, Update, Delete and Search Record by SqlDataReader

First of all we create following design in Window Form Application as shown in below and give proper control name. We add three textbox and named are txtName, txtRollno and txtBranch. We added four buttons named are-->> btnSave, btnUpdate, btnDelete, btnSearch, btnClear and one label named it lblMessage:-



Now, we are using SQL SERVER database, so we need to create table named tblDemo as following code:-

  

CREATE DATABASE dbSantoshTest

USE dbSantoshTest

CREATE TABLE tblDemo
(
 Name varchar(50),
 Rollno int NOT NULL,
 Branch varchar(30)
)

Now, Go to code behind form or click on button and write the following code for Insert, Update, Delete and Search:-

  

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 dbTest
{
    public partial class Form2 : Form
    {
        string connectionString = "Data source=PS-PC\\SANTOSH; Database=dbSantoshTest; Integrated Security=true";
        public Form2()
        {
            InitializeComponent();
        }

        #region "Funtion"

        private bool validate()
        {
            lblMessage.ForeColor = Color.Red;
            if (txtName.Text == "")
            {
                lblMessage.Text = "Name field should not be blank.";
                return false;
            }

            if (txtRollno.Text == "")
            {
                lblMessage.Text = "Roll number field should not be blank.";
                return false;
            }

            if (txtBranch.Text == "")
            {
                lblMessage.Text = "Branch field should not be blank.";
                return false;
            }

            return true;
        }

        private void Clear()
        {
            txtName.Clear();
            txtRollno.Clear();
            txtBranch.Clear();
            
        }


#endregion


        //Insert code here
        private void btnSave_Click(object sender, EventArgs e)
        {
            if (validate() == false)
            {
                return;
            }

            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("Insert into tblDemo values(@Name,@Rollno,@Branch)",con);
                    cmd.Parameters.AddWithValue("@Name", txtName.Text);
                    cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.Text));
                    cmd.Parameters.AddWithValue("@Branch", txtBranch.Text);
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }

                    int Result = cmd.ExecuteNonQuery();
                    if (Result > 0)
                    {
                        lblMessage.ForeColor = Color.Green;
                        lblMessage.Text = "Record has been inserted.";
                    }
                    else
                    {
                        lblMessage.ForeColor = Color.Red;
                        lblMessage.Text = "Record has not been inserted.";
                    }
                }

            }
            catch
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Input data is not in correct format.";

            }
            Clear();
        }


        //Update code here
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                if (validate() == false)
                {                  
                    return;
                }
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("Update tblDemo set Name=@Name, Branch=@Branch where Rollno=@Rollno", con);
                    cmd.Parameters.AddWithValue("@Name", txtName.Text);
                    cmd.Parameters.AddWithValue("@Rollno",Convert.ToInt32(txtRollno.Text));
                    cmd.Parameters.AddWithValue("@Branch", txtBranch.Text);
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }

                    int Result = cmd.ExecuteNonQuery();
                    if (Result > 0)
                    {
                        lblMessage.ForeColor = Color.Green;
                        lblMessage.Text = "Record has been updated.";
                    }
                    else
                    {
                        lblMessage.ForeColor = Color.Red;
                        lblMessage.Text = "Record has not been updated.";
                    }
                }
            }
            catch
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Input data is not in correct format.";
            }
            Clear();
        }


        //Delete code here
        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtRollno.Text=="")
                {
                    lblMessage.ForeColor = Color.Red;
                    lblMessage.Text = "Roll number field should not be blank.";
                    return;
                }
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("Delete from tblDemo where Rollno=@Rollno", con);             
                    cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.Text));               
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }

                    int Result = cmd.ExecuteNonQuery();
                    if (Result > 0)
                    {
                        lblMessage.ForeColor = Color.Green;
                        lblMessage.Text = "Record has been deleted.";
                    }
                    else
                    {
                        lblMessage.ForeColor = Color.Red;
                        lblMessage.Text = "Record has not been deleted.";
                    }
                }
            }
            catch
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Input data is not in correct format.";
            }
            Clear();
        }


        //Search code here
        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtRollno.Text == "")
                {
                    lblMessage.ForeColor = Color.Red;
                    lblMessage.Text = "Roll number field should not be blank.";
                    return;
                }
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("Select * from tblDemo where Rollno=@Rollno", con);
                    cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.Text));
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }

                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        txtName.Text = reader["Name"].ToString();
                        txtBranch.Text = reader["Branch"].ToString();
                       
                    }
                    else
                    {
                        lblMessage.ForeColor = Color.Red;
                        lblMessage.Text = "Record has not been founded.";
                    }
                }
            }
            catch
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Input data is not in correct format.";
            }
            
        }

        //Clear code here
        private void btnClear_Click(object sender, EventArgs e)
        {
            Clear();
        }
    }
}

Now, Run the application and perform Insert, Update, Delete and Search.