.NET

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.

Adding 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.

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 List View. 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:

  1. <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">  
  2.     <h2>Colleges List</h2>  
  3.     <div class="user">  
  4.         <asp:LinkButton runat="server" CssClass="btn btn-info leftButton" PostBackUrl="~/College/AddCollegeDetails.aspx" Text="Add New College" />  
  5.         <asp:Button ID="ExportToPdf" Text="ExportToPdf" runat="server" OnClick="ExportToPdf_Click" CssClass="btn btn-info" style="float: right;" />  
  6.         <asp:Button ID="ExportToExcel" Text="ExportToExcel" runat="server" OnClick="ExportToExcel_Click" CssClass="btn btn-info" style=" float: right; margin:0 14px; "/>  
  7.     </div>  
  8.     <div class="clear"></div>  
  9.    <div class="page">  
  10.         <asp:ListView runat="server" ID="CollegeDetailsView"  
  11.             DataKeyNames="CollegeID" ItemType="CollegeDataLibrary.CollegeDetail"  
  12.             AutoGenerateColumns="false" ItemPlaceholderID="CollegeItem"  
  13.             AllowPaging="true" AllowSorting="true" SelectMethod="GetData">  
  14.             <EmptyDataTemplate>  
  15.                 There are no entries found for Colleges  
  16.             </EmptyDataTemplate>  
  17.             <LayoutTemplate>  
  18.                 <table class="table">  
  19.                     <thead>  
  20.                         <tr>  
  21.                             <th>  
  22.                                 <asp:LinkButton ID="LblCollegeName" CommandArgument="CollegeName" CommandName="Sort" Text="College Name" runat="server" /></th>  
  23.                             <th>Contact Person</th>  
  24.                             <th>Phone No</th>  
  25.                             <th>Email ID</th>  
  26.                             <th>State</th>  
  27.                             <th>City</th>  
  28.                             <th>  
  29.                                 <asp:LinkButton ID="LblFirstVisit" CommandArgument="FirstVisitDate" CommandName="Sort" Text="First Visit" runat="server" /></th>  
  30.                             <th>Created By</th>  
  31.                             <th> </th>  
  32.                         </tr>  
  33.                     </thead>  
  34.                     <tbody>  
  35.                         <asp:PlaceHolder runat="server" ID="CollegeItem"></asp:PlaceHolder>  
  36.                     </tbody>  
  37.                 </table>  
  38.             </LayoutTemplate>  
  39.             <ItemTemplate>  
  40.                 <tr>  
  41.                     <td>  
  42.                         <asp:DynamicControl runat="server" DataField="CollegeName" ID="CollegeName" />  
  43.                     </td>  
  44.                     <td>  
  45.                         <asp:DynamicControl runat="server" DataField="ContactPerson" ID="ContactPerson" />  
  46.                     </td>  
  47.                     <td>  
  48.                         <asp:DynamicControl runat="server" DataField="ContactPersonPhoneNo" ID="ContactPersonPhoneNo" />  
  49.                     </td>  
  50.                     <td>  
  51.                         <asp:DynamicControl runat="server" DataField="ContactPersonEmailID" ID="ContactPersonEmailID" />  
  52.                     </td>  
  53.                     <td>  
  54.                         <asp:DynamicControl runat="server" DataField="State" ID="State" />  
  55.                     </td>  
  56.                     <td>  
  57.                         <asp:DynamicControl runat="server" DataField="City" ID="City" />  
  58.                     </td>  
  59.                     <td>  
  60.                         <asp:DynamicControl runat="server" DataField="FirstVisitDate" ID="FirstVisitDate" />  
  61.                     </td>  
  62.                     <td>  
  63.                         <asp:DynamicControl runat="server" DataField="CreatedBy" ID="CreatedBy" />  
  64.                     </td>  
  65.    
  66.                     <td>  
  67.                         <asp:HyperLink ID="EditLink" runat="server" NavigateUrl='<%# "EditCollegeDetails.aspx?ID=" + Eval("CollegeID") %>'>Edit</asp:HyperLink>  
  68.                         |  
  69.                         <asp:HyperLink ID="DetailsLink" runat="server" NavigateUrl='<%# "ManageCollegeDetails.aspx?ID=" + Eval("CollegeID") %>'>Details</asp:HyperLink>  
  70.                         |  
  71.                         <asp:HyperLink ID="DeleteLink" runat="server" NavigateUrl='<%# "DeleteCollegeDetails.aspx?ID=" + Eval("CollegeID") %>'>Delete</asp:HyperLink>  
  72.                     </td>  
  73.                 </tr>  
  74.             </ItemTemplate>  
  75.         </asp:ListView>  
  76.    
  77.         <asp:DataPager ID="DataPager1" runat="server" PagedControlID="CollegeDetailsView"  
  78.             PageSize="10">  
  79.             <Fields>  
  80.                 <asp:NumericPagerField ButtonCount="3" />  
  81.             </Fields>  
  82.         </asp:DataPager>  
  83.     </div>         
  84. </asp:Content>  

 

