SubsDotNet: 3-Tier Operation of Insert,Update,Delete and show Data from DB in Asp.Net

First create 1 Website and then 2 Library project in same application

Step wise:-

1-File->New Website---website will be create

2-Right click of Solution Explorer ->New Project->Class Library->give name as BAL

->Create 2 folder->give name as BE and BL

->In BE folder create one Class(.cs) file

named as-BE_tbl_Employee.cs

->In BL folder create one Class(.cs) file

named as-BL_Employee_Logics.cs

3-Right click of Solution Explorer ->New Project->Class Library->give name as DAL

->Create one Class(.cs) file

named as-ConnectionFactory.cs

Default.aspx:-(design part)

	EmpID	Ename	Job	Sal
Edit Delete	2	ewe	yyy	6545
Edit Delete	5	subs	fhhh	67865
Edit Delete	6	dhg	fghg	677
Edit Delete	12	sss	jjj	555
Edit Delete	13	gfdd	cxvcb	45354
Edit Delete	33	ghfh	sgh	34356

Insert Employee Data:-
Employee ID	:
Employee Name	:
Job	:
Sal	:
	:

Default.aspx:-(Source part)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

</head>

<body>

<div>

<asp:GridView ID="grdvOperations" runat="server" AutoGenerateColumns="False"

AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" OnRowCancelingEdit="grdvOperations_RowCancelingEdit" OnRowDeleting="grdvOperations_RowDeleting" OnRowEditing="grdvOperations_RowEditing" OnRowUpdating="grdvOperations_RowUpdating" >

<asp:TemplateField HeaderText="EmpID" SortExpression="EmpID" Visible="True">

<asp:Label ID="lblEmpId" runat="server" Text='<%#Eval("EmpID") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Ename" SortExpression="Ename">

<asp:Label ID="lblEname" runat="server" Text='<%# Eval("Ename") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtEname" runat="server" Text='<%# Eval("Ename") %>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Job" SortExpression="Job">

<asp:Label ID="lblJob" runat="server" Text='<%# Eval("Job") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtJob" runat="server" Text='<%# Eval("Job") %>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Sal" SortExpression="Sal">

<asp:Label ID="lblSal" runat="server" Text='<%# Eval("Sal") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtSal" runat="server" Text='<%# Eval("Sal") %>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

<div>

<table>

<tr><td>Insert Employee Data:-</td></tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</td>

</tr>

<tr>

</tr>

</table>

</div>

</form>

</body>

</html>

Default.aspx.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

using System.Data;

using BAL.BE;

using BAL.BL;

using DAL;

public partial class _Default : System.Web.UI.Page

{

BL_Employee_Logics obj_BL_Employee_Logics = new BL_Employee_Logics();

protected void Page_Load(object sender, EventArgs e)

{

if(!Page.IsPostBack)

{

BindData();

}

protected void BindData()

{

grdvOperations.DataSource = obj_BL_Employee_Logics.GetEmpDatafromDB();

grdvOperations.DataBind();

}

protected void grdvOperations_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

BE_tbl_Employee obj_BE_tbl_Employee = new BE_tbl_Employee();

Label lblEid = (Label)grdvOperations.Rows[e.RowIndex].FindControl("lblEmpId");

obj_BE_tbl_Employee.EmpID = Convert.ToInt32(lblEid.Text);

int rowAffected =obj_BL_Employee_Logics.DeleteEmpRecord(obj_BE_tbl_Employee);

grdvOperations.EditIndex = -1;

BindData();

if (rowAffected > 0)

{

Response.Write("Record Deleted Successfully");

}

else

{

Response.Write("Record Not Yet Deleted");

}

protected void grdvOperations_RowEditing(object sender, GridViewEditEventArgs e)

{

grdvOperations.EditIndex = e.NewEditIndex;

BindData();

}

protected void grdvOperations_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

BE_tbl_Employee obj_BE_tbl_Employee = new BE_tbl_Employee();

//TextBox Eid = (TextBox)grdvOperations.Rows[e.RowIndex].FindControl("txtEmpId");

//int empId = Convert.ToInt32(Eid.Text);

//obj_BE_tbl_Employee.EmpID = empId;

string eid =((Label)grdvOperations.Rows[e.RowIndex].FindControl("lblEmpId")).Text;

obj_BE_tbl_Employee.EmpID = Convert.ToInt32(eid);

string ename = ((TextBox)grdvOperations.Rows[e.RowIndex].FindControl("txtEname")).Text.Trim();

//string eName = ename.Text;

obj_BE_tbl_Employee.EName = ename;

string job = ((TextBox)grdvOperations.Rows[e.RowIndex].FindControl("txtJob")).Text.Trim();

//string Ejob = job.Text;

obj_BE_tbl_Employee.Job = job;

string sal = ((TextBox)grdvOperations.Rows[e.RowIndex].FindControl("txtSal")).Text.Trim();

//int Esal = Convert.ToInt32(sal.Text);

obj_BE_tbl_Employee.Sal =Convert.ToInt32(sal);

int rowAffected =obj_BL_Employee_Logics.UpdateEmpRecord(obj_BE_tbl_Employee);

grdvOperations.EditIndex = -1;

BindData();

if (rowAffected > 0)

{

Response.Write("updated successfully");

}

else

{

Response.Write("Not updated");

}

protected void grdvOperations_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

grdvOperations.EditIndex = -1;

BindData();

}

protected void btnInsertEmpData_Click(object sender, EventArgs e)

{

obj_BL_Employee_Logics = new BL_Employee_Logics();

BE_tbl_Employee obj_BE_tbl_Employee = new BE_tbl_Employee();

obj_BE_tbl_Employee.EmpID = Convert.ToInt32(txtEid.Text);

obj_BE_tbl_Employee.EName = txtEname.Text;

obj_BE_tbl_Employee.Job = txtJob.Text;

obj_BE_tbl_Employee.Sal =Convert.ToInt32(txtSal.Text);

int rowAffected = obj_BL_Employee_Logics.InsertEmpRecord(obj_BE_tbl_Employee);

if (rowAffected > 0)

{

Response.Write("Record Inserted Successfully");

}

else

{

Response.Write("Record Inserted Unsuccessfully");

txtEname.Text = txtJob.Text = txtSal.Text = "";

txtEname.Focus();

}

In Web.Config File:-

<?xml version="1.0"?>

<!--

For more information on how to configure your ASP.NET application, please visit

http://go.microsoft.com/fwlink/?LinkId=169433

-->

</connectionStrings>

<system.web>

</system.web>

</configuration>

In BAL:-(BE folder)

BE_tbl_Employee.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Configuration;

using System.Data;

using BAL.BE;

using BAL.BL;

using DAL;

namespace BAL.BE

{

public class BE_tbl_Employee

{

private int empID;

public int EmpID

{

get { return empID; }

set { empID = value; }

}

private string eName;

public string EName

{

get { return eName; }

set { eName = value; }

}

private string job;

public string Job

{

get { return job; }

set { job = value; }

}

private int sal;

public int Sal

{

get { return sal; }

set { sal = value; }

}

In BAL:-(BL folder)

BL_Employee_Logics.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

using BAL.BE;

using BAL.BL;

using DAL;

namespace BAL.BL

{

public class BL_Employee_Logics

{

public DataSet GetEmpDatafromDB()

{

DataSet ds = ConnectionFactory.ExecuteDataset(ConnectionFactory.GetConnection(), "SP_GetEmployeeAllData", CommandType.StoredProcedure);

return ds;

}

public int DeleteEmpRecord(BE_tbl_Employee obj_BE_tbl_Employee)

{

SqlParameter[] p = new SqlParameter[1];

p[0] = new SqlParameter("@EmpID", obj_BE_tbl_Employee.EmpID);

int r = ConnectionFactory.ExecuteNonQuery1(ConnectionFactory.GetConnection(), "SP_DeleteEmpRecords", CommandType.StoredProcedure, p);

return r;

}

public int UpdateEmpRecord(BE_tbl_Employee obj_BE_tbl_Employee)

{

SqlParameter[] p = new SqlParameter[4];

p[0] = new SqlParameter("@empid", obj_BE_tbl_Employee.EmpID);

p[1] = new SqlParameter("@ename", obj_BE_tbl_Employee.EName);

p[2] = new SqlParameter("@job", obj_BE_tbl_Employee.Job);

p[3] = new SqlParameter("@sal", obj_BE_tbl_Employee.Sal);

int r = ConnectionFactory.ExecuteNonQuery1(ConnectionFactory.GetConnection(), "SP_UpdateEmpRecord", CommandType.StoredProcedure, p);

return r;

}

public int InsertEmpRecord(BE_tbl_Employee obj_BE_tbl_Employee)

{

SqlParameter[] p = new SqlParameter[4];

p[0] = new SqlParameter("@eid", obj_BE_tbl_Employee.EmpID);

p[1] = new SqlParameter("@ename", obj_BE_tbl_Employee.EName);

p[2] = new SqlParameter("@job", obj_BE_tbl_Employee.Job);

p[3]=new SqlParameter("@sal",obj_BE_tbl_Employee.Sal);

int r = ConnectionFactory.ExecuteNonQuery1(ConnectionFactory.GetConnection(), "SP_InsertEmpRecord", CommandType.StoredProcedure, p);

return r;

}

In DAL:-

ConnectionFactory.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace DAL

{

public class ConnectionFactory

{

static SqlConnection scon;

static SqlDataAdapter sda;

static SqlCommand scmd;

public static string GetConnection()

{

return ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

}

static void CreateConnection(string strConnection)

{

scon = new SqlConnection(strConnection);

}

public static DataSet ExecuteDataset(string strSqlConnection, string strCommandText, CommandType ct)

{

CreateConnection(strSqlConnection);

sda = new SqlDataAdapter(strCommandText, scon);

sda.SelectCommand.CommandType = ct;

DataSet ds = new DataSet();

sda.Fill(ds);

return ds;

}

public static DataSet ExecuteDataset(string strSqlConnection, string strCommandText, CommandType ct, SqlParameter[] p)

{

CreateConnection(strSqlConnection);

sda = new SqlDataAdapter(strCommandText, scon);

sda.SelectCommand.CommandType = ct;

scmd.Parameters.AddRange(p);

DataSet ds = new DataSet();

sda.Fill(ds);

return ds;

}

public static int ExecuteNonQuery1(string strSqlConnection, string strCommandText, CommandType ct, SqlParameter[] p)

{

CreateConnection(strSqlConnection);

if (scon.State != ConnectionState.Open)

scon.Open();

scmd = new SqlCommand(strCommandText, scon);

scmd.CommandType = ct;

scmd.Parameters.AddRange(p);

return scmd.ExecuteNonQuery();

}

public static SqlDataReader ExecuteReader(string strSqlConnection, string strCommandText, CommandType ct, SqlParameter[] p)

{

CreateConnection(strSqlConnection);

if (scon.State != ConnectionState.Open)

scon.Open();

scmd = new SqlCommand(strCommandText, scon);

scmd.CommandType = ct;

scmd.Parameters.AddRange(p);

return scmd.ExecuteReader();

}

Store Procedures:-

1. Create procedure SP_UpdateEmpRecord(@empid int,@ename varchar(30),@job varchar(30),@sal int)

Begin

update employee set Ename=@ename,Job=@job,Sal=@sal where EmpID=@empid

End

2. Create procedure SP_InsertEmpRecord(@eid int,@ename varchar(30),@job varchar(30),@sal int)

Begin

insert into Employee(EmpID,Ename,Job,Sal)values(@eid,@ename,@job,@sal);

end

3. Create procedure SP_GetAllEmpRecords

Begin

Select * from Employee;

End

4. Create procedure SP_DeleteEmpRecords(@EmpID int)

Begin

delete from Employee where EmpID=@EmpID;

End

SubsDotNet

Monday, 2 December 2013

3-Tier Operation of Insert,Update,Delete and show Data from DB in Asp.Net

No comments:

Post a Comment