Insert, Update, Delete and Search Record by DataSet

Insert, Update, Delete and Search Record by DataSet

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.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
namespace dbTest
{
    public partial class Form1 : Form
    {
        SqlConnection con = new SqlConnection("Data source=PS-PC\\SANTOSH; Database=dbSantoshTest;  <br />Integrated Security=true");
      
        public Form1()
        {
            InitializeComponent();
        }


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

        //code for insert or save
        private void btnSave_Click(object sender, EventArgs e)
        {
            if (validate() == false)
            {
                return;
            }
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter;
                using (SqlCommand cmd = new SqlCommand("Select * from tblDemo", con))
                {
                    con.Open();
                    adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds, "tblDemo");
                    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
                    DataRow row = ds.Tables["tblDemo"].NewRow();
                    row["Name"] = txtName.Text.Trim();
                    row["Rollno"] = Convert.ToInt32(txtRollno.Text.Trim());
                    row["Branch"] = txtBranch.Text.Trim();
                    ds.Tables["tblDemo"].Rows.Add(row);

                    int Result = adapter.Update(ds, "tblDemo");
                    if (Result > 0)
                    {
                        MessageBox.Show("Record is inserted.");
                    }
                    else
                    {
                        MessageBox.Show("Record is not inserted.");
                    }
                }
            }

            catch (Exception exec)
            {
                MessageBox.Show("Internal problem. Try Again...");
            }
             finally
             {
                 con.Close();
                 Clear();
             }
           
            
        }

       

       //code for update.....
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (validate() == false)
            {
                return;
            }

            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter;
                using (SqlCommand cmd = new SqlCommand("select * from tblDemo", con))
                {
                    adapter = new SqlDataAdapter(cmd);
                    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
                    adapter.Fill(ds, "tblDemo");
                    con.Open();
                    foreach (DataRow row in ds.Tables["tblDemo"].Rows)
                    {
                        if (row["Rollno"].ToString() == (txtRollno.Text.Trim()))
                        {
                            row["Name"] = txtName.Text.Trim();
                            row["Branch"] = txtBranch.Text.Trim();
                        }

                    }

                    int result = adapter.Update(ds, "tblDemo");
                    if (result > 0)
                    {
                        MessageBox.Show("Record is updated.");
                    }
                    else
                    {
                        MessageBox.Show("Record is not updated.");
                    }
                }
            }
            catch (Exception exec)
            {
                MessageBox.Show("Internal problem...");
            }
              finally
              {
                  con.Close();
              }
                
        }

        //code for delete...
        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (txtRollno.Text == "")
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Roll number field should not be blank.";
                return;
            }

            try
            {

                DataSet ds = new DataSet();
                SqlDataAdapter adapter;
                using (SqlCommand cmd = new SqlCommand("select * from tbldemo", con))
                {
                    adapter = new SqlDataAdapter(cmd);
                    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);

                    adapter.Fill(ds, "tbldemo");
                    foreach (DataRow row in ds.Tables["tbldemo"].Rows)
                    {
                        if (row["Rollno"].ToString() == txtRollno.Text.Trim())
                        {
                            row.Delete();
                        }
                    }

                    int result = adapter.Update(ds, "tbldemo");
                    if (result > 0)
                    {
                        MessageBox.Show("Record is deleted...");
                    }
                    else
                    {
                        MessageBox.Show("Record is not deleted....");
                    }
                }
            }
            catch (Exception exec)
            {
                MessageBox.Show("Internal problem...Try Again....");

            }
             finally
             {
                 con.Close();
             }
        }


        //code for search
        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (txtRollno.Text == "")
            {
                lblMessage.Text = "Roll number field should not be blank.";
                return;
            }

            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter;
                using (SqlCommand cmd = new SqlCommand("select * from tbldemo", con))
                {
                    adapter = new SqlDataAdapter(cmd);
                    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);

                    DataTable td = new System.Data.DataTable();
                    con.Open();
                    adapter.Fill(ds, "tbldemo");
                    td = ds.Tables["tbldemo"];
                    DataRow[] row;
                    row = td.Select("Rollno=" + Convert.ToInt32(txtRollno.Text.Trim()));

                    int length = row.Length;
                    if (length > 0)
                    {
                        txtName.Text = row[0]["Name"].ToString();
                        txtRollno.Text = row[0]["Rollno"].ToString();
                        txtBranch.Text = row[0]["Branch"].ToString();
                    }
                    else
                    {
                        MessageBox.Show("Record is not found");
                    }
                }
            }

            catch
            {
                MessageBox.Show("internal problem....");
            }
             finally
             {
                 con.Close();
             }      
        }


        //code for clear text box fields
        private void btnClear_Click(object sender, EventArgs e)
        {
            Clear();
        }
        public void Clear()
        {
            txtName.Clear();
            txtBranch.Clear();
            txtRollno.Clear();
        }
     
    }
}

Now, run the application and add perform the operations.