Step 3

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

  1. using CollegeDataLibrary;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.IO;  
  5. using iTextSharp.text;  
  6. using iTextSharp.text.pdf;  
  7. using iTextSharp.text.html.simpleparser;  
  8. using System.Linq;  
  9. using System.Web.UI;  
  10. using System.Web.UI.WebControls;  
  11. using System.Web;  
  12.    
  13. namespace CollegeDetailsApplication  
  14. {  
  15.     public partial class CollegeDetails : System.Web.UI.Page  
  16.     {  
  17.         CollegeDataOperations DataOperations = new CollegeDataOperations();  
  18.         CollegeDetail detail = new CollegeDetail();  
  19.    
  20.         protected void Page_Load(object sender, EventArgs e)  
  21.         {  
  22.              
  23.         }  
  24.    
  25.         //This method is used to get the college details  
  26.         //Created on 20th June  
  27.         public IQueryable<CollegeDataLibrary.CollegeDetail> GetData()  
  28.         {  
  29.             var context = DataOperations.GetCollegeDetails();  
  30.             return context.AsQueryable();  
  31.         }  
  32.          
  33.         protected void ExportToExcel_Click(object sender, EventArgs e)  
  34.         {  
  35.             string File = "CollegeDetails";  
  36.             var result = GetData().ToList();  
  37.             ExportIntoExcel(result, File);  
  38.         }  
  39.    
  40.         public void ExportIntoExcel(List<CollegeDetail> result, string FileName)  
  41.         {  
  42.             StringWriter writer = new StringWriter();  
  43.             HtmlTextWriter htmlWriter = new HtmlTextWriter(writer);  
  44.             GridView gridView = new GridView();  
  45.             gridView.DataSource = result;  
  46.             gridView.AutoGenerateColumns = true;  
  47.             gridView.DataBind();  
  48.             gridView.RenderControl(htmlWriter);  
  49.             htmlWriter.Close();  
  50.             Response.Clear();  
  51.             Response.AddHeader("content-disposition", "attachment;filename=" + FileName + ".xls");  
  52.             Response.Charset = "";  
  53.             Response.Write(writer.ToString());  
  54.             Response.End();  
  55.         }  
  56.    
  57.         protected void ExportToPdf_Click(object sender, EventArgs e)  
  58.         {  
  59.             string File = "PDFCollegeDetails";  
  60.             var result = DataOperations.GetCollege();  
  61.             ExportListToPDF(result, File);  
  62.         }  
  63.    
  64.         private void ExportListToPDF(List<CollegeData> result, string File)  
  65.         {  
  66.             Response.ContentType = "application/pdf";  
  67.             Response.AddHeader("content-disposition", "attachment;filename=CollegeDetailsInPdf.pdf");  
  68.             Response.Cache.SetCacheability(HttpCacheability.NoCache);  
  69.             StringWriter sw = new StringWriter();  
  70.             HtmlTextWriter hw = new HtmlTextWriter(sw);  
  71.             GridView gridView = new GridView();  
  72.             gridView.DataSource = result;  
  73.             gridView.DataBind();  
  74.             gridView.RenderControl(hw);  
  75.             gridView.HeaderRow.Style.Add("width", "100%");  
  76.             gridView.HeaderRow.BackColor = System.Drawing.Color.Green;  
  77.             gridView.HeaderRow.Style.Add("color", "green");  
  78.             gridView.Style.Add("text-decoration", "none");  
  79.             gridView.Style.Add("font-family", "Arial, Helvetica, sans-serif;");  
  80.             gridView.Style.Add("font-size", "8px");  
  81.             StringReader sr = new StringReader(sw.ToString());  
  82.             Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);  
  83.             HTMLWorker htmlparser = new HTMLWorker(pdfDoc);  
  84.             PdfWriter.GetInstance(pdfDoc, Response.OutputStream);  
  85.             pdfDoc.Open();  
  86.             htmlparser.Parse(sr);  
  87.             pdfDoc.Close();  
  88.             Response.Write(pdfDoc);  
  89.             Response.End();  
  90.             gridView.AutoGenerateColumns = true;  
  91.         }  
  92.     }  
  93. }  

 

