Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_4_Tier_Default" %>
<!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"><style type="text/css">body{font-family: Arial, Trebuchet Ms;font-size: 10pt;}</style><title>Insert Records into DataBase</title></head><body><h3>Demo: 4-Tier Architecture</h3><form id="form1" runat="server"><div><p><a href="List.aspx">List Records</a></p><asp:Label ID="lblMessage" runat="Server" ForeColor="red" EnableViewState="False"></asp:Label><table style="border: 2px solid #cccccc;"><tr style="background-color: #507CD1; color: White;"><th colspan="3">Add Records</th></tr><tr><td>First Name:</td><td><asp:TextBox ID="txtFirstName" runat="Server"></asp:TextBox></td><td><asp:RequiredFieldValidator ID="req1" runat="Server" Text="*" ControlToValidate="txtFirstName"Display="dynamic"></asp:RequiredFieldValidator></td></tr><tr><td>Last Name:</td><td><asp:TextBox ID="txtLastName" runat="Server"></asp:TextBox></td><td><asp:RequiredFieldValidator ID="req2" runat="Server" Text="*" ControlToValidate="txtLastName"Display="dynamic"></asp:RequiredFieldValidator></td></tr><tr><td>Age:</td><td><asp:TextBox ID="txtAge" runat="Server" Columns="4"></asp:TextBox></td><td><asp:RequiredFieldValidator ID="req3" runat="Server" Text="*" ControlToValidate="txtAge"Display="dynamic"></asp:RequiredFieldValidator><asp:CompareValidator ID="Comp1" runat="Server" Text="Only integer" ControlToValidate="txtAge"Operator="DataTypeCheck" Type="Integer"></asp:CompareValidator></td></tr><tr><td> </td><td><asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="AddRecords" /></td></tr></table><table><tr><td><asp:GridView ID="GridView1" runat="server" PageSize="10" AutoGenerateColumns="False"OnSelectedIndexChanging="GridView1_SelectedIndexChanging" AllowPaging="True"SkinID="ProfessionalGV" Width="700px" Visible="true"><Columns><asp:TemplateField HeaderText="#"><ItemTemplate><%# Container.DataItemIndex + 1 %></ItemTemplate></asp:TemplateField><asp:BoundField DataField="FirstName" HeaderText="FirstName" /><asp:BoundField DataField="LastName" HeaderText="LastName" /><asp:BoundField DataField="Age" HeaderText="Age" /><asp:TemplateField HeaderText="PersonID" Visible="False"><ItemTemplate><asp:Label ID="lblPersonID" runat="server" Text='<%# Bind("PersonID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CommandField SelectText="Edit" ShowSelectButton="True" /></Columns></asp:GridView><asp:HiddenField ID="hdnPersonID" runat="server"></asp:HiddenField></td></tr></table></div></form></body></html>
Default.aspx.cs
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class _4_Tier_Default : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){if (!IsPostBack){BindGrid();}}public void BindGrid(){GridView1.DataSource = GridDataSource();GridView1.DataBind();}private DataTable GridDataSource(){PersonBAL p = new PersonBAL();DataTable dTable = new DataTable();try{dTable = p.Load();}catch (Exception ee){lblMessage.Text = ee.Message.ToString();}finally{p = null;}return dTable;}/// <summary>/// Add records into database/// </summary>/// <param name="sender"></param>/// <param name="e"></param>#region AddRecordsprotected void AddRecords(object sender, EventArgs e){Person objbo = new Person();PersonBAL objbal = new PersonBAL();try{if (btnSubmit.Text == "Submit"){objbo.FirstName = txtFirstName.Text;objbo.LastName = txtLastName.Text;objbo.Age = Convert.ToInt32(txtAge.Text);int intResult = objbal.Insert(objbo);if (intResult > 0){lblMessage.Text = "Record Inserted Successfully";}else{lblMessage.Text = "Name Already Exists";}}else if (btnSubmit.Text == "Update"){objbo.PersonID = Convert.ToInt32(hdnPersonID.Value);objbo.FirstName = txtFirstName.Text;objbo.LastName = txtLastName.Text;objbo.Age = Convert.ToInt32(txtAge.Text);int intResult = objbal.Update(objbo);if (intResult > 0){lblMessage.Text = "Record Inserted Successfully";}else{lblMessage.Text = "Name Already Exists";}}}catch (Exception ex){throw ex;}GridView1.EditIndex = -1;BindGrid();btnSubmit.Text = "Submit";}#endregion
#region SelectedIndexChangingprotected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e){try{hdnPersonID.Value = "";GridViewRow gvrow = GridView1.Rows[e.NewSelectedIndex];hdnPersonID.Value = ((Label)gvrow.FindControl("lblPersonID")).Text.ToString();txtFirstName.Text = gvrow.Cells[1].Text.ToString();txtLastName.Text = gvrow.Cells[2].Text.ToString();txtAge.Text = gvrow.Cells[3].Text.ToString();//string ddlStateId = ((Label)gvrow.FindControl("lblstateid")).Text.ToString();//string ddldistrictId = ((Label)gvrow.FindControl("lblDistrictID")).Text.ToString();//string ddlCityId = ((Label)gvrow.FindControl("lblCityID")).Text.ToString();//string ddlpmcId = ((Label)gvrow.FindControl("lblPMCID")).Text.ToString();// txtPassword.Text = gvrow.Cells[3].Text.ToString();lblPMCID//ddlCityName.SelectedValue = ddlCityId;//ddlDistrictName.SelectedValue = ddldistrictId;//ddlStateName.SelectedValue = ddlStateId;//ddlpharmacytype.SelectedValue = ddlpmcId;//ddlDistrictName.Enabled = true;//ddlCityName.Enabled = true;if (gvrow.Cells[1].Text.ToString().Equals(" ")){txtFirstName.Text = "";}else{txtFirstName.Text = gvrow.Cells[1].Text.ToString();}if (gvrow.Cells[2].Text.ToString().Equals(" ")){txtLastName.Text = "";}else{txtLastName.Text = gvrow.Cells[2].Text.ToString();}if (gvrow.Cells[3].Text.ToString().Equals(" ")){txtAge.Text = "";}else{txtAge.Text = gvrow.Cells[3].Text.ToString();}btnSubmit.Text = "Update";}catch (Exception ex){//lblmsg.ForeColor = System.Drawing.Color.Red;//lblmsg.Text = ex.Message.ToString();}}#endregion}AppCode/BO/Person.cs
---------------
using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;/// <summary>/// Summary description for Person/// </summary>public class Person{public Person(){}int m_PersonID = 0;string m_FirstName = string.Empty;string m_LastName = string.Empty;int m_Age = 0;#region Propertierspublic int PersonID{get { return m_PersonID; }set { m_PersonID = value; }}public string FirstName{get { return m_FirstName; }set { m_FirstName = value; }}public string LastName{get { return m_LastName; }set { m_LastName = value; }}public int Age{get { return m_Age; }set { m_Age = value; }}#endregion Properties}
AppCode/DAL/PersonDAL.cs
-----------------------------using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;/// <summary>/// Summary description for PersonDAL/// </summary>public class PersonDAL{string connStr = ConfigurationManager.ConnectionStrings["TutTestConn"].ToString();public PersonDAL(){}/// <summary>/// Used to insert records into database/// </summary>/// <param name="p"></param>/// <returns></returns>public int Insert(Person person){SqlConnection conn = new SqlConnection(connStr);conn.Open();SqlCommand dCmd = new SqlCommand("InsertData", conn);dCmd.CommandType = CommandType.StoredProcedure;try{dCmd.Parameters.AddWithValue("@firstName", person.FirstName);dCmd.Parameters.AddWithValue("@lastName", person.LastName);dCmd.Parameters.AddWithValue("@age", person.Age);return dCmd.ExecuteNonQuery();}catch{throw;}finally{dCmd.Dispose();conn.Close();conn.Dispose();}}/// <summary>/// Update record into database/// </summary>/// <param name="p"></param>/// <returns></returns>public int Update(Person person){SqlConnection conn = new SqlConnection(connStr);conn.Open();SqlCommand dCmd = new SqlCommand("UpdateData", conn);dCmd.CommandType = CommandType.StoredProcedure;try{dCmd.Parameters.AddWithValue("@firstName", person.FirstName);dCmd.Parameters.AddWithValue("@lastName", person.LastName);dCmd.Parameters.AddWithValue("@age", person.Age);dCmd.Parameters.AddWithValue("@personID", person.PersonID);return dCmd.ExecuteNonQuery();}catch{throw;}finally{dCmd.Dispose();conn.Close();conn.Dispose();}}/// <summary>/// Load all records from database/// </summary>/// <returns></returns>public DataTable Load(){SqlConnection conn = new SqlConnection(connStr);SqlDataAdapter dAd = new SqlDataAdapter("LoadAll", conn);dAd.SelectCommand.CommandType = CommandType.StoredProcedure;DataSet dSet = new DataSet();try{dAd.Fill(dSet, "PersonTable");return dSet.Tables["PersonTable"];}catch{throw;}finally{dSet.Dispose();dAd.Dispose();conn.Close();conn.Dispose();}}/// <summary>/// Delete record from database/// </summary>/// <param name="person"></param>/// <returns></returns>public int Delete(Person person){SqlConnection conn = new SqlConnection(connStr);conn.Open();SqlCommand dCmd = new SqlCommand("DeleteData", conn);dCmd.CommandType = CommandType.StoredProcedure;try{dCmd.Parameters.AddWithValue("@personID", person.PersonID);return dCmd.ExecuteNonQuery();}catch{throw;}finally{dCmd.Dispose();conn.Close();conn.Dispose();}}}Stored Procedure-----------------------//InsertDataCreate procedure InsertData(@firstName varchar(200),@lastName varchar(200),@age int)AsInsert into Person (FirstName,LastName,Age)Values(@firstName,@lastName,@age)//UpdateDataCreate PROCEDURE UpdateData(@personID int,@firstName varchar(50),@lastName varchar(50),@age int)AS/* SET NOCOUNT ON */UPDATE Person SETFirstname = @firstName,Lastname = @lastName,Age = @ageWHERE PersonID = @personIDRETURN//DeleteDataALTER PROCEDURE DeleteData(@personID int)AS/* SET NOCOUNT ON */Delete Person WHERE PersonID = @personIDRETURN
Table Structure:
Output:
