Working With Stored Procedures Using Entity Framework: Part 2

Introduction

I have started an article series. In the first part: Working With Stored Procedures Using Entity Framework: Part 1, we saw how to work with Stored Procedures using the Entity Framework 6. In that article, I created an ASP.NET Library in the ASP.NET Web Application, added an ADO.NET Entity Data Model and we can access the database using a class.

In this article, we will learn to add a new Web Forms Project Template, and using that library we will access the database. So, let's begin with the step-by-step procedure and follow the sections given below:

Adding Web Forms Project Template
Working with Web Application

Adding Web Forms Project Template

At first, we need to have the Project Template to work in the application. So follow the procedure given below:

Step 1

Right-click on the Solution and go to Add a New Project.

Step 2

Select ASP.NET Web Application and after entering the name, select the Web Forms Project Template and click OK.

Step 3

Now set the second project as a Startup Project. Add a new folder named College to the project. Now you can see that there are projects available in the Solution Explorer.

Working with Web Application

In this section, we'll create the web application. We will do the paging and sorting functionality in the ListView. We will also export the data to Excel and PDF files. To export to a PDF file, we need to get a reference for the iTextSharp.dll file. You can download it.

Doing the Read Operation

In this section, we will create the list view in the web form and access the college list in it. So use the following procedure.

Step 1

First, add the reference of the library and iTextSharp to the project.

Step 2

In the College folder, add a new page named CollegeDetails and design it with the following code:

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
<h2>Colleges List</h2>
<div class="user">
<asp:LinkButton runat="server" CssClass="btn btn-info leftButton" PostBackUrl="~/College/AddCollegeDetails.aspx" Text="Add New College" />
<asp:Button ID="ExportToPdf" Text="ExportToPdf" runat="server" OnClick="ExportToPdf_Click" CssClass="btn btn-info" style="float: right;" />
<asp:Button ID="ExportToExcel" Text="ExportToExcel" runat="server" OnClick="ExportToExcel_Click" CssClass="btn btn-info" style=" float: right; margin:0 14px; "/>
</div>
<div class="clear"></div>
<div class="page">
<asp:ListView runat="server" ID="CollegeDetailsView"
DataKeyNames="CollegeID" ItemType="CollegeDataLibrary.CollegeDetail"
AutoGenerateColumns="false" ItemPlaceholderID="CollegeItem"
AllowPaging="true" AllowSorting="true" SelectMethod="GetData">
<EmptyDataTemplate>
There are no entries found for Colleges
</EmptyDataTemplate>
<LayoutTemplate>
<table class="table">
<thead>
<tr>
<th>
<asp:LinkButton ID="LblCollegeName" CommandArgument="CollegeName" CommandName="Sort" Text="College Name" runat="server" /></th>
<th>Contact Person</th>
<th>Phone No</th>
<th>Email ID</th>
<th>State</th>
<th>City</th>
<th>
<asp:LinkButton ID="LblFirstVisit" CommandArgument="FirstVisitDate" CommandName="Sort" Text="First Visit" runat="server" /></th>
<th>Created By</th>
<th> </th>
</tr>
</thead>
<tbody>
<asp:PlaceHolder runat="server" ID="CollegeItem"></asp:PlaceHolder>
</tbody>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<td>
<asp:DynamicControl runat="server" DataField="CollegeName" ID="CollegeName" />
</td>
<td>
<asp:DynamicControl runat="server" DataField="ContactPerson" ID="ContactPerson" />
</td>
<td>
<asp:DynamicControl runat="server" DataField="ContactPersonPhoneNo" ID="ContactPersonPhoneNo" />
</td>
<td>
<asp:DynamicControl runat="server" DataField="ContactPersonEmailID" ID="ContactPersonEmailID" />
</td>
<td>
<asp:DynamicControl runat="server" DataField="State" ID="State" />
</td>
<td>
<asp:DynamicControl runat="server" DataField="City" ID="City" />
</td>
<td>
<asp:DynamicControl runat="server" DataField="FirstVisitDate" ID="FirstVisitDate" />
</td>
<td>
<asp:DynamicControl runat="server" DataField="CreatedBy" ID="CreatedBy" />
</td>
<td>
<asp:HyperLink ID="EditLink" runat="server" NavigateUrl='<%# "EditCollegeDetails.aspx?ID=" + Eval("CollegeID") %>'>Edit</asp:HyperLink>
|
<asp:HyperLink ID="DetailsLink" runat="server" NavigateUrl='<%# "ManageCollegeDetails.aspx?ID=" + Eval("CollegeID") %>'>Details</asp:HyperLink>
|
<asp:HyperLink ID="DeleteLink" runat="server" NavigateUrl='<%# "DeleteCollegeDetails.aspx?ID=" + Eval("CollegeID") %>'>Delete</asp:HyperLink>
</td>
</tr>
</ItemTemplate>
</asp:ListView>
<asp:DataPager ID="DataPager1" runat="server" PagedControlID="CollegeDetailsView"
PageSize="10">
<Fields>
<asp:NumericPagerField ButtonCount="3" />
</Fields>
</asp:DataPager>
</div>
</asp:Content>