In the preceding web forms code, we can export the list view data to the 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:

College List View

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:

Add New College View

Step 2

Paste the following code in the AddCollegeDetails.cs page:

  1. using System;  
  2. using CollegeDataLibrary;  
  3. using System.Web.UI.WebControls;  
  4.    
  5. namespace CollegeDetailsApplication  
  6. {  
  7.     public partial class AddCollegeDetails : System.Web.UI.Page  
  8.     {  
  9.         CollegeDataOperations DataOperations = new CollegeDataOperations();  
  10.         CollegeDetail collegeDetail = new CollegeDetail();  
  11.         string AlbumFile, FeedbackFile;  
  12.         protected void Page_Load(object sender, EventArgs e)  
  13.         {            
  14.             if (!IsPostBack)  
  15.             {  
  16.                 Fill_States();  
  17.             }  
  18.         }  
  19.    
  20.         public void Fill_States()  
  21.         {  
  22.             State.DataSource = DataOperations.GetStates();  
  23.             State.DataTextField = "State_Name";  
  24.             State.DataValueField = "State_ID";  
  25.             State.DataBind();  
  26.    
  27.             State.Items.Insert(0, new ListItem("--Select--", "0"));  
  28.         }  
  29.    
  30.         public void Fill_City()  
  31.         {  
  32.             City.DataSource = DataOperations.GetCity(Convert.ToInt32(State.Text));  
  33.             City.DataTextField = "City_Name";  
  34.             City.DataValueField = "CIty_ID";  
  35.             City.DataBind();  
  36.             City.Items.Insert(0, new ListItem("--Select--", "0"));  
  37.         }  
  38.         protected void AddCollege_Click(object sender, EventArgs e)  
  39.         {  
  40.             InsertDetails();  
  41.         }  
  42.          
  43. void InsertDetails()  
  44.         {  
  45.             //This code is used to add the college details to the database table College Details             
  46.             try{  
  47.                 if (IsValid)  
  48.                 {  
  49.                     collegeDetail.CollegeName = CollegeName.Text;  
  50.                      
  51. collegeDetail.ContactPerson = ContactPerson.Text;  
  52.                     collegeDetail.ContactPersonPhoneNo = Convert.ToInt64(ContactPersonPhoneNo.Text == string.Empty ? "0" : ContactPersonPhoneNo.Text.ToString());  
  53.                     collegeDetail.ContactPersonEmailID = ContactPersonEmailID.Text;  
  54.                      
  55. collegeDetail.FirstVisitDate = DateTime.Parse(FirstVisitDate.Text);  
  56.                      
  57. collegeDetail.State = State.SelectedItem.ToString();  
  58.                     collegeDetail.City = City.SelectedItem.ToString();  
  59.                      
  60. collegeDetail.CreatedBy = CreatedBy.SelectedItem.Value;  
  61.                     collegeDetail.Album = AlbumFile;  
  62.                     collegeDetail.Feedback = FeedbackFile;  
  63.                     var result = DataOperations.InsertCollegeDetails(collegeDetail);  
  64.                     if (result == true)  
  65.                     {  
  66.                         Response.Redirect("~/College/CollegeDetails.aspx");  
  67.                     }  
  68.                     else  
  69.                     {  
  70.                         ErrorMessage.Text = "Data Do Not Inserted ";  
  71.                     }  
  72.                 }                 
  73.             }  
  74.             catch(Exception ex)  
  75.             {  
  76.                 ErrorMessage.Text = ex.Message;  
  77.             }  
  78.         }  
  79.    
  80.         protected void State_SelectedIndexChanged(object sender, EventArgs e)  
  81.         {  
  82.             Fill_City();  
  83.         }         
  84.     }  
  85. }  

 

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:

