CRUD Operations Using Three Tier in ASP.NET

In this blog you will learn how to perform CRUD Operations Using Three Tier in ASP.NET.

In three tier Architecture we have Business Layer,DataAccess Layer,Presentation Layer and a Schema which has properties to set values.

 

First open sql server and Create a table name Employee_Test Include following columns,

 

Id int not null primary key,

Name varchar(50),

Address varchar(50),

Age int

 

Then Create a Stored Procedure name Insert_User_Data, Following is the Stored Procedure:

Create PROCEDURE Insert_User_Data (
@Para varchar(50)= '',
@Id int = 0,
@Name varchar(50)= '',
@Address varchar(50)= '',
@Age int = 0
) AS BEGIN If @Para = 'ADD' Begin Insert into Employee_Test(Name, Address, Age)
values
(@Name, @Address, @Age);
END Else If @Para = 'Get_For_Grid' Begin
Select
*
from
Employee_Test END Else If @Para = 'Get_By_Id' Begin
Select
Name,
Address,
Age
from
Employee_Test
where
Id = @Id;
END Else If @Para = 'Update' Begin
Update
Employee_Test
Set
Name = @Name,
Address = @Address,
Age = @Age
where
Id = @Id;
END Else If @Para = 'Delete' Begin
Delete from
Employee_Test
where
Id = @Id;
END END

Go to Microsoft visual studio,then file-new project and select asp.net WebApplication Right click on solution in Solution Explorer and click add new project and in the windows tab Select class library and name it as EmployeeSchema and insert following code in it,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace EmployeeSchema {
    public class Class1 {
        private string _Name;
        private string _Address;
        private int _Age;
        public string Name {
            get {
                return _Name;
            }
            set {
                _Name = value;
            }
        }
        public string Address {
            get {
                return _Address;
            }
            set {
                _Address = value;
            }
        }
        public int Age {
            get {
                return _Age;
            }
            set {
                _Age = value;
            }
        }
    }
}

In the same way create one more class library and name it to EmployeeDAL and write following code to it.Add Reference of EmployeeSchema to EmployeeDAL. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using EmployeeSchema;
namespace EmployeeDAL {
    public class Class1 {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());
        SqlCommand cmd;
        DataTable dt;
        public int InsertData(EmployeeSchema.Class1 objSchema) {
            try {
                using(cmd = new SqlCommand("Insert_User_Data", con)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "ADD");
                    cmd.Parameters.AddWithValue("@Name", objSchema.Name);
                    cmd.Parameters.AddWithValue("@Address", objSchema.Address);
                    cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(objSchema.Age));
                    if (con.State.Equals(ConnectionState.Closed)) con.Open();
                    int result = cmd.ExecuteNonQuery();
                    con.Close();
                    return result;
                }
            } catch (Exception ex) {
                throw ex;
            } finally {
                con.Close();
            }
        }
        public int UpdateData(EmployeeSchema.Class1 objSchema, int Id) {
            try {
                using(cmd = new SqlCommand("Insert_User_Data", con)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "Update");
                    cmd.Parameters.AddWithValue("@Id", Id);
                    cmd.Parameters.AddWithValue("@Name", objSchema.Name);
                    cmd.Parameters.AddWithValue("@Address", objSchema.Address);
                    cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(objSchema.Age));
                    if (con.State.Equals(ConnectionState.Closed)) con.Open();
                    int result = cmd.ExecuteNonQuery();
                    con.Close();
                    return result;
                }
            } catch (Exception ex) {
                throw ex;
            } finally {
                con.Close();
            }
        }
        public int DeleteData(int Id) {
            try {
                using(cmd = new SqlCommand("Insert_User_Data", con)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "Delete");
                    cmd.Parameters.AddWithValue("@Id", Id);
                    if (con.State.Equals(ConnectionState.Closed)) con.Open();
                    int result = cmd.ExecuteNonQuery();
                    con.Close();
                    return result;
                }
            } catch (Exception ex) {
                throw ex;
            } finally {
                con.Close();
            }
        }
        public DataTable BindGrid() {
            using(cmd = new SqlCommand("Insert_User_Data", con)) {
                try {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "Get_For_Grid");
                    if (con.State.Equals(ConnectionState.Closed)) con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    da.Fill(dt);
                    con.Close();
                    return dt;
                } catch (Exception ex) {
                    throw ex;
                }
            }
        }
        public DataTable GetById(int Id) {
            using(cmd = new SqlCommand("Insert_User_Data", con)) {
                try {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "Get_By_Id");
                    cmd.Parameters.AddWithValue("@Id", Id);
                    if (con.State.Equals(ConnectionState.Closed)) con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    da.Fill(dt);
                    con.Close();
                    return dt;
                } catch (Exception ex) {
                    throw ex;
                }
            }
        }
    }
}

In the same way add another class library and name it to EmployeeBAL and add reference of EmployeeDAL to EmployeeBAL

and write following code in it,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EmployeeSchema;
using EmployeeDAL;
using System.Data;
namespace EmployeeBAL {
    public class Class1 {
        public int Insert(EmployeeSchema.Class1 objSchema) {
            try {
                EmployeeDAL.Class1 objDAL = new EmployeeDAL.Class1();
                return objDAL.InsertData(objSchema);
            } catch (Exception ex) {
                throw ex;
            }
        }
        public int Update(EmployeeSchema.Class1 objSchema, int Id) {
            try {
                EmployeeDAL.Class1 objDAL = new EmployeeDAL.Class1();
                return objDAL.UpdateData(objSchema, Id);
            } catch (Exception ex) {
                throw ex;
            }
        }
        public int Delete(int Id) {
            try {
                EmployeeDAL.Class1 objDAL = new EmployeeDAL.Class1();
                return objDAL.DeleteData(Id);
            } catch (Exception ex) {
                throw ex;
            }
        }
        public DataTable BindGrid() {
            try {
                EmployeeDAL.Class1 objDAL = new EmployeeDAL.Class1();
                return objDAL.BindGrid();
            } catch (Exception ex) {
                throw ex;
            }
        }
        public DataTable GetById(int Id) {
            try {
                EmployeeDAL.Class1 objDAL = new EmployeeDAL.Class1();
                return objDAL.GetById(Id);
            } catch (Exception ex) {
                throw ex;
            }
        }
    }
}

Now right click on project in solution explorer and  add a webform and name it to webform1.aspx.

On design side write this code: 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication14.WebForm2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html
    xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <table>
                    <tr>
                        <td>
                            <asp:Label ID="lblName" runat="server" Text="Enter Name"></asp:Label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <asp:Label ID="lblAge" runat="server" Text="Enter Age"></asp:Label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <asp:Label ID="lblAddress" runat="server" Text="Enter Address"></asp:Label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <asp:GridView ID="gvDisplay" runat="server" AutoGenerateColumns="false"
                                AllowPaging="true" PageSize="2"
                                onrowcommand="gvDisplay_RowCommand" onrowdeleting="gvDisplay_RowDeleting"
                                onselectedindexchanging="gvDisplay_SelectedIndexChanging"
                                onpageindexchanging="gvDisplay_PageIndexChanging">
                                <Columns>
                                    <asp:BoundField DataField="Id" HeaderText="Id" />
                                    <asp:BoundField DataField="Name" HeaderText="Name" />
                                    <asp:BoundField DataField="Address" HeaderText="Address" />
                                    <asp:BoundField DataField="Age" HeaderText="Age" />
                                    <asp:CommandField ButtonType="Button" SelectText="Edit" ShowSelectButton="True" />
                                    <asp:TemplateField>
                                        <ItemTemplate>
                                            <asp:Button runat="server" ID="btnDelete" OnClientClick="return confirm('Are you sure,you want to delete this record ?');" Text="Delete" CommandArgument='
                                                <%# Eval("Id") %>' CommandName="Delete" />
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                    </Columns>
                                </asp:GridView>
                            </td>
                        </tr>
                    </table>
                </div>
            </form>
        </body>
    </html>

