Yesterday, I set myself to develop a simple web application and targeted an hour for it. I used VS 2010 for my development purpose.
The application will do the following:
- List a set of users
- List a set of books
- Perform a simple search on the Users vs. Books borrowed (through IDs)
- Allow users to borrow & return books. Entries to be made accordingly
The output turned out to be something like this:
The following three layers were considered:
- Database
- Data Access Layer
- UI/Presentation Layer
For brevity, I chose to ignore the Business Layer and any Service Layer.
Database Layer
Here I created a DB called Library and added three tables with the following constraints. The image below is self explanatory as to what each of these table do.
Data Access Layer
This was going to be a class library. I could have chosen to have this merged with the Web Application. But, thought adding some amount of modularizing won't hurt.
Here I chose to add an ADO.Net Entity Data Model. This will give a data access view of the tables in your .Net application. Follow the steps below to create it:
Add New Item on the DAL > Visual Studio C# Items > Data > ADO.Net Entity Data Model > Specify a valid Model name say, LibraryModel.edmx and go to the next page.
Select Generate from Database and go to the next page.
Select the Database connection and rest leave it with defaults. You may want to change the Identifier for the connection string that will go to App.Config/Web.Config. Say, LibraryEntities
Select the Tables you would want to make it visible in the .Net application and specify the Model namespace say, LibraryModel and click on Finish.
Your class library should look something like this:
Now, I simply created a simple Facade to wrap some operations of the DB operations. I called that class as LibraryManager.cs
This class used Linq to Entities for faster development towards the tables.
The file looked something like this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccess
{
public class LibraryManager
{
private LibraryEntities dbContext;
public LibraryManager()
{
dbContext = new LibraryEntities();
}
public List GetBooksList()
{
var query = from p in dbContext.tblBooks
select p;
return query.ToList();
}
public List GetUsersList()
{
var query = from p in dbContext.tblUsers
select p;
return query.ToList();
}
public bool AddBook(tblBook book)
{
dbContext.AddTotblBooks(book);
dbContext.SaveChanges();
return true;
}
public bool AddUser(tblUser user)
{
dbContext.AddTotblUsers(user);
dbContext.SaveChanges();
return true;
}
public bool BorrowBook(int nBookId, int nUserId, DateTime date)
{
tblUserBook userBook = new tblUserBook();
userBook.nBookId = nBookId;
userBook.nUserId = nUserId;
userBook.sBorrowedDate = date;
dbContext.AddTotblUserBooks(userBook);
dbContext.SaveChanges();
return true;
}
public List SearchBorrowedItemsByBookAndUser(int nBookId, int nUserId)
{
var query = from p in dbContext.tblUserBooks
where (p.nBookId == nBookId && p.nUserId == nUserId)
select p;
return query.ToList();
}
public bool ReturnBook(int nUserBookId, DateTime date)
{
tblUserBook userBook = dbContext.tblUserBooks.Single(p => p.nId == nUserBookId);
userBook.sReturnedDate = date;
dbContext.SaveChanges();
return true;
}
}
}
UI/Presentation Layer
Here I just created a simple Web Application and added a new item "Web Form using a master page" called User.aspx which is tied to the default master page.
In order to access the Database through the Entity Framework, you need to have the connection string set in your web.config.
Copy the connection string from the App.config of your Data Access Layer to your web.config
User.aspx had the following controls
Grid views to show a list of Users, Books & User vs Books Search results
Other simple controls to perform Borrow and Return book feature
Important to highlight is to see how easy it is to bind the List returned from DAL into the Grid.
Observe the ones highlighted in bold below.
Users.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Users.aspx.cs" Inherits="Library.Users" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<asp:GridView ID="gvUsers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="ID" DataField="nId" />
<asp:BoundField HeaderText="Name" DataField="sName" />
</Columns>
</asp:GridView>
<br />
<asp:GridView ID="gvBooks" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="ID" DataField="nId" />
<asp:BoundField HeaderText="Name" DataField="sName" />
<asp:BoundField HeaderText="ISBN" DataField="sISBN" />
<asp:BoundField HeaderText="Is Active" DataField="bActive" />
</Columns>
</asp:GridView>
<br />
<asp:Label ID="lblBorrowMessage" runat="server" Text=""></asp:Label>
<br />
<table>
<tr>
<td><asp:Label ID="lblSearchBookId" runat="server" Text="BookId"></asp:Label></td>
<td><asp:TextBox ID="txtSearchBookId" runat="server"></asp:TextBox></td>
<td><asp:Label ID="lblSearchUserId" runat="server" Text="UserId"></asp:Label></td>
<td><asp:TextBox ID="txtSearchUserId" runat="server"></asp:TextBox></td>
<td><asp:Button ID="btnSearchBookUserId" runat="server" Text="Search"
onclick="btnSearchBookUserId_Click" /></td>
<td><asp:Button ID="btnBorrowBook" runat="server" Text="Borrow" onclick="btnBorrowBook_Click"
/></td>
</tr>
</table>
<br />
<asp:GridView ID="gvBookUserId" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="ID" DataField="nId" />
<asp:BoundField HeaderText="Borrowed Date" DataField="sBorrowedDate" />
<asp:BoundField HeaderText="Returned Date" DataField="sReturnedDate" />
</Columns>
</asp:GridView>
<br />
<asp:Label ID="lblReturnMessage" runat="server" Text=""></asp:Label>
<br />
<table>
<tr>
<td><asp:Label ID="lblReturnBook" runat="server" Text="BookUserId"></asp:Label></td>
<td><asp:TextBox ID="txtBookUserId" runat="server"></asp:TextBox></td>
<td><asp:Button ID="btnReturnBook" runat="server" Text="Return"
onclick="btnReturnBook_Click"/></td>
</tr>
</table>
<br />
</asp:Content>
Users.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DataAccess;
namespace Library
{
public partial class Users : System.Web.UI.Page
{
private LibraryManager libraryMgr;
protected void Page_Load(object sender, EventArgs e)
{
libraryMgr = new LibraryManager();
gvUsers.DataSource = libraryMgr.GetUsersList();
gvUsers.DataBind();
gvBooks.DataSource = libraryMgr.GetBooksList();
gvBooks.DataBind();
lblBorrowMessage.ForeColor = System.Drawing.Color.Black;
lblBorrowMessage.Text = "";
}
protected void btnSearchBookUserId_Click(object sender, EventArgs e)
{
int nBookId = Convert.ToInt32(txtSearchBookId.Text.Trim());
int nUserId = Convert.ToInt32(txtSearchUserId.Text.Trim());
gvBookUserId.DataSource = libraryMgr.SearchBorrowedItemsByBookAndUser(nBookId, nUserId);
gvBookUserId.DataBind();
}
protected void btnBorrowBook_Click(object sender, EventArgs e)
{
int nBookId = Convert.ToInt32(txtSearchBookId.Text.Trim());
int nUserId = Convert.ToInt32(txtSearchUserId.Text.Trim());
if (libraryMgr.BorrowBook(nBookId, nUserId, DateTime.Now))
{
lblBorrowMessage.Text = @"Data has been successfully recorded. Have fun reading.";
lblBorrowMessage.ForeColor = System.Drawing.Color.Green;
}
else
{
lblBorrowMessage.Text = @"There was an error while trying to Borrow the Book.
Contact the Administrator.";
lblBorrowMessage.ForeColor = System.Drawing.Color.Red;
}
}
protected void btnReturnBook_Click(object sender, EventArgs e)
{
int nBookUserId = Convert.ToInt32(txtBookUserId.Text.Trim());
if (libraryMgr.ReturnBook(nBookUserId, DateTime.Now))
{
lblReturnMessage.Text = @"Data has been successfully recorded. Hope you had fun reading.";
lblReturnMessage.ForeColor = System.Drawing.Color.Green;
}
else
{
lblReturnMessage.Text = @"There was an error while trying to Return the Book.
Contact the Administrator.";
lblReturnMessage.ForeColor = System.Drawing.Color.Red;
}
}
}
}
Just to ramp up, things that helped me in achieving my one hour target was:
- Using Mangement Studio for SQL Server 2008
- Creating ADO.Net Entity Data Model
- Using LINQ to do CRUD on the table data
- Using Grids and Binding data
Two links worth mentioning are:
http://www.telerik.com/help/silverlight/consuming-data-linq-to-ado-net-entity-data-model.html
http://www.mikepope.com/blog/DisplayBlog.aspx?permalink=1419&count=no
Well just a little more than a hour, i was able to develop what I set out for. So Mission accomplished :)