Step 3

In the code page, replace the code with the following code:

using CollegeDataLibrary;
using System;
using System.Collections.Generic;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web;
namespace CollegeDetailsApplication
{
public partial class CollegeDetails : System.Web.UI.Page
{
CollegeDataOperations DataOperations = new CollegeDataOperations();
CollegeDetail detail = new CollegeDetail();
protected void Page_Load(object sender, EventArgs e)
{
}
//This method is used to get the college details
//Created on 20th June
public IQueryable<CollegeDataLibrary.CollegeDetail> GetData()
{
var context = DataOperations.GetCollegeDetails();
return context.AsQueryable();
}
protected void ExportToExcel_Click(object sender, EventArgs e)
{
string File = "CollegeDetails";
var result = GetData().ToList();
ExportIntoExcel(result, File);
}
public void ExportIntoExcel(List<CollegeDetail> result, string FileName)
{
StringWriter writer = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(writer);
GridView gridView = new GridView();
gridView.DataSource = result;
gridView.AutoGenerateColumns = true;
gridView.DataBind();
gridView.RenderControl(htmlWriter);
htmlWriter.Close();
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + FileName + ".xls");
Response.Charset = "";
Response.Write(writer.ToString());
Response.End();
}
protected void ExportToPdf_Click(object sender, EventArgs e)
{
string File = "PDFCollegeDetails";
var result = DataOperations.GetCollege();
ExportListToPDF(result, File);
}
private void ExportListToPDF(List<CollegeData> result, string File)
{
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "attachment;filename=CollegeDetailsInPdf.pdf");
Response.Cache.SetCacheability(HttpCacheability.NoCache);
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView gridView = new GridView();
gridView.DataSource = result;
gridView.DataBind();
gridView.RenderControl(hw);
gridView.HeaderRow.Style.Add("width", "100%");
gridView.HeaderRow.BackColor = System.Drawing.Color.Green;
gridView.HeaderRow.Style.Add("color", "green");
gridView.Style.Add("text-decoration", "none");
gridView.Style.Add("font-family", "Arial, Helvetica, sans-serif;");
gridView.Style.Add("font-size", "8px");
StringReader sr = new StringReader(sw.ToString());
Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
Response.Write(pdfDoc);
Response.End();
gridView.AutoGenerateColumns = true;
}
}
}

In the preceding web forms code, we can export the list view data to the PDF file as well as to the Excel file.

Step 4

After running the application, you can see the data as shown in the following browser:

Doing the Create Operation

We have done the Read Operation, now we will do the create operation. So use the following procedure.

Step 1

Add a new page to the College folder named AddCollegeDetails. Design the page as shown below:

Step 2

Paste the following code in the AddCollegeDetails.cs page:

using System;
using CollegeDataLibrary;
using System.Web.UI.WebControls;
namespace CollegeDetailsApplication
{
public partial class AddCollegeDetails : System.Web.UI.Page
{
CollegeDataOperations DataOperations = new CollegeDataOperations();
CollegeDetail collegeDetail = new CollegeDetail();
string AlbumFile, FeedbackFile;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Fill_States();
}
}
public void Fill_States()
{
State.DataSource = DataOperations.GetStates();
State.DataTextField = "State_Name";
State.DataValueField = "State_ID";
State.DataBind();
State.Items.Insert(0, new ListItem("--Select--", "0"));
}
public void Fill_City()
{
City.DataSource = DataOperations.GetCity(Convert.ToInt32(State.Text));
City.DataTextField = "City_Name";
City.DataValueField = "CIty_ID";
City.DataBind();
City.Items.Insert(0, new ListItem("--Select--", "0"));
}
protected void AddCollege_Click(object sender, EventArgs e)
{
InsertDetails();
}
void InsertDetails()
{
//This code is used to add the college details to the database table College Details
try{
if (IsValid)
{
collegeDetail.CollegeName = CollegeName.Text;
collegeDetail.ContactPerson = ContactPerson.Text;
collegeDetail.ContactPersonPhoneNo = Convert.ToInt64(ContactPersonPhoneNo.Text == string.Empty ? "0" : ContactPersonPhoneNo.Text.ToString());
collegeDetail.ContactPersonEmailID = ContactPersonEmailID.Text;
collegeDetail.FirstVisitDate = DateTime.Parse(FirstVisitDate.Text);
collegeDetail.State = State.SelectedItem.ToString();
collegeDetail.City = City.SelectedItem.ToString();
collegeDetail.CreatedBy = CreatedBy.SelectedItem.Value;
collegeDetail.Album = AlbumFile;
collegeDetail.Feedback = FeedbackFile;
var result = DataOperations.InsertCollegeDetails(collegeDetail);
if (result == true)
{
Response.Redirect("~/College/CollegeDetails.aspx");
}
else
{
ErrorMessage.Text = "Data Do Not Inserted ";
}
}
}
catch(Exception ex)
{
ErrorMessage.Text = ex.Message;
}
}
protected void State_SelectedIndexChanged(object sender, EventArgs e)
{
Fill_City();
}
}
}

From the code above, we can successfully add the college to the database.

Do the Edit Operation

In this section, we'll do the update operation. So follow the procedure below.

Step 1

At first, add the EditCollegeDetails page in the College folder. Design the page as shown below:

Step 2

When the user clicks on the Edit link, the information of that specific college will bind to the next Edit Page, then we can update the information. Now replace the edit page cs code with the following code:

using CollegeDataLibrary;
using System;
using System.Web.ModelBinding;
using System.Web.UI.WebControls;
namespace CollegeDetailsApplication
{
public partial class EditCollegeDetails : System.Web.UI.Page
{
CollegeDataOperations DataOperations = new CollegeDataOperations();
CollegeDetail detail = new CollegeDetail();
string AlbumFile, FeedbackFile;
protected void Page_Load(object sender, EventArgs e)
{
}
// This is the Select method to selects a single College item with the id
public CollegeDetail GetItem([QueryString]int? ID)
{
if (ID == null)
{
return null;
}
var context = DataOperations.GetCollegeData(ID);
return context;
}
// This is the ItemCommand method which works on Cancel
protected void ItemCommand(object sender, FormViewCommandEventArgs e)
{
if (e.CommandName.Equals("Cancel", StringComparison.OrdinalIgnoreCase))
{
Response.Redirect("~/College/CollegeDetails.aspx");
}
}
// This is the Update methd to update the selected College item
public void UpdateItem([QueryString]int? ID)
{
var item = DataOperations.GetCollegeData(ID);
if (item == null)
{
// The item wasn't found
ModelState.AddModelError("", String.Format("Item with id {0} was not found", ID));
return;
}
if (ModelState.IsValid)
{
try
{
TextBox College_Name = CollegeFormView.FindControl("CollegeName") as TextBox;
TextBox Contact_Person = CollegeFormView.FindControl("ContactPerson") as TextBox;
TextBox ContactPerson_PhoneNo = CollegeFormView.FindControl("ContactPersonPhoneNo") as TextBox;
TextBox ContactPerson_EmailID = CollegeFormView.FindControl("ContactPersonEmailID") as TextBox;
DropDownList CreatedBy_Name = CollegeFormView.FindControl("CreatedBy") as DropDownList;
detail.CollegeName = College_Name.Text;
detail.ContactPerson = Contact_Person.Text;
detail.ContactPersonPhoneNo = Convert.ToInt64(ContactPerson_PhoneNo.Text);
detail.ContactPersonEmailID = ContactPerson_EmailID.Text;
detail.CreatedBy = CreatedBy_Name.Text;
detail.Album = AlbumFile;
detail.Feedback = FeedbackFile;
detail.EditedBy = Session["UserName"].ToString();
DataOperations.UpdateCollegeDetails(ID, detail);
Response.Redirect("~/College/CollegeDetails.aspx");
}
catch (Exception ex)
{
ErrorMessage.Text = ex.Message;
}
}
}
}
}

Step 3

Suppose we have clicked on the first record, the following Edit Page will show the information of the first record and we can update the data.

Do the Delete Operation

In this section, we will do the delete operation. Use the procedure given below.

Step 1

At first add the DeleteCollegeDetails page. Paste in the following code:

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
<div>
<p> </p>
<h3>Are you sure want to delete this College?</h3>
<asp:FormView runat="server" ID="DeleteDetail"
ItemType="CollegeDataLibrary.CollegeDetail" DefaultMode="Edit" DataKeyNames="CollegeID"
DeleteMethod="DeleteItem" SelectMethod="GetItem"
OnItemCommand="ItemCommand" RenderOuterTable="false">
<EmptyDataTemplate>
Cannot find the College with ID <%: Request.QueryString["ID"] %>
</EmptyDataTemplate>
<EditItemTemplate>
<div class="form-horizontal">
<h4>Delete College</h4>
<hr />
<asp:ValidationSummary runat="server" CssClass="text-danger" />
<div class="form-group">
<asp:Label runat="server" AssociatedControlID="CollegeName" CssClass="col-md-2 control-label">College Name</asp:Label>
<div class="col-md-10">
<asp:Label runat="server" ID="CollegeName" Text='<%#Bind("CollegeName") %>' CssClass="col-md-2 control-label"></asp:Label>
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" CssClass="btn btn-danger" />
<asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" CssClass="btn btn-info" />
</div>
</div>
</div>
</EditItemTemplate>
</asp:FormView>
</div>
</asp:Content>

Step 2

Paste the following code in the code page:

using CollegeDataLibrary;
using System;
using System.Web.ModelBinding;
using System.Web.UI.WebControls;
namespace CollegeDetailsApplication
{
public partial class DeleteCollegeDetails : System.Web.UI.Page
{
CollegeDataOperations DataOperations = new CollegeDataOperations();
protected void Page_Load(object sender, EventArgs e)
{
}
// This is the Select method to selects a single College item with the id
public CollegeDetail GetItem([QueryString]int? ID)
{
if (ID == null)
{
return null;
}
var context = DataOperations.GetCollegeData(ID);
return context;
}
// This is the Delete methd to delete the selected College item
public void DeleteItem([QueryString]int? ID)
{
var item = DataOperations.GetCollegeData(ID);
if (item != null)
{
DataOperations.DeleteCollegeDetails(ID);
}
Response.Redirect("~/College/CollegeDetails.aspx");
}
// This is the ItemCommand method which works on Cancle
protected void ItemCommand(object sender, FormViewCommandEventArgs e)
{
if (e.CommandName.Equals("Cancel", StringComparison.OrdinalIgnoreCase))
{
Response.Redirect("~/College/CollegeDetails.aspx");
}
}
}
}

Step 3

When you click on the Delete link on any record, the following page will open and you can delete the record.

That's it.

Summary

This article described how to use the Entity Framework code in the web application and do the CRUD operation. In the next part, we'll see how to manage the college details.

Happy Coding!!

3 Comments

Join the conversation! Your thoughts help the community grow.

Gowtham Rajamanickam11y ago
this is great..
Yogesh Karale12y ago
it's good to creating gridView in ASP.Net..... but same thing used MVC3 how
- Nimit Joshi12y ago
  Hey I have used List View in here and this application is created with Web Forms Project Template and i have already mentioned it. I'll post an article in mvc soon. Thanks for reading.