In cs side write the following code,add reference of EmployeeDAL,EmployeeSchema and EmployeeBAL in the project.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using EmployeeDAL;
using EmployeeBAL;
using System.Data;
namespace WebApplication14 {
    public partial class WebForm2: System.Web.UI.Page {
        DataTable dt;
        protected void Page_Load(object sender, EventArgs e) {
            if (!Page.IsPostBack) {
                gvDisplay.Visible = false;
            }
        }
        protected void btnSubmit_Click(object sender, EventArgs e) {
            try {
                if (btnSubmit.Text == "Submit") {
                    InsertData();
                } else if (btnSubmit.Text == "Update") {
                    int Id = int.Parse(gvDisplay.Rows[gvDisplay.SelectedIndex].Cells[0].Text);
                    UpdateData(Id);
                }
            } catch (Exception ex) {
                throw ex;
            }
        }
        public void UpdateData(int Id) {
            EmployeeSchema.Class1 objSchema = new EmployeeSchema.Class1();
            objSchema.Name = txtName.Text;
            objSchema.Address = txtAddress.Text;
            objSchema.Age = Convert.ToInt32(txtAge.Text);
            EmployeeBAL.Class1 objBAL = new EmployeeBAL.Class1();
            int result = objBAL.Update(objSchema, Id);
            if (result > 0) {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Data Updated Successfully')", true);
            }
            btnSubmit.Text = "Submit";
            BindGrid();
            Clear();
        }
        public void InsertData() {
            EmployeeSchema.Class1 objSchema = new EmployeeSchema.Class1();
            objSchema.Name = txtName.Text;
            objSchema.Address = txtAddress.Text;
            objSchema.Age = Convert.ToInt32(txtAge.Text);
            EmployeeBAL.Class1 objBAL = new EmployeeBAL.Class1();
            int result = objBAL.Insert(objSchema);
            if (result > 0) {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Data Saved Successfully')", true);
            }
            BindGrid();
            Clear();
        }
        private void Clear() {
            txtName.Text = "";
            txtAge.Text = "";
            txtAddress.Text = "";
        }
        private void BindGrid() {
            try {
                EmployeeBAL.Class1 objBal = new EmployeeBAL.Class1();
                gvDisplay.Columns[0].Visible = true;
                gvDisplay.DataSource = objBal.BindGrid();
                gvDisplay.DataBind();
                gvDisplay.Columns[0].Visible = false;
                gvDisplay.Visible = true;
            } catch (Exception ex) {
                throw ex;
            }
        }
        protected void gvDisplay_SelectedIndexChanging(object sender, GridViewSelectEventArgs e) {
            try {
                EmployeeBAL.Class1 objBAL = new EmployeeBAL.Class1();
                int Id = int.Parse(gvDisplay.Rows[e.NewSelectedIndex].Cells[0].Text);
                dt = new DataTable();
                dt = objBAL.GetById(Id);
                if (dt.Rows.Count > 0) {
                    txtName.Text = dt.Rows[0]["Name"].ToString();
                    txtAddress.Text = dt.Rows[0]["Address"].ToString();
                    txtAge.Text = dt.Rows[0]["Age"].ToString();
                    btnSubmit.Text = "Update";
                }
            } catch (Exception ex) {
                throw ex;
            }
        }
        protected void gvDisplay_RowCommand(object sender, GridViewCommandEventArgs e) {
            if (e.CommandName.Equals("Delete")) {
                int Id = int.Parse(e.CommandArgument.ToString());
                DeleteRecord(Id);
            }
        }
        private void DeleteRecord(int Id) {
            try {
                EmployeeBAL.Class1 objBAL = new EmployeeBAL.Class1();
                int Result = objBAL.Delete(Id);
                if (Result > 0) {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Data Deleted Successfully')", true);
                }
                BindGrid();
                Clear();
            } catch (Exception ex) {
                throw ex;
            }
        }
        protected void gvDisplay_RowDeleting(object sender, GridViewDeleteEventArgs e) {}
        protected void gvDisplay_PageIndexChanging(object sender, GridViewPageEventArgs e) {
            gvDisplay.PageIndex = e.NewPageIndex;
            BindGrid();
        }
    }
}

Implement this.I will go on with more details in further blogs.Do not worry.