 |
| Click on image to enlarge |
I am assuming that you have setup the 3-tier project after reading the above mentioned article.
So now our next step is to create the database.
- So create a Sql server database e.g. "BookDb" and in that create a table using the script below:
CREATE TABLE [dbo].[BookDetails]
(
[BookId] [int] IDENTITY(1,1) NOT NULL,
[BookName] [varchar](100),
[Author] [varchar](100),
[Publisher] [varchar](200),
[Price] [decimal](18, 2) NOT NULL
)
- Now create the stored procedure to insert the book details in the table.
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
- Create a stored procedure to update the book detail
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
- Create a stored procedure to delete book record
CREATE PROCEDURE [dbo].[DeleteBookRecords_Sp]
@BookId INT
AS
BEGIN
DELETE FROM BookDetails WHERE BookId=@BookId
END
- Create a stored procedure to delete book record
CREATE PROCEDURE [dbo].[FetchBookRecords_Sp]
AS
BEGIN
SELECT * FROM BookDetails
END
- Now we 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>
<add name="conStr" connectionString="Data Source=localhost;Initial Catalog=BooksDb;Integrated Security=True"/>
</connectionStrings>
Now database part is done. Now it's time to write the code for each layer.
Now database part is done. Now it's time to write the code for each layer.
Create Class In BEL
- So let's create an entity/ property class in BEL.
- Right click on the "BEL_BookApp" in the solution explorer -> Add -> New Item -> Select "Class" and name it "BooksDetails_BEL.cs" as shown in image below.
 |
| Click on image to enlarge |
- Create the property for each column in the table "BookDetails". So write the code in BooksDetails_BEL.cs as:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BEL_BookApp
{
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; }
}
}
Create Class in DAL
Create Class in DAL
- Now we need to create a class in DAL to perform database operations.
- So right click on the "DAL_BookApp" in the solution explorer -> Add -> New Item -> Select "Class" and name it "BooksDetails_DAL.cs" as shown in image below.
 |
| Click on image to enlarge |
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using BEL_BookApp;
namespace DAL_BookApp
{
public class BooksDetails_DAL
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].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();
}
}
}
}
}
Create Class in BLL
Create Class in BLL
- Now we need to create a class that act as a bridge between Presentation tier 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 "BLL_BookApp" in the solution explorer -> Add -> New Item -> Select "Class" and name it "BooksDetails_BLL.cs" as shown in image below.
 |
| Click on image to enlarge |
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BEL_BookApp;
using DAL_BookApp;
namespace BLL_BookApp
{
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
{
return objDal.GetBookRecords();
}
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;
}
}
}
}
Create page in Presentation Tier
Create page in Presentation Tier
- Now let's design the front end i.e. the page in presentation tier from where user will input the book details.
- So right click on the "Presentation_BookApp" > Add -> Add New Item -> select Web Form and name it "bookdetails.aspx"
- In the <Form> tag of the Design page (.aspx) design the page using Html source code as:
<div>
<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/edit.jpg" CommandName="Edit" 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" 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>
Note: Create a folder in root directory of the project "Presentation_BookApp" and name it "Images" and search icon images for edit and delete from Google and paste in this folder. These images will be used to display the edit and delete option in gridview.
Asp.Net C# Code
- In the code behind file (bookdetails.cs) write the code as:
#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_BookApp;
using BLL_BookApp;
#endregion
namespace Presentation_BookApp
{
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
}
}
No comments:
Post a Comment