mithilesh kumar singh

HOW TO CREATE TO INSERT,EDIT,UPDATE,BIND AND DELETE DATA in 3 TIER APPLICATION IN ASP.NET So create a Sql server database e.g. Sample
and in that create a table e.g. UserDetails

Now create the stored procedure to insert the Userdetails in the table.
create procedure SP_InsertUserDetails
(
@UserName varchar(50),
@Address varchar(150),
@EmailId varchar(50),
@LastQualification varchar(50),
@PhoneNo varchar(10),
@Age varchar(3),
@Gender char(1),
@Hobby varchar(150)
)
As
Begin
Insert intoUserDetails(
UserName,
Address,
EmailId,
LastQualification,
PhoneNo,
Age,
Gender,
Hobby
)
values
(
@UserName,
@Address,
@EmailId,
@LastQualification,
@PhoneNo,
@Age,
@Gender,
@Hobby
)

End
Create a stored procedure to update the Userdetail
create procedure SP_UpdateUserDetails
(
@UserId int,
@UserName varchar(50),
@Address varchar(150),
@EmailId varchar(50),
@LastQualification varchar(50),
@PhoneNo varchar(10),
@Age varchar(3),
@Gender char(1),
@Hobby varchar(150)
)
As
Begin
UPDATE UserDetails SET
UserName=@UserName,
Address=@Address,
EmailId=@EmailId,
LastQualification=@LastQualification,
PhoneNo=@PhoneNo,
Age=@Age,
Gender=@Gender,
Hobby=@Hobby
where UserId=@UserId
End

Create a stored procedure to delete UserDetail record

Create procedure SP_DeleteUserDetailById

(
@UserId int
)
As
Begin
delete from UserDetails where UserId=@UserId
end

Create a stored procedure to Select All UserDetails

Create procedure SP_DeleteUserDetail

As
Begin
select * from UserDetails
end

Now need to connect our asp.net application with the sql server database. So in the web.config file create the connection string under the <configuration> tag as:

</connectionStrings>

Create Class In BEL

Right click on the "UserDetails3Tier" in the solution explorer -> Add -> New Item -> Select "Class" and name it "UserDetails_BEL.cs" as shown in image below.

->Click Add

Create the property for each column in the table "UserDetails". So write the code in UserDetails_BEL.cs as:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace UserDetails.BEL
{

public class UserDetails_BEL
{ public int userId { get; set; }
public string userName { get; set; }
public string address { get; set; }
public string emailId { get; set; }
public string qastQualification { get; set; }
public string phoneNo { get; set; }
public string age { get; set; }
public char gender { get; set; }
public string hobby { get; set; } }}
Create Class in DAL

Now we need to create a class in DAL to perform database operations.
Right click on the "UserDetails3Tier" in the solution explorer -> Add -> New Item -> Select "Class" and name it "UserDetails_DAL.cs" as shown in image below.

Write the following code in UserDetailsDAL.cs :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using UserDetails.BEL;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for UserDetailsDAL
/// </summary>
///
namespace UserDetails.DAL
{
public class UserDetailsDAL
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StrCon"].ConnectionString);
public int InsertUserDetails(UserDetails_BEL objBel)
{
int rowAffected;
try
{
SqlCommand cmd = new SqlCommand("InsertUserDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", objBel.userName);
cmd.Parameters.AddWithValue("@Address", objBel.address);
cmd.Parameters.AddWithValue("@EmailId", objBel.emailId);
cmd.Parameters.AddWithValue("@LastQualification", objBel.qastQualification);
cmd.Parameters.AddWithValue("@PhoneNo", objBel.phoneNo);
cmd.Parameters.AddWithValue("@Age", objBel.age);
cmd.Parameters.AddWithValue("@Gender", objBel.gender);
cmd.Parameters.AddWithValue("@Hobby", objBel.hobby);
if(con.State==ConnectionState.Closed)
{
con.Open();
}
rowAffected = cmd.ExecuteNonQuery();
cmd.Dispose();
if(rowAffected>0)
{
return rowAffected;
}
else
{
return 0;
}
} catch (Exception ex)

{

throw;

}

finally

{

if(con.State!=ConnectionState.Closed)

{

con.Close();

}

public DataSet GetUserDetails()

{

DataSet ds = new DataSet();

try

{

SqlCommand cmd = new SqlCommand("SP_DeleteUserDetail", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(ds);

cmd.Dispose();

}

catch(Exception ex)

{

throw;

}

finally

{

ds.Dispose();

}

return ds;

}

public int DeleteUserDetails(UserDetails_BEL ObjBel)

{

int rowAffected;

try

{

SqlCommand cmd = new SqlCommand("SP_DeleteUserDetailById", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@UserId", ObjBel.userId);

if(con.State==ConnectionState.Closed)

{

con.Open();

}

rowAffected = cmd.ExecuteNonQuery();

cmd.Dispose();

if(rowAffected>0)

{

return rowAffected;

}

else

{

return 0;

}

catch(Exception ex)

{

throw;

}

finally

{

if(con.State!=ConnectionState.Closed)

{

con.Close();

}

public int UpadateUserDetails(UserDetails_BEL ObjBel)

{

int RowAffected;

try

{

SqlCommand cmd = new SqlCommand("SP_UpdateUserDetails", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@UserName", ObjBel.userName);

cmd.Parameters.AddWithValue("@Address", ObjBel.address);

cmd.Parameters.AddWithValue("@EmailId", ObjBel.emailId);

cmd.Parameters.AddWithValue("@LastQualification", ObjBel.qastQualification);

cmd.Parameters.AddWithValue("@PhoneNo", ObjBel.phoneNo);

cmd.Parameters.AddWithValue("@Age", ObjBel.age);

cmd.Parameters.AddWithValue("@Gender", ObjBel.gender);

cmd.Parameters.AddWithValue("@Hobby", ObjBel.hobby);

if(con.State!=ConnectionState.Open)

{

con.Open();

}

RowAffected = cmd.ExecuteNonQuery();

cmd.Dispose();

if(RowAffected>0)

{

return RowAffected;

}

else

{

return 0;

}

catch(Exception ex)

{

throw;

}

finally

{

if(con.State!=ConnectionState.Closed)

{

con.Close();

}

}}

Create Class in BLL
Now here need to create a class that act as a bridge between Presentation layer and Data access layer whose work is to pass the data from the presentation layer to data access layer for processing and after that getting sending the results back to the presentation layer.

So right click on the "UserDetails3Tier" in the solution explorer -> Add -> New Item -> Select "Class" and name it "UserDetailsPRLogic.cs" as shown in image below.

->Click Add

And Write the following code in UserDetailsPRLogic.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using UserDetails.BEL;
using UserDetails.DAL;
using System.Data;
/// <summary>
/// Summary description for UserDetailsPRLogic
/// </summary>
///
namespace UserDetails.PRLogic
{
public class UserDetailsPRLogic
{
public int InsertUserDetails(UserDetails_BEL objBel)
{
UserDetailsDAL objDalInsert = new UserDetailsDAL();
try
{
return objDalInsert.InsertUserDetails(objBel);
}
catch
{
throw;
}
finally
{
objDalInsert = null;
}
}

public DataSet GetUserDetails()

{

UserDetailsDAL objDalGet = new UserDetailsDAL();

try

{

return objDalGet.GetUserDetails();

}

catch(Exception ex)

{

throw ex;

}

finally

{

objDalGet = null;

}

public int DeleteUserDetails(UserDetails_BEL objBel)

{

UserDetailsDAL objDalDelete = new UserDetailsDAL();

try

{

return objDalDelete.DeleteUserDetails(objBel);

}

catch(Exception ex)

{

throw ex;

}

finally

{

objDalDelete = null;

}

public int UpdateUserDetails(UserDetails_BEL ObjBel)

{

UserDetailsDAL ObjDalUpdate = new UserDetailsDAL();

try

{

return ObjDalUpdate.UpadateUserDetails(ObjBel);

}

catch(Exception ex)

{

throw ex;

}

finally

{

ObjDalUpdate = null;

}

Create page in Presentation Layer

Default.aspx

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

<!DOCTYPE html>

</head>

<body>

<legend style="font-size:25px">3 tier example of User details</legend>

<tr>

<td>

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

<asp:RequiredFieldValidator ID="rfvUserkName" runat="server"

ErrorMessage="User Name can't be left blank" ControlToValidate="txtUserName"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<th>Address </th><th>:</th>

<td>

<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox><br />

<asp:RequiredFieldValidator ID="rfvAddress" runat="server"

ErrorMessage="User Address can't be left blank" ControlToValidate="txtAddress"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<th>EmailId </th><th>:</th>

<td>

<asp:TextBox ID="txtEmailId" runat="server"></asp:TextBox><br />

<asp:RequiredFieldValidator ID="rfvEmailId" runat="server"

ErrorMessage="User EmailId can't be left blank" ControlToValidate="txtEmailId"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<th>LastQualification </th><th>:</th>

<td>

<asp:TextBox ID="txtLastQualification" runat="server"></asp:TextBox><br />

<asp:RequiredFieldValidator ID="rfvLastQualification" runat="server"

ErrorMessage="Last Qualification can't be left blank" ControlToValidate="txtLastQualification"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<th>Phone Numer </th><th>:</th>

<td>

<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox><br />

<asp:RequiredFieldValidator ID="RFVPhone" runat="server"

ErrorMessage="User Phone can't be left blank" ControlToValidate="txtPhone"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="REVPhone" runat="server"

ControlToValidate="txtPhone" Display="Dynamic"

ErrorMessage="Enter Numeric only" ForeColor="Red" SetFocusOnError="True"

ValidationExpression="^\d*[0-9](|.\d*[0-9]|)*$"></asp:RegularExpressionValidator>

</td>

</tr>

<tr>

<td>

<asp:TextBox ID="txtAge" runat="server"></asp:TextBox><br />

<asp:RequiredFieldValidator ID="RFVAge" runat="server"

ErrorMessage="User Age can't be left blank" ControlToValidate="txtAge"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="rgeAge" runat="server"

ControlToValidate="txtAge" Display="Dynamic"

ErrorMessage="Enter Numeric only" ForeColor="Red" SetFocusOnError="True"

ValidationExpression="^\d*[0-9](|.\d*[0-9]|)*$"></asp:RegularExpressionValidator>

</td>

</tr>

<tr>

<th>Gender </th><th>:</th>

<td>

<asp:RadioButtonList ID="RDBGender" runat="server" RepeatDirection="Horizontal">

<asp:ListItem Value="M">Male</asp:ListItem>

<asp:ListItem Value="F">FeMale</asp:ListItem>

</asp:RadioButtonList>

<br />

<asp:RequiredFieldValidator ID="RFVGender" runat="server"

ErrorMessage="User Gender can't be left blank" ControlToValidate="RDBGender"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<th>Hobbies </th><th>:</th>

<td>

<asp:TextBox ID="txtHobby" runat="server"></asp:TextBox><br />

<asp:RequiredFieldValidator ID="RFVHobby" runat="server"

ErrorMessage="User Hobbies can't be left blank" ControlToValidate="txtHobby"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click"

/></td>

</tr>

<tr>

<asp:Label ID="lblStatus" runat="server" Text=""></asp:Label></td>

</tr>

</table>

<br />

</fieldset>

</div>

<asp:GridView ID="grdUserDetails" runat="server" DataKeyNames="UserId"

AutoGenerateColumns="False"

OnPageIndexChanging="grdUserDetails_PageIndexChanging"

OnRowCancelingEdit="grdUserDetails_RowCancelingEdit"

OnRowDeleting="grdUserDetails_RowDeleting"

OnRowEditing="grdUserDetails_RowEditing"

OnRowUpdating="grdUserDetails_RowUpdating" AllowPaging="True" PageSize="5"

CellPadding="4" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px">

<asp:TemplateField HeaderText="User Name">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="EmailId">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Last Qualification">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="PhoneNo">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Age">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Gender">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Hobby">

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

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=" Edit " ItemStyle-HorizontalAlign="Center">

<asp:ImageButton ID="imgEdit" runat="server" Width="75px" Height="30px" ImageUrl="~/Images/edit-button-blue-hi.png" CommandName="Edit" CausesValidation="false" />

</ItemTemplate>

<asp:LinkButton ID="lnkUpdate" runat="server" Text="Update" CommandName="Update" CausesValidation="false"></asp:LinkButton>

<asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" CommandName="Cancel" CausesValidation="false"></asp:LinkButton>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=" Delete " ItemStyle-HorizontalAlign="Center">

<asp:ImageButton ID="imgDelete" runat="server" Width="75px" Height="30px" ImageUrl="~/Images/delete.png" CommandName="Delete" CausesValidation="false" OnClientClick="return confirm('Are you sure you want to delete?')" />

</ItemTemplate>

</EditItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView></div>

</form>

</body>

</html>

Asp.Net C# Code

In the code behind file (Default.aspx.cs.) write the code as:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using UserDetails.BEL;

using UserDetails.DAL;

using UserDetails.PRLogic;

using System.Data;

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

{

UserDetails_BEL objBEL = new UserDetails_BEL();

UserDetailsDAL objDEL = new UserDetailsDAL();

UserDetailsPRLogic objPR = new UserDetailsPRLogic();

protected void Page_Load(object sender, EventArgs e)

{

if(!Page.IsPostBack)

{

BindUserDetails();

}

private void BindUserDetails()

{

DataSet ds = new DataSet();

try

{

ds = objPR.GetUserDetails();

if(ds.Tables[0].Rows.Count>0)

{

grdUserDetails.DataSource = ds;

grdUserDetails.DataBind();

}

else

{

grdUserDetails.DataSource = null;

grdUserDetails.DataBind();

}

catch(Exception ex)

{

throw ex;

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

objBEL.userName = txtUserName.Text;

objBEL.address = txtAddress.Text;

objBEL.emailId = txtEmailId.Text;

objBEL.lastQualification = txtLastQualification.Text;

objBEL.phoneNo = txtPhone.Text;

objBEL.age = txtAge.Text;

objBEL.gender = Convert.ToChar(RDBGender.SelectedItem.Value);

objBEL.hobby = txtHobby.Text;

try

{

int rowAffected = objPR.InsertUserDetails(objBEL);

if (rowAffected > 0)

{

lblStatus.Text = "<b style='color:green'>User Details Insertion Sucessful</b>";

BindUserDetails();

}

else

{

lblStatus.Text = "<b style='color:red'>User Details Insertion Faild</b>";

}

catch (Exception ex)

{

throw ex;

}

protected void grdUserDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

grdUserDetails.PageIndex = e.NewPageIndex;

BindUserDetails();

}

protected void grdUserDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

grdUserDetails.EditIndex = -1;

BindUserDetails();

}

protected void grdUserDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

objBEL.userId = Convert.ToInt32(grdUserDetails.DataKeys[e.RowIndex].Value);

try

{

int rowAffected = objPR.DeleteUserDetails(objBEL);

if (rowAffected > 0)

{

lblStatus.Text = "<b style='color:green'>User Details deletion Sucessful</b>";

BindUserDetails();

}

else

{

lblStatus.Text = "<b style='color:red'>User Details deletion Faild</b>";

}

catch (Exception ex)

{

throw ex;

}

protected void grdUserDetails_RowEditing(object sender, GridViewEditEventArgs e)

{

grdUserDetails.EditIndex = e.NewEditIndex;

BindUserDetails();

}

protected void grdUserDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

objBEL.userId = Convert.ToInt32(grdUserDetails.DataKeys[e.RowIndex].Value);

objBEL.userName = ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtUserNameEdit"))).Text.Trim();

objBEL.address = ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtAddressEdit"))).Text.Trim();

objBEL.emailId= ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtEmailIdEdit"))).Text.Trim();

objBEL.lastQualification = ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtLastQualificationEdit"))).Text.Trim();

objBEL.phoneNo = ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtPhoneNoEdit"))).Text.Trim();

objBEL.age = ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtAgeEdit"))).Text.Trim();

objBEL.gender1 = ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtGenderEdit"))).Text.Trim();

objBEL.hobby = ((TextBox)(grdUserDetails.Rows[e.RowIndex].FindControl("txtHobbyEdit"))).Text.Trim();

try

{

int rowAffected = objPR.UpdateUserDetails(objBEL);

if (rowAffected > 0)

{

lblStatus.Text = "<b style='color:green'>User Details Updation Sucessful</b>";

grdUserDetails.EditIndex = -1;

BindUserDetails();

}

else

{

lblStatus.Text = "<b style='color:red'>User Details Updation Faild</b>";

}

catch (Exception ex)

{

Response.Write("Oops! error occured :" + ex.Message.ToString()); ;

}

Output:

After Filled the all fields

All the best----------------

Saturday, September 5, 2015

No comments:

C# Corner status