CRUD operation using N-Tier Architecture

Introduction

Here, I am explaining the Grid view Insert Update Delete Using the N-Tier Architecture.

Default.aspx

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<br />

User Name

<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>

<br />

Passwors

<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox>

<br />

<asp:Button ID="btnlogin" runat="server" onclick="btnSave_Click" Text="Login"
/>

</div>

</form>

</body>

</html>

Home.aspx

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<br />

<tr><td>UserName</td><td>

<asp:TextBox ID="txtuser" runat="server"></asp:TextBox></td></tr>

<tr><td>Password</td><td>

<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox></td></tr>

<center><asp:Button ID="savebtn" runat="server" Text="Save"

onclick="savebtn_Click" /></center></td><td>

<center><asp:Button ID="updatebtn" runat="server" Text="Update"

onclick="updatebtn_Click" /></center></td></tr>

</table>

</center>

</div>

</form>

</body>

</html>

UserDetail.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<tr>

<td>

<asp:GridView ID="GridView1" DataKeyNames="id,UserName,Password"
runat="server" Style="margin-left: 121px"

OnRowDeleting="GridView1_RowDeleting" Height="175px"
OnRowUpdating="GridView1_RowUpdating"

Width="368px">

<asp:TemplateField>

<asp:ImageButton ID="imgbtnEdit" CommandName="Update"
runat="server" ImageUrl="~/Images/Edit.jpg"

ToolTip="Edit" Height="20px" Width="20px" />

<asp:ImageButton ID="imgbtnDelete" CommandName="Delete"
runat="server" ImageUrl="~/Images/delete.jpg"

ToolTip="Delete" Height="20px" Width="20px" />

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</td>

</tr>

<tr>

<td>

<center><asp:Button ID="newuserbtn" runat="server" OnClick="newuserbtn_Click"

Text="New User" /></center>

</td>

</tr>

</table></center>

</div>

</form>

</body>

</html>

Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using UserManBO;

using UserManEntity;

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSave_Click(object sender, EventArgs e)

