SubsDotNet: Gridview CRUD Operations using 3-Tier Architechture in ASP.NET

Ans:-

First Design the UI as Below:-

	UID	UName	EmailId	MobileNo	Pwd
Edit Delete	3	shaika	shaik@gmail.com	9876543211	shaik123
Edit Delete	4	subas	subas@gmail.com	9876543210	subs123
Edit Delete	5	shaik	shaik@gmail.com	9876543211	shaik123
Edit Delete	7	hzuzz	gfhjh	9765423567	34567bvb
Edit Delete	9	dfsd	fdgdfg	9876543211	hgrths

Home.aspx:-

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

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

</head>

<body>

<div>

<asp:GridView ID="GridView1" runat="server"

AutoGenerateColumns="False" PageSize="5" AutoGenerateDeleteButton="True"

AutoGenerateEditButton="True" ShowFooter = "true"

onpageindexchanging="GridView1_PageIndexChanging"

onrowcancelingedit="GridView1_RowCancelingEdit"

onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"

onrowupdating="GridView1_RowUpdating" AllowPaging="True" >

<asp:TemplateField HeaderText="UID" SortExpression="Uid">

<asp:TextBox ID="txtid" runat="server" Text='<%# Bind("Uid") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label1" runat="server" Text='<%# Bind("Uid") %>'></asp:Label>

</ItemTemplate>

<asp:Button ID="btnInsrt" Text="Insert" runat="server" onclick="btnInsrt_Click"></asp:Button>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="UName" SortExpression="Uname">

<asp:TextBox ID="txtUname" runat="server" Text='<%# Bind("Uname") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label2" runat="server" Text='<%# Bind("Uname") %>'></asp:Label>

</ItemTemplate>

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

</FooterTemplate>

</asp:TemplateField>

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

<asp:TextBox ID="txtEmailid" runat="server" Text='<%# Bind("EmailId") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label3" runat="server" Text='<%# Bind("EmailId") %>'></asp:Label>

</ItemTemplate>

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

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="MobileNo">

<asp:TextBox ID="txtMobile" runat="server" Text='<%# Bind("Mobile") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label4" runat="server" Text='<%# Bind("Mobile") %>'></asp:Label>

</ItemTemplate>

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

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Pwd">

<asp:Label ID="Label5" runat="server" Text='<%# Bind("Pwd") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtPWD" runat="server" Text='<%# Bind("Pwd") %>'></asp:TextBox>

</EditItemTemplate>

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

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

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 DAL;

using BAL.BL;

using BAL.BE;

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

{

BL_tbl_Register obj_BL = new BL_tbl_Register();

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

Bind();

}

void Bind()

{

GridView1.DataSource = obj_BL.ViewData();

GridView1.DataBind();

}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridView1.EditIndex = -1;

Bind();

}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

GridView1.PageIndex = e.NewPageIndex;

Bind();

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex =e.NewEditIndex;

Bind();

}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

BE_tbl_Register obj_BE = new BE_tbl_Register();

// string id = ((TextBox)GridView1.Rows[e.RowIndex].Cells[0].FindControl("txtid")).Text.Trim();

GridViewRow objgr = (GridViewRow)GridView1.Rows[e.RowIndex];

Label id1 = (Label)objgr.FindControl("Label1");

obj_BE.Uid = int.Parse(id1.Text);

int rowAffctd = obj_BL.Delete(obj_BE);

if (rowAffctd > 0)

{

Response.Write("Dlt Successfully..");

}

else

{

Response.Write("Dlt Unsuccessfully..");

}

Bind();

}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

BE_tbl_Register objBE_tbl_Register = new BE_tbl_Register();

GridViewRow objGVR = (GridViewRow)GridView1.Rows[e.RowIndex];

TextBox txtobjid = (TextBox)objGVR.FindControl("txtid");

TextBox txtObjUname = (TextBox)objGVR.FindControl("txtUname");

TextBox txtObjEmailid = (TextBox)objGVR.FindControl("txtEmailid");

TextBox txtObjMobile = (TextBox)objGVR.FindControl("txtMobile");

TextBox txtObjPWD = (TextBox)objGVR.FindControl("txtPWD");

objBE_tbl_Register.Uid = int.Parse(txtobjid.Text);

objBE_tbl_Register.Uname = (txtObjUname.Text);

objBE_tbl_Register.EmailId = (txtObjEmailid.Text);

objBE_tbl_Register.Mobile = Int64.Parse(txtObjMobile.Text);

objBE_tbl_Register.Pwd = (txtObjPWD.Text);

int rowAffctd = obj_BL.Update(objBE_tbl_Register);

if (rowAffctd > 0)

{

Response.Write("Update Successfully..");

}

else

{

Response.Write("Update Unsuccessfully..");

}

GridView1.EditIndex = -1;

Bind();

}

protected void btnInsrt_Click(object sender, EventArgs e)

{

BE_tbl_Register objBE_tbl_Register = new BE_tbl_Register();

GridViewRow objGVR = (GridViewRow)GridView1.FooterRow;

TextBox txtObjUname = (TextBox)objGVR.FindControl("txtIFUname");

TextBox txtObjEmailid = (TextBox)objGVR.FindControl("txtIFEmailid");

TextBox txtObjMobile = (TextBox)objGVR.FindControl("txtIFMobile");

TextBox txtObjPWD = (TextBox)objGVR.FindControl("txtIFPWD");

objBE_tbl_Register.Uname = (txtObjUname.Text);

objBE_tbl_Register.EmailId = (txtObjEmailid.Text);

objBE_tbl_Register.Mobile = Int64.Parse(txtObjMobile.Text);

objBE_tbl_Register.Pwd = (txtObjPWD.Text);

int rowAffctd = obj_BL.Insert(objBE_tbl_Register);

if (rowAffctd > 0)

{

Response.Write("Insrt Successfully..");

}

else

{

Response.Write("Insrt Unsuccessfully..");

}

Bind();

}

BAL Library Class:-

In BE folder:-

BE_tbl_Register.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace BAL.BE

{

public class BE_tbl_Register

{

private int uid;

public int Uid

{

get { return uid; }

set { uid = value; }

}

private Int64 mobile;

public Int64 Mobile

{

get { return mobile; }

set { mobile = value; }

}

private string uname;

public string Uname

{

get { return uname; }

set { uname = value; }

}

private string pwd;

public string Pwd

{

get { return pwd; }

set { pwd = value; }

}

private string emailId;

public string EmailId

{

get { return emailId; }

set { emailId = value; }

}

In BL Folder:-

BL_tbl_Register.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using DAL;

using BAL.BE;

namespace BAL.BL

{

public class BL_tbl_Register

{

BE_tbl_Register objBE_tbl_Register = new BE_tbl_Register();

public DataTable ViewData()

{

return ConnectionFactory.ExecuteCommand("sp_ViewData");

}

public int ChkEmail(BE_tbl_Register objBE)

{

SqlParameter[] p=new SqlParameter[1];

p[0]=new SqlParameter("@EmailId", objBE.EmailId);

return ConnectionFactory.ExecuteNonQueryMethod("SP_ChkEmail", p);

}

public int ChkUserNamePwd(BE_tbl_Register objBE)

{

SqlParameter[] p = new SqlParameter[2];

p[0] = new SqlParameter("@Uname", objBE.Uname);

p[1] = new SqlParameter("@Pwd", objBE.Pwd);

return ConnectionFactory.ExecuteNonQueryMethod("SP_CHKUnamePwd", p);

}

public int Insert(BE_tbl_Register objBE)

{

SqlParameter[] p = new SqlParameter[4];

p[0] = new SqlParameter("@Uname", objBE.Uname);

p[1] = new SqlParameter("@EmailId", objBE.EmailId);

p[2] = new SqlParameter("@Mobile", objBE.Mobile);

p[3] = new SqlParameter("@Pwd", objBE.Pwd);

return ConnectionFactory.ExecuteNonQueryMethod("SP_InsertUserData", p);

}

public int Update(BE_tbl_Register objBE)

{

SqlParameter[] p = new SqlParameter[5];

p[0] = new SqlParameter("@Uid", objBE.Uid);

p[1] = new SqlParameter("@Uname", objBE.Uname);

p[2] = new SqlParameter("@emailid", objBE.EmailId);

p[3] = new SqlParameter("@Mobile", objBE.Mobile);

p[4] = new SqlParameter("@PWD", objBE.Pwd);

return ConnectionFactory.ExecuteNonQueryMethod("SP_UpdateUserData", p);

}

public int Delete(BE_tbl_Register objBE)

{

SqlParameter[] p = new SqlParameter[1];

p[0] = new SqlParameter("@Uid", objBE.Uid);

return ConnectionFactory.ExecuteNonQueryMethod("SP_DeleteUserData", p);

}

DAL Library Class:-

ConnectionFactory.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace DAL

{

public class ConnectionFactory

{

static SqlConnection cn;

static SqlCommand cmd;

static SqlDataReader dr;

static string constr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

static SqlConnection GetConnection()

{

cn = new SqlConnection(constr);

cn.Open();

return cn;

}

public static int ExecuteNonQueryMethod(string spname, params SqlParameter[] p)

{

try

{

cmd = new SqlCommand();

cmd.Connection = GetConnection();

cmd.CommandText = spname;

cmd.CommandType = CommandType.StoredProcedure;

foreach (object o in p)

{

if (p != null)

cmd.Parameters.Add(o);

}

int result = cmd.ExecuteNonQuery();

return result;

}

catch (Exception e)

{

throw;

}

finally

{

cn.Close();

}

public static DataTable ExecuteCommand(string spname)

{

try

{

DataTable dt = new DataTable();

cmd = new SqlCommand();

cmd.Connection = GetConnection();

cmd.CommandText = spname;

cmd.CommandType = CommandType.StoredProcedure;

dr = cmd.ExecuteReader();

dt.Load(dr);

return dt;

}

catch (Exception e)

{

throw;

}

finally

{

cn.Close();

}

In Web.Config File:-

</connectionStrings>

Stored Procedures Created for above Operations:-

1.Create procedure [dbo].[SP_ChkEmail](@emailid varchar(15))

if Exists(select EmailId from Register where EmailId=@emailid)

Begin

select 1

end

else

Begin

select 0;

end

2.Create procedure [dbo].[SP_ChkUnamePwd](@emailid varchar(15),@pwd varchar(15))

if Exists(select EmailId,Pwd from Register where EmailId=@emailid and Pwd=@pwd)

Begin

select 1

end

else

Begin

select 0;

end

3.Create procedure [dbo].[SP_DeleteUserData](@uid int)

Begin

Delete Register where Uid=@uid

end

4.Create procedure [dbo].[SP_InsertUserData](@uname varchar(15),@emailid varchar(15),@mobile bigint,@pwd varchar(15))

Begin

insert into Register(Uname,EmailId,Mobile,Pwd) values(@uname,@emailid,@mobile,@pwd)

end

5.Create procedure [dbo].[SP_UpdateUserData](@uid int,@uname varchar(15),@emailid varchar(15),@mobile bigint,@pwd varchar(15))

Begin

update Register set Uname=@uname,EmailId=@emailid,Mobile=@mobile,Pwd=@pwd where Uid=@uid;

end

6.Create procedure [dbo].[sp_ViewData]

begin

select * from Register

end

SubsDotNet

Saturday, 18 January 2014

Gridview CRUD Operations using 3-Tier Architechture in ASP.NET

No comments:

Post a Comment