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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
User Name
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
<br />
<br />
Passwors
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox>
<br />
<br />
<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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
<center> <table>
<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>
<tr><td>
<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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<center><table width="50%">
<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">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<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; }
}
}
}