{

UserEntity userEntity = new UserEntity ();

userEntity.Password= txtPassword.Text;

userEntity.UserName =txtUserName.Text;

UserBO userBo = new UserBO();

bool value = userBo.LoginCheckUser(userEntity);

if (value == true)

{

Response.Redirect("~/userdetail.aspx");

}

else

{

ScriptManager.RegisterStartupScript(this, GetType(), "Message",
"alert('Error in username or password');", true);

// Response.Redirect("Error in username or password");

}

home.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using UserManBO;

using UserManEntity;

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

{

protected void Page_Load(object sender, EventArgs e)

{

string btntext2;

if (!IsPostBack)

{

if (Session["btntext1"] != null)

{

btntext2 = Session["btntext1"].ToString();

Session["btntext1"] = null;

if (btntext2 == "New User")

savebtn.Visible = true;

updatebtn.Visible = false;

}

else {

int id1;

if (Session["id"]!=null && Session ["user"]!=null && Session["pass"]!=null )

{

id1 = int.Parse(Session["id"].ToString());

string username=Session ["user"].ToString();

string password = Session["pass"].ToString();

txtuser.Text = username;

txtPassword.Text = password;

Session["btntext1"] = null;

Session["user"] = null;

Session["pass"] = null;

savebtn.Visible = false;

updatebtn.Visible = true;

}

//UserBO userBo = new UserBO();

//UserEntity userEntity = new UserEntity();

//int userid ;

//if (!IsPostBack)

//{

// userid = int.Parse(Session["id"].ToString());

// userEntity.Id = userid;

// userBo.SelectUserForUpdate(userEntity);

//}

}

protected void updatebtn_Click(object sender, EventArgs e)

{

UserEntity userEntity = new UserEntity();

int id1=int.Parse(Session["id"].ToString());

userEntity.Id = id1;

userEntity.UserName = txtuser.Text.Trim();

userEntity.Password = txtPassword.Text.Trim();

UserBO userBO = new UserBO();

bool value=userBO.UpdateUser(userEntity);

if(value ==true)

{

ScriptManager.RegisterStartupScript(this, GetType(), "Message",
"alert('Records updated successfully');", true);

//Response.Write("Update");

}

else { ScriptManager.RegisterStartupScript(this, GetType(), "Message",
"alert('Error');", true); }

}

protected void savebtn_Click(object sender, EventArgs e)

{

UserEntity userEntity = new UserEntity();

userEntity.UserName = txtuser.Text.Trim();

userEntity.Password = txtPassword.Text.Trim();

UserBO userBO = new UserBO();

bool value = userBO.InsertUser(userEntity);

if (value == true)

{

ScriptManager.RegisterStartupScript(this, GetType(), "Message",
"alert('Records saved successfully');", true);

}

else

{

ScriptManager.RegisterStartupScript(this, GetType(), "Message",
"alert('Error in record saved');", true);

//Response.Write("Error in record saved");

}

userdetail.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.Data.SqlClient;

using UserManEntity;

using UserManBO;

using System.Data;

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

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

string query = "select * from tblUser";

UserBO userBo = new UserBO();

DataTable dt = userBo.SelectUserForGridView(query);

GridView1.DataSource = dt;

GridView1.DataBind();

}

protected void newuserbtn_Click(object sender, EventArgs e)

{

string btntext = newuserbtn.Text;

Session.Add("btntext1",btntext );

Response.Redirect("~/home.aspx");

}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

int userid = Convert.ToInt32(GridView1.DataKeys
[e.RowIndex].Values["id"].ToString());

UserBO userBo = new UserBO();

bool value=userBo.DeleteUser(userid );

if (value == true)

{

Response.Redirect("~/userdetail.aspx");

ScriptManager.RegisterStartupScript(this, GetType(), "Message",
"alert('Deleted successfully');", true);

// Response.Write("deleted sucessfully");

}

else { Response.Write("deleted sucessfully"); }

}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

int userid = Convert.ToInt32(GridView1.DataKeys
[e.RowIndex].Values["id"].ToString());

string username = GridView1.DataKeys[e.RowIndex].
Values["UserName"].ToString();

string password = GridView1.DataKeys[e.RowIndex].
Values["Password"].ToString();

Session.Add("id",userid );

Session.Add("user", username);

Session.Add("pass", password);

//UserEntity userEntity = new UserEntity();

//userEntity.Id = userid;

//UserBO userBo = new UserBO();

//userBo.UpdateUser(userEntity );

Response.Redirect("~/home.aspx");

}

UserBO.cs

using UserManEntity;

using UserManDAL;

using System.Data;

namespace UserManBO

{

public class UserBO

{

public bool InsertUser(UserEntity userEntity)

{

string SQLQuery = "Insert into tblUser Values('" + userEntity.UserName
+ "', '" + userEntity.Password + "')";

BaseDAL dal = new BaseDAL();

dal.CreateConnection();

dal.BeginTransaction();

dal.ExecuteSQL(SQLQuery);

dal.CommitTransaction();

dal.CloseConnection();

return true;

}

public bool UpdateUser(UserEntity userEntity)

{

string SQLQuery = "update tblUser set username='" + userEntity.UserName
+ "',Password= '" + userEntity.Password + "' where id="+userEntity.Id ;

BaseDAL dal = new BaseDAL();

dal.CreateConnection();

dal.BeginTransaction();

dal.ExecuteSQL(SQLQuery);

dal.CommitTransaction();

dal.CloseConnection();

return true;

}

public bool DeleteUser(int id1)

{

string query = "delete tblUser where id=" + id1;

BaseDAL dal = new BaseDAL();

dal.CreateConnection();

dal.BeginTransaction();

dal.ExecuteSQL(query );

dal.CommitTransaction();

dal.CloseConnection();

return true;

}

public bool GetAllUser()

{

return true;

}

public bool LoginCheckUser(UserEntity userEntity)

{

string query = "select Password from tblUser where UserName='"+
userEntity.UserName+"'";

BaseDAL dal = new BaseDAL();

string password = userEntity.Password;

dal.CreateConnection();

dal.BeginTransaction();

bool value=dal.ExecuteReaderSQL(query,password);

dal.CommitTransaction();

dal.CloseConnection();

return value;

}

//public bool SelectUserForUpdate(UserEntity userEntity)

//{

// string query = "select UserName,Password from where id=" +
userEntity.Id;

// BaseDAL dal = new BaseDAL();

// dal.CreateConnection();

// dal.BeginTransaction();

// dal.ExecuteDataReaderSQL(query);

// dal.CommitTransaction();

// dal.CloseConnection();

// return true;

//}

public DataTable SelectUserForGridView(string sql)

{

BaseDAL dal = new BaseDAL();

dal.CreateConnection();

dal.BeginTransaction();

DataTable dt=dal.FetchRecords(sql);

dal.CommitTransaction();

dal.CloseConnection();

return dt;

}

BaseDAL.cs

using System.Data;

using System.Data.SqlClient;

namespace UserManDAL

{

public class BaseDAL

{

//private string ConnectionString = System.Configuration.
ConfigurationSettings.AppSettings["ConnectionString"].ToString();

public string ConnectionString = "";

public SqlConnection Connection = new SqlConnection();

public SqlCommand Command;

public SqlTransaction Transaction;

public BaseDAL()

{

Command = Connection.CreateCommand();

}

public bool CreateConnection()

{

if (Connection.State != ConnectionState.Open)

{

Connection.ConnectionString = ConnectionString;

Connection.Open();

}

return true;

}

public bool CloseConnection()

{

if (Connection.State == ConnectionState.Open)

{

Connection.Close();

}

return true;

}

public bool BeginTransaction()

{

Transaction = Connection.BeginTransaction();

Command.Transaction = Transaction;

return true;

}

public bool CommitTransaction()

{

Transaction.Commit();

return true;

}

public bool RollBackTransaction()

{

Transaction.Rollback();

return true;

}

public bool ExecuteSQL(string SQL)

{

Command.Connection = Connection;

Command.CommandText = SQL;

Command.CommandType = CommandType.Text;

int Val = Command.ExecuteNonQuery();

return true;

}

public bool ExecuteReaderSQL(string SQL,string password)

{

Command.Connection = Connection;

Command.CommandText = SQL;

Command.CommandType = CommandType.Text;

string password1 = Command.ExecuteScalar().ToString();

if (password == password1)

{

return true;

}

else { return false; }

}

public DataTable FetchRecords(string SQL)

{

Command.Connection = Connection;

Command.CommandText = SQL;

Command.CommandType = CommandType.Text;

SqlDataReader Reader = Command.ExecuteReader();

DataTable Dt = new DataTable();

Dt.Load(Reader);

return Dt;

}

public DataTable ExecuteDataReaderSQL(string SQL)

{

Command.Connection = Connection;

Command.CommandText = SQL;

Command.CommandType = CommandType.Text;

SqlDataReader Reader = Command.ExecuteReader();

DataTable Dt = new DataTable();

Dt.Load(Reader);

return Dt;

}

UserEntity.cs

namespace UserManEntity

{

public class UserEntity

{

private string _userName;

public string UserName

{

get { return _userName; }

set { _userName = value; }

}

private string _password;

public string Password

{

get { return _password; }

set { _password = value; }

}

private int _id;

public int Id

{

get { return _id; }

set { _id = value; }

}