Edit College View

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:

  1. using CollegeDataLibrary;  
  2. using System;  
  3. using System.Web.ModelBinding;  
  4. using System.Web.UI.WebControls;  
  5.    
  6. namespace CollegeDetailsApplication  
  7. {  
  8.     public partial class EditCollegeDetails : System.Web.UI.Page  
  9.     {  
  10.         CollegeDataOperations DataOperations = new CollegeDataOperations();  
  11.         CollegeDetail detail = new CollegeDetail();  
  12.         string AlbumFile, FeedbackFile;  
  13.         protected void Page_Load(object sender, EventArgs e)  
  14.         {  
  15.              
  16.         }  
  17.    
  18.         // This is the Select method to selects a single College item with the id  
  19.         public CollegeDetail GetItem([QueryString]int? ID)  
  20.         {  
  21.             if (ID == null)  
  22.             {  
  23.                 return null;  
  24.             }  
  25.    
  26.             var context = DataOperations.GetCollegeData(ID);            
  27.             return context;        
  28.         }  
  29.    
  30.         // This is the ItemCommand method which works on Cancel  
  31.         protected void ItemCommand(object sender, FormViewCommandEventArgs e)  
  32.         {  
  33.             if (e.CommandName.Equals("Cancel", StringComparison.OrdinalIgnoreCase))  
  34.             {  
  35.                 Response.Redirect("~/College/CollegeDetails.aspx");  
  36.             }  
  37.         }  
  38.    
  39.         // This is the Update methd to update the selected College item  
  40.         public void UpdateItem([QueryString]int? ID)  
  41.         {  
  42.             var item = DataOperations.GetCollegeData(ID);  
  43.    
  44.             if (item == null)  
  45.             {  
  46.                 // The item wasn't found  
  47.                 ModelState.AddModelError("", String.Format("Item with id {0} was not found", ID));  
  48.                 return;  
  49.             }  
  50.    
  51.             if (ModelState.IsValid)  
  52.             {  
  53.                 try  
  54.                 {  
  55.                     TextBox College_Name = CollegeFormView.FindControl("CollegeName") as TextBox;  
  56.                     TextBox Contact_Person = CollegeFormView.FindControl("ContactPerson") as TextBox;  
  57.                     TextBox ContactPerson_PhoneNo = CollegeFormView.FindControl("ContactPersonPhoneNo") as TextBox;  
  58.                     TextBox ContactPerson_EmailID = CollegeFormView.FindControl("ContactPersonEmailID") as TextBox;  
  59.                     DropDownList CreatedBy_Name = CollegeFormView.FindControl("CreatedBy") as DropDownList;  
  60.    
  61.                     detail.CollegeName = College_Name.Text;  
  62.                     detail.ContactPerson = Contact_Person.Text;  
  63.                     detail.ContactPersonPhoneNo = Convert.ToInt64(ContactPerson_PhoneNo.Text);  
  64.                     detail.ContactPersonEmailID = ContactPerson_EmailID.Text;  
  65.                     detail.CreatedBy = CreatedBy_Name.Text;  
  66.                     detail.Album = AlbumFile;  
  67.                     detail.Feedback = FeedbackFile;  
  68.                     detail.EditedBy = Session["UserName"].ToString();  
  69.                     DataOperations.UpdateCollegeDetails(ID, detail);  
  70.                     Response.Redirect("~/College/CollegeDetails.aspx");  
  71.                 }  
  72.                 catch (Exception ex)  
  73.                 {  
  74.                     ErrorMessage.Text = ex.Message;  
  75.                 }  
  76.             }  
  77.         }         
  78.     }  
  79. }  

 

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:

  1. <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">  
  2.     <div>  
  3.         <p> </p>  
  4.         <h3>Are you sure want to delete this College?</h3>  
  5.         <asp:FormView runat="server" ID="DeleteDetail"  
  6.             ItemType="CollegeDataLibrary.CollegeDetail" DefaultMode="Edit" DataKeyNames="CollegeID"  
  7.             DeleteMethod="DeleteItem" SelectMethod="GetItem"  
  8.             OnItemCommand="ItemCommand" RenderOuterTable="false">  
  9.             <EmptyDataTemplate>  
  10.                 Cannot find the College with ID <%: Request.QueryString["ID"] %>  
  11.             </EmptyDataTemplate>  
  12.             <EditItemTemplate>  
  13.                 <div class="form-horizontal">  
  14.                     <h4>Delete College</h4>  
  15.                     <hr />  
  16.                     <asp:ValidationSummary runat="server" CssClass="text-danger" />  
  17.                     <div class="form-group">  
  18.                         <asp:Label runat="server" AssociatedControlID="CollegeName" CssClass="col-md-2 control-label">College Name</asp:Label>  
  19.                         <div class="col-md-10">  
  20.                             <asp:Label runat="server" ID="CollegeName" Text='<%#Bind("CollegeName") %>' CssClass="col-md-2 control-label"></asp:Label>  
  21.                         </div>  
  22.                     </div>  
  23.                                        
  24.                     <div class="form-group">  
  25.                         <div class="col-sm-offset-2 col-sm-10">  
  26.                             <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" CssClass="btn btn-danger" />  
  27.                             <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" CssClass="btn btn-info" />  
  28.                         </div>  
  29.                     </div>  
  30.                 </div>                
  31.             </EditItemTemplate>  
  32.         </asp:FormView>  
  33.     </div>  
  34. </asp:Content>  

 

Step 2

Paste the following code in the code page:

  1. using CollegeDataLibrary;  
  2. using System;  
  3. using System.Web.ModelBinding;  
  4. using System.Web.UI.WebControls;  
  5.    
  6. namespace CollegeDetailsApplication  
  7. {  
  8.     public partial class DeleteCollegeDetails : System.Web.UI.Page  
  9.     {  
  10.         CollegeDataOperations DataOperations = new CollegeDataOperations();  
  11.         protected void Page_Load(object sender, EventArgs e)  
  12.         {  
  13.             
  14.         }  
  15.    
  16.         // This is the Select method to selects a single College item with the id  
  17.         public CollegeDetail GetItem([QueryString]int? ID)  
  18.         {  
  19.             if (ID == null)  
  20.             {  
  21.                 return null;  
  22.             }  
  23.    
  24.             var context = DataOperations.GetCollegeData(ID);  
  25.             return context;  
  26.         }  
  27.    
  28.         // This is the Delete methd to delete the selected College item  
  29.         public void DeleteItem([QueryString]int? ID)  
  30.         {  
  31.             var item = DataOperations.GetCollegeData(ID);  
  32.    
  33.             if (item != null)  
  34.             {  
  35.                 DataOperations.DeleteCollegeDetails(ID);  
  36.             }  
  37.    
  38.             Response.Redirect("~/College/CollegeDetails.aspx");  
  39.         }  
  40.    
  41.         // This is the ItemCommand method which works on Cancle  
  42.         protected void ItemCommand(object sender, FormViewCommandEventArgs e)  
  43.         {  
  44.             if (e.CommandName.Equals("Cancel", StringComparison.OrdinalIgnoreCase))  
  45.             {  
  46.                 Response.Redirect("~/College/CollegeDetails.aspx");  
  47.             }  
  48.         }  
  49.     }  
  50. }  

 

Step 3

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

Delete College View

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!!

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs