FIROZ DOT NET: Q.Crud Operation Using Three Tier In asp.net

connection in web.cofig

<add name="ConStr" connectionString="Data Source=INDIA;Database=Firoz_Sample_Database;User id=sa;Password=123;" providerName="System.Data.SqlClient" />

Table:-

CREATE TABLE [dbo].[BookDetails](
[BookId] [int] IDENTITY(1,1) NOT NULL,
[BookName] [varchar](100) NULL,
[Author] [varchar](100) NULL,
[Publisher] [varchar](200) NULL,
[Price] [decimal](18, 2) NOT NULL

) ON [PRIMARY]

Store Procedures:-

CREATE PROCEDURE [dbo].[DeleteBookRecords_Sp]
@BookId INT
AS
BEGIN
DELETE FROM BookDetails WHERE BookId=@BookId
END

/****** Object: StoredProcedure [dbo].[FetchBookRecords_Sp] Script Date: 12-Jun-18 11:47:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchBookRecords_Sp]
AS
BEGIN
SELECT * FROM BookDetails
END

/****** Object: StoredProcedure [dbo].[InsertBookDetails_SP] Script Date: 12-Jun-18 11:47:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertBookDetails_SP]
@BookName VARCHAR(100),
@Author VARCHAR(100),
@Publisher VARCHAR(200),
@Price DECIMAL(18,2)
AS
BEGIN
INSERT INTO BookDetails
(
BookName,Author,Publisher,Price
)
VALUES
(
@BookName,@Author,@Publisher,@Price
)
END

/****** Object: StoredProcedure [dbo].[UpdateBookRecord_SP] Script Date: 12-Jun-18 11:47:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateBookRecord_SP]
@BookId INT,
@BookName VARCHAR(100),
@Author VARCHAR(100),
@Publisher VARCHAR(200),
@Price DECIMAL(18,2)
AS
BEGIN
UPDATE BookDetails SET
BookName=@BookName,
Author=@Author,
Publisher=@Publisher,
Price=@Price
WHERE BookId=@BookId

END

.ASPX

Note:-Add Three Class Library one for Entity second for Business Logic and Data access layer
Note:- Right Click on Slolution->Select-> Add->Select New Project->Select Class Library

Note-> Put Name like BEL,DAL AND BLL

First of all create BEL ex

public class BooksDetails_BEL
{
public int BookId { get; set; }
public string BookName { get; set; }
public string Author { get; set; }
public string Publisher { get; set; }
public decimal Price { get; set; }

}

After that build the solutions and add rferences in DAL.Now i am going to create Data accesslayer see below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using BEL;
namespace DAL
{
public class BooksDetails_DAL
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["PnG_AQUAConnection"].ConnectionString);
public Int32 SaveBookDetails(BooksDetails_BEL objBEL)
{
int result;
try
{
SqlCommand cmd = new SqlCommand("InsertBookDetails_SP", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BookName", objBEL.BookName);
cmd.Parameters.AddWithValue("@Author", objBEL.Author);
cmd.Parameters.AddWithValue("@Publisher", objBEL.Publisher);
cmd.Parameters.AddWithValue("@Price", objBEL.Price);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
result = cmd.ExecuteNonQuery();
cmd.Dispose();
if (result > 0)
{
return result;
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}

public DataSet GetBookRecords()
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand("FetchBookRecords_Sp", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
cmd.Dispose();
}
catch (Exception ex)
{
throw;
}
finally
{
ds.Dispose();
}
return ds;
}

public Int32 DeleteBookRecord(BooksDetails_BEL objBEL)
{
int result;
try
{
SqlCommand cmd = new SqlCommand("DeleteBookRecords_Sp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BookId", objBEL.BookId);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
result = cmd.ExecuteNonQuery();
cmd.Dispose();
if (result > 0)
{
return result;
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}

public Int32 UpdateBookRecord(BooksDetails_BEL objBEL)
{
int result;
try
{
SqlCommand cmd = new SqlCommand("UpdateBookRecord_SP", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BookId", objBEL.BookId);
cmd.Parameters.AddWithValue("@BookName", objBEL.BookName);
cmd.Parameters.AddWithValue("@Author", objBEL.Author);
cmd.Parameters.AddWithValue("@Publisher", objBEL.Publisher);
cmd.Parameters.AddWithValue("@Price", objBEL.Price);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
result = cmd.ExecuteNonQuery();
cmd.Dispose();
if (result > 0)
{
return result;
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
}

}

Note :-Now Create BLL class and here we are adding BEL and DAL References.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using BEL;

using DAL;

using System.Data;

namespace BLL

{

public class BookDetails_BLL

{

public Int32 SaveBookDetails(BooksDetails_BEL objBel)

{

BooksDetails_DAL objDal = new BooksDetails_DAL();

try

{

return objDal.SaveBookDetails(objBel);

}

catch (Exception ex)

{

throw;

}

finally

{

objDal = null;

}

public DataSet GetBookRecords()

{

BooksDetails_DAL objDal = new BooksDetails_DAL();

try

{

DataSet ds= objDal.GetBookRecords();

return ds;

}

catch (Exception ex)

{

throw;

}

finally

{

objDal = null;

}

public Int32 DeleteBookRecord(BooksDetails_BEL objBel)

{

BooksDetails_DAL objDal = new BooksDetails_DAL();

try

{

return objDal.DeleteBookRecord(objBel);

}

catch (Exception ex)

{

throw;

}

finally

{

objDal = null;

}

public Int32 UpdateBookRecord(BooksDetails_BEL objBel)

{

BooksDetails_DAL objDal = new BooksDetails_DAL();

try

{

return objDal.UpdateBookRecord(objBel);

}

catch (Exception ex)

{

throw;

}

finally

{

objDal = null;

}

now create UI Page Like .ASPX

<form id="form1" runat="server">
<div align="center">
<fieldset style="width: 470px">
<legend>3 tier example to insert and bind book details</legend>
<table>
<tr>
<td>Book Name * : </td>
<td>
<asp:TextBox ID="txtBookName" runat="server"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="rfvBookName" runat="server"
ErrorMessage="Book Name can't be left blank" ControlToValidate="txtBookName"
Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Author * : </td>
<td>
<asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="rfvAuthor" runat="server"
ErrorMessage="Author Name can't be left blank" ControlToValidate="txtAuthor"
Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Publisher * : </td>
<td>
<asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="rfvPublisher" runat="server"
ErrorMessage="Publisher Name can't be left blank" ControlToValidate="txtPublisher"
Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Price * : </td>
<td>
<asp:TextBox ID="txtPrice" runat="server"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="rfvPrice" runat="server"
ErrorMessage="Price can't be left blank" ControlToValidate="txtPrice"
Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="rgePrice" runat="server"
ControlToValidate="txtPrice" Display="Dynamic"
ErrorMessage="Enter Numeric only" ForeColor="Red" SetFocusOnError="True"
ValidationExpression="^\d*[0-9](|.\d*[0-9]|)*$"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
OnClick="btnSubmit_Click" /></td>
</tr>
<tr>
<td colspan="2">
<asp:Label ID="lblStatus" runat="server" Text=""></asp:Label></td>
</tr>
</table>
<br />
<asp:GridView ID="grdBookDetails" runat="server" DataKeyNames="BookId"
AutoGenerateColumns="False"
OnPageIndexChanging="grdBookDetails_PageIndexChanging"
OnRowCancelingEdit="grdBookDetails_RowCancelingEdit"
OnRowDeleting="grdBookDetails_RowDeleting"
OnRowEditing="grdBookDetails_RowEditing"
OnRowUpdating="grdBookDetails_RowUpdating" AllowPaging="True" PageSize="5"
CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:TemplateField HeaderText="Book Name">
<ItemTemplate>
<asp:Label ID="lblBookName" runat="server" Text='<%#Eval("BookName")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtBookNameEdit" runat="server" Text='<%#Eval("BookName")%>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Author">
<ItemTemplate>
<asp:Label ID="lblAuthor" runat="server" Text='<%#Eval("Author")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtAuthorEdit" runat="server" Text='<%#Eval("Author")%>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Publisher">
<ItemTemplate>
<asp:Label ID="lblPublisher" runat="server" Text='<%#Eval("Publisher")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtPublisherEdit" runat="server" Text='<%#Eval("Publisher")%>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Price">
<ItemTemplate>
<asp:Label ID="lblPrice" runat="server" Text='<%#Eval("Price")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtPriceEdit" runat="server" Text='<%#Eval("Price")%>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:ImageButton ID="imgEdit" runat="server" ImageUrl="~/Images/Custom-Icon-Design-Office-Edit.ico" CommandName="Edit" Height="30" Width="30" CausesValidation="false" />
</ItemTemplate>
<EditItemTemplate>
<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>
<ItemStyle HorizontalAlign="Center"></ItemStyle>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:ImageButton ID="imgDelete" runat="server" ImageUrl="~/Images/delete.jpg" CommandName="Delete" Height="30" Width="30" CausesValidation="false" OnClientClick="return confirm('Are you sure you want to delete?')" />
</ItemTemplate>
<EditItemTemplate>
</EditItemTemplate>
<ItemStyle HorizontalAlign="Center"></ItemStyle>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</fieldset>
</div>

</form>

now go to ASPX.CS

#region "Namespaces"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using BEL;
using BLL;
#endregion

namespace UploadAndDownload
{
public partial class bookdetails : System.Web.UI.Page
{
#region "Create and Initialize objects "
BooksDetails_BEL objBookDetailsBEL = new BooksDetails_BEL();
BookDetails_BLL objBookDetailsBLL = new BookDetails_BLL();
#endregion

#region "Bind Book Records on Page load Event"
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindBookRecordsGridView();
}
}
#endregion

#region "Save Book Record"
protected void btnSubmit_Click(object sender, EventArgs e)
{
objBookDetailsBEL.BookName = txtBookName.Text.Trim();
objBookDetailsBEL.Author = txtAuthor.Text.Trim();
objBookDetailsBEL.Publisher = txtPublisher.Text.Trim();
objBookDetailsBEL.Price = Convert.ToDecimal(txtPrice.Text);
try
{
int retVal = objBookDetailsBLL.SaveBookDetails(objBookDetailsBEL);
if (retVal > 0)
{
lblStatus.Text = "Book detail saved successfully";
lblStatus.ForeColor = System.Drawing.Color.Green;
ClearControls();
BindBookRecordsGridView();
}
else
{
lblStatus.Text = "Book details couldn't be saved";
lblStatus.ForeColor = System.Drawing.Color.Red;
}
}
catch (Exception ex)
{
Response.Write("Oops! error occured :" + ex.Message.ToString());
}
finally
{
objBookDetailsBEL = null;
objBookDetailsBLL = null;
}
}
#endregion

#region "Bind Book Records in GridView"
private void BindBookRecordsGridView()
{
DataSet ds = new DataSet();
try
{
ds = objBookDetailsBLL.GetBookRecords();
if (ds.Tables[0].Rows.Count > 0)
{
grdBookDetails.DataSource = ds;
grdBookDetails.DataBind();
}
else
{
grdBookDetails.DataSource = null;
grdBookDetails.DataBind();
}
}
catch (Exception ex)
{
Response.Write("Oops! error occured :" + ex.Message.ToString());
}
finally
{
objBookDetailsBEL = null;
objBookDetailsBLL = null;
}
}
#endregion

#region "Edit and update Book Records"
protected void grdBookDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
grdBookDetails.EditIndex = e.NewEditIndex;
BindBookRecordsGridView();
}

protected void grdBookDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grdBookDetails.EditIndex = -1;
BindBookRecordsGridView();
}

protected void grdBookDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
objBookDetailsBEL.BookId = Convert.ToInt32(grdBookDetails.DataKeys[e.RowIndex].Value);
objBookDetailsBEL.BookName = ((TextBox)(grdBookDetails.Rows[e.RowIndex].FindControl("txtBookNameEdit"))).Text.Trim();
objBookDetailsBEL.Author = ((TextBox)(grdBookDetails.Rows[e.RowIndex].FindControl("txtAuthorEdit"))).Text.Trim();
objBookDetailsBEL.Publisher = ((TextBox)(grdBookDetails.Rows[e.RowIndex].FindControl("txtPublisherEdit"))).Text.Trim();
objBookDetailsBEL.Price = Convert.ToDecimal(((TextBox)(grdBookDetails.Rows[e.RowIndex].FindControl("txtPriceEdit"))).Text.Trim());
try
{
int retVal = objBookDetailsBLL.UpdateBookRecord(objBookDetailsBEL);
if (retVal > 0)
{
lblStatus.Text = "Book detail updated successfully";
lblStatus.ForeColor = System.Drawing.Color.Green;
ClearControls();
grdBookDetails.EditIndex = -1;
BindBookRecordsGridView();
}
else
{
lblStatus.Text = "Book details couldn't be updated";
lblStatus.ForeColor = System.Drawing.Color.Red;
}
}
catch (Exception ex)
{
Response.Write("Oops! error occured :" + ex.Message.ToString());
}
finally
{
objBookDetailsBEL = null;
objBookDetailsBLL = null;
}
}
#endregion

#region "Delete Book Record"
protected void grdBookDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int Book_Id = Convert.ToInt32(grdBookDetails.DataKeys[e.RowIndex].Value);
objBookDetailsBEL.BookId = Book_Id;
try
{
int retVal = objBookDetailsBLL.DeleteBookRecord(objBookDetailsBEL);

if (retVal > 0)
{
lblStatus.Text = "Book detail deleted successfully";
lblStatus.ForeColor = System.Drawing.Color.Green;
ClearControls();
BindBookRecordsGridView();
}
else
{
lblStatus.Text = "Book details couldn't be deleted";
lblStatus.ForeColor = System.Drawing.Color.Red;
}
}
catch (Exception ex)
{
Response.Write("Oops! error occured :" + ex.Message.ToString());
}
finally
{
objBookDetailsBEL = null;
objBookDetailsBLL = null;
}
}
#endregion

#region "Paging in GridView"
protected void grdBookDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdBookDetails.PageIndex = e.NewPageIndex;
BindBookRecordsGridView();
}
#endregion

#region "Clear/Reset controls "
protected void btnReset_Click(object sender, EventArgs e)
{
ClearControls();
}
private void ClearControls()
{
txtBookName.Text = string.Empty;
txtAuthor.Text = string.Empty;
txtPublisher.Text = string.Empty;
txtPrice.Text = string.Empty;
txtBookName.Focus();
}
#endregion
}

}

Out Put Will be Like That