Insert, Update, Delete, Search In Asp.net using 3 tier Architecture

by Rakesh on June 23, 2014 · 0 comments

Hello Friends…

In This article write the step to how create Insert,Update,Delete & Search in Asp.net using  3 tier Architecture. In this we also use grid view.

Step : 1 -First Open New Web Project

Step : 2- Create Database in Microsoft Sql Server 2008 e.g Name:Demo

Step :3 – In Database Create First Table Named Dept_Master show in image

dept_masterstep :4 – Dept_Master Table Fill the Data

dept_master_dtstep :5- Create Second Table Name Emp_Master in this table use Foreign key

emp_masterstep :6 After that Create mycon.cs class write code bellow

public SqlConnection cn = new SqlConnection(“Data Source=RAKESH-PC;Initial Catalog=demo_3tier;Integrated Security=True”);

Step :7- Create EntityTest Class name entitytest.cs &? write code bellow

public class EntityTest
{
public int Emp_ID;
public string Emp_Name;
public int Salary;
public string City;
public int Dept_ID;

public int mEmp_ID
{
get
{
return Emp_ID;
}
set
{
Emp_ID = value;
}
}

public string mEmp_Name
{
get
{
return Emp_Name;
}
set
{
Emp_Name = value;
}
}
public int mSalary
{
get
{
return Salary;
}
set
{
Salary = value;
}
}
public string mCity
{
get
{
return City;
}
set
{
City = value;
}
}
public int mDept_ID
{
get
{
return Dept_ID;
}
set
{
Dept_ID = value;
}
}
}

Step – 8 Create DalTest class & write code bellow

public class DalTest
{
mycon con = new mycon();

public void Insert(string Emp_Name,int Salary,string City, int Dept_ID)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “SP_Insert_Emp_Master”;
cmd.Connection = con.cn;
cmd.Parameters.Add(“@Emp_Name”,Emp_Name);
cmd.Parameters.Add(“@Salary”, Salary);
cmd.Parameters.Add(“@City”, City);
cmd.Parameters.Add(“@Dept_ID”, Dept_ID);
con.cn.Open();
cmd.ExecuteNonQuery();
con.cn.Close();
}
public void Update(string Emp_Name, int Salary, string City, int Dept_ID, int Emp_ID)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “SP_Update_Emp_Master”;
cmd.Connection = con.cn;
cmd.Parameters.Add(“@Emp_ID”, Emp_ID);
cmd.Parameters.Add(“@Emp_Name”, Emp_Name);
cmd.Parameters.Add(“@Salary”, Salary);
cmd.Parameters.Add(“@City”, City);
cmd.Parameters.Add(“@Dept_ID”, Dept_ID);
con.cn.Open();
cmd.ExecuteNonQuery();
con.cn.Close();

}
public void delete(int emp_id)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “SP_Delete_Emp_Master”;
cmd.Connection = con.cn;
cmd.Parameters.Add(“@Emp_ID”,emp_id);
con.cn.Open();
cmd.ExecuteNonQuery();
con.cn.Close();
}
public void gridbind(string str,GridView gv)
{
con.cn.Open();
SqlDataAdapter da = new SqlDataAdapter(str,con.cn);
DataTable dt = new DataTable();
da.Fill(dt);
gv.DataSource = dt;
gv.DataBind();
con.cn.Close();
}
public void dept_id(string str, DropDownList dd)
{
con.cn.Open();
SqlCommand cmd = new SqlCommand(str,con.cn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
dd.Items.Add(dr[0].ToString());
}
}
public DataTable selecttable(string str)
{
SqlDataAdapter da = new SqlDataAdapter(str, con.cn);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}

 

Step 9- Design Page like bellow image

Step : 10 – Code of above page

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace WebApplication2
{
    public partial class Default : System.Web.UI.Page
    {
        mycon con = new mycon();
        Entity.EntityTest en = new Entity.EntityTest();
        Dal.DalTest d = new Dal.DalTest();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                d.gridbind("select * from Emp_Master", GridView1);
                d.dept_id("Select Dept_ID from Dept_Master", DropDownList1);
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            en.mEmp_Name = txtename.Text;
            en.mSalary = Convert.ToInt32(txtsal.Text);
            en.mCity = txtcity.Text;
            en.mDept_ID = Convert.ToInt32(DropDownList1.Text);

            if (Button1.Text == "Insert")
            {
                d.Insert(en.mEmp_Name, en.mSalary, en.mCity, en.mDept_ID);
                Response.Write("");
                txtename.Text = "";
                txtsal.Text = "";
                txtcity.Text = "";
                DropDownList1.Text = "-- Select --";

                d.gridbind("select * from Emp_Master", GridView1);
            }
            if (Button1.Text == "Update")
            {
                ViewState["Emp_ID"] = Convert.ToInt32(GridView1.SelectedDataKey.Value);
                en.mEmp_ID = Convert.ToInt32(ViewState["Emp_ID"]);
                d.Update(en.mEmp_Name,en.mSalary,en.mCity,en.mDept_ID,en.mEmp_ID);
                Response.Write("");
                txtename.Text = "";
                txtsal.Text = "";
                txtcity.Text = "";
                DropDownList1.Text = "-- Select --";
                Button1.Text = "Insert";
                d.gridbind("select * from Emp_Master", GridView1);
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            txtename.Text = "";
            txtsal.Text = "";
            txtcity.Text = "";
            DropDownList1.Text = "-- Select --";
            Button1.Text = "Insert";

        }

        protected void LinkButton1_Click(object sender, EventArgs e)
        {
            Button1.Text = "Update";
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ViewState["Emp_ID"] = Convert.ToInt32(GridView1.SelectedDataKey.Value);
            DataTable dt = d.selecttable("select * from Emp_Master where Emp_ID='" + ViewState["Emp_ID"] + "'");
            txtename.Text = dt.Rows[0][1].ToString();
            txtsal.Text = dt.Rows[0][2].ToString();
            txtcity.Text = dt.Rows[0][3].ToString();
            DropDownList1.Text = dt.Rows[0][4].ToString();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int emp_id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
            en.mEmp_ID = emp_id;
            d.delete(en.mEmp_ID);
            Response.Write("");
            d.gridbind("select * from Emp_Master",GridView1);
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            if (txtsearch.Text == "")
            {
                Response.Write("");
                txtsearch.Focus();
            }
            else
            {
                if (DropDownList2.Text == "--Select--")
                {
                    Response.Write("");
                    DropDownList2.Focus();
                }
                else
                {
                    if (DropDownList2.Text == "Emp_Name")
                    {
                        //con.cn.Open();
                        d.gridbind("select * from Emp_Master where Emp_Name Like '%" + txtsearch.Text + "%'", GridView1);

                    }
                    if (DropDownList2.Text == "City")
                    {
                        //con.cn.Open();
                        d.gridbind("select * from Emp_Master where City Like '%" + txtsearch.Text + "%'", GridView1);

                    }
                    if (DropDownList2.Text == "Dept_ID")
                    {
                        //con.cn.Open();
                        d.gridbind("select * from Emp_Master where Dept_ID Like '%" + txtsearch.Text + "%'", GridView1);

                    }
                }
            }
        }

    }

I hope this post is like you...:)

Leave a Comment

Previous post:

Next post:

Güncel Haberler