Post
Ask Question

Working With Stored Procedures Using Entity Framework: Part 3

Introduction

 
I am creating an article series about the use of Stored Procedures with the Entity Framework 6. In the first part, we saw how to create the Stored Procedure and how to map that Stored Procedure with the entity data model. In the second part, we have created a web application and done the CRUD operations using the Entity Framework with a Stored Procedure.
 
You can check out the previous articles of this series with the following links:
In this part, we will move further and deal with the details of the data and do some operations in it. So, let's begin with the following sections:
  • Applying Membership Functionality
  • Working with Application
  • Running the Application

Applying Membership Functionality

 
In this section, we will implement the login functionality in the application. To do this use the following procedure:
 
Step 1
In the Solution Explorer, select the project and add a new folder, Account.
 
Step 2
Add a web form to the folder and paste the following code in the form:
  1. <div class="navbar navbar-inverse navbar-fixed-top">  
  2.     <div class="container">  
  3.         <div class="navbar-header">  
  4.             <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">  
  5.                 <span class="icon-bar"></span>  
  6.                 <span class="icon-bar"></span>  
  7.                 <span class="icon-bar"></span>  
  8.            </button>  
  9.             <p class="navbar-brand" runat="server">College Lead Tracker</p>  
  10.         </div>  
  11.     </div>  
  12. </div>  
  13. <div class="col-md-4">  
  14. </div>  
  15. <div class="mind">  
  16. </div>  
  17. <div class="container body-content">  
  18.     <div class="row">  
  19.         <div class="log  col-md-12">  
  20.             <section id="loginForm" class="col-md-4 ">  
  21.                 <div class="form-horizontal">  
  22.                     <hr />  
  23.                     <div class="form-group">  
  24.                         <div class="col-md-10">  
  25.                             <asp:TextBox runat="server" ID="UserID" CssClass="form-control" placeholder="User Name" />  
  26.                             <asp:RequiredFieldValidator runat="server" ControlToValidate="UserID"  
  27.                                 CssClass="text-danger" ErrorMessage="The UserID field is required." />  
  28.                         </div>  
  29.                     </div>  
  30.                     <div class="form-group">  
  31.                          <div class="col-md-10">  
  32.                              <asp:TextBox runat="server" ID="Password" TextMode="Password" CssClass="form-control" placeholder="Password" />  
  33.                              <asp:RequiredFieldValidator runat="server" ControlToValidate="Password" CssClass="text-danger" ErrorMessage="The password field is required." />  
  34.                         </div>  
  35.                     </div>  
  36.                     <div class="form-group">  
  37.                         <div class="col-md-offset-2 col-md-10">  
  38.                             <asp:Button runat="server" Text="Log in" ID="Login" OnClick="Login_Click" CssClass="btn btn-info" />  
  39.                          </div>  
  40.                      </div>  
  41.                 </div>  
  42.                 <p class="text-danger">  
  43.                     <asp:Literal runat="server" ID="FailureText" />  
  44.                 </p>  
  45.             </section>  
  46.         </div>  
  47.     </div>  
  48.     <hr />  
  49.      <footer>  
  50.          <p>© <%: DateTime.Now.Year %> - College Details App</p>  
  51.      </footer>  
  52. </div>  
After using the code the page will look such as the following:
 
Login Form in Application
 
Step 3
In the code behind replace the code with the following code:
  1. using CollegeDataLibrary;  
  2. using System;  
  3.    
  4. namespace CollegeDetailsApplication.Account  
  5. {  
  6.     public partial class WebForm1 : System.Web.UI.Page  
  7.     {  
  8.         CollegeDataOperations obj = new CollegeDataOperations();  
  9.         protected void Page_Load(object sender, EventArgs e)  
  10.         {  
  11.    
  12.         }  
  13.    
  14.         protected void Login_Click(object sender, EventArgs e)  
  15.         {  
  16.             Check_LoginDetails();  
  17.             Reset();  
  18.         }  
  19.    
  20.         //This method is used to check the login details  
  21.         void Check_LoginDetails()  
  22.         {  
  23.             try  
  24.             {  
  25.                 string UserName = UserID.Text;  
  26.                 string UserPassword = Password.Text;  
  27.                 bool Result = obj.GetLoginDetails(UserName, UserPassword);  
  28.                 if (Result == true)  
  29.                 {  
  30.                     Session["UserName"] = UserName;  
  31.                     Response.Redirect("~/College/CollegeDetails.aspx");  
  32.                 }  
  33.                 else  
  34.                 {  
  35.                     FailureText.Text = "Invalid User Name or Password!!";  
  36.                 }  
  37.             }  
  38.             catch (Exception ex)  
  39.             {  
  40.                 FailureText.Text = ex.Message;  
  41.             }  
  42.         }  
  43.    
  44.         void Reset()  
  45.         {  
  46.             UserID.Text = "";  
  47.             Password.Text = "";  
  48.         }  
  49.     }  
  50. }  
Step 4
You can also implement the registration functionality here.
 

Working with Application

 
So far we have done the CRUD operations in the previous part of this article series. In this section, we will do the next operation in which we can check out the details of the data stored in the database and do some further operations with the existing data.
 
Let's begin with the following procedure.
 
Step 1
As you can see in the following screenshot, we have the ListView of the existing data. Now, for checking the details we can click on the Details link.
 
Data in ListView
 
Step 2
Now add a new web form with the master page named ManageCollegeDetails in the College folder.
 
Adding Web Form with Master Page
 
Step 3
Design the web form with the following code:
  • Creating FormView
     
    Add a FormView and design the FormView with the following code: 
    1. <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">  
    2.      
    3.     <p class="text-danger">  
    4.         <asp:Literal runat="server" ID="ErrorMessage" />  
    5.     </p>  
    6.     <asp:FormView runat="server" ID="DetailsFormView"  
    7.         ItemType="CollegeDataLibrary.CollegeDetail" DefaultMode="Edit" DataKeyNames="CollegeID"  
    8.         SelectMethod="GetItem" OnItemCommand="ItemCommand" RenderOuterTable="false">  
    9.         <EmptyDataTemplate>  
    10.             Cannot find the College with ID <%: Request.QueryString["ID"] %>  
    11.         </EmptyDataTemplate>  
    12.    
    13.         <EditItemTemplate>  
    14.             <div class="college form-horizontal ">  
    15.                 <h4 style="margin-top: 52px;">College Details Summary</h4>  
    16.                 <div class="col-sm-7">  
    17.                     <asp:Button runat="server" PostBackUrl="~/College/CollegeDetails.aspx" Text="Back" CssClass="btn btn-info" Style="float: right; margin: -33px 0 0 0" />  
    18.                 </div>  
    19.                 <hr />  
    20.                 <asp:ValidationSummary runat="server" CssClass="text-danger" />  
    21.                 <div class="form-group">  
    22.                     <asp:Label runat="server" AssociatedControlID="CollegeName" CssClass="col-md-2 control-label">College Name</asp:Label>  
    23.                     <div class="col-md-10">  
    24.                         <asp:Label runat="server" ID="CollegeName" Text='<%#Bind("CollegeName") %>' CssClass="col-md-8 control-label "></asp:Label>  
    25.                     </div>  
    26.                 </div>  
    27.                 <div class="form-group">  
    28.                     <asp:Label runat="server" AssociatedControlID="ContactPerson" CssClass="col-md-2 control-label">Contact Person</asp:Label>  
    29.                     <div class="col-md-10">  
    30.                         <asp:Label runat="server" ID="ContactPerson" Text='<%#Bind("ContactPerson") %>' CssClass="col-md-8 control-label"></asp:Label>  
    31.                     </div>  
    32.                 </div>  
    33.                 <div class="form-group">  
    34.                     <table style="width: 100%;">  
    35.                         <tr>  
    36.                             <td id="cmmnts">  
    37.                                 <asp:Label runat="server" ID="LblComments" CssClass="col-md-2 control-label">Remarks</asp:Label>  
    38.                             </td>  
    39.                         </tr>  
    40.                         <tr>  
    41.                             <td>  
    42.                                 <div class="col-md-7">  
    43.                                     <asp:TextBox runat="server" ID="TxtPostComment" CssClass="form-control Comments" placeholder="Post Your Comment"   
    44. TextMode="MultiLine" ValidationGroup="PostComment" />                                     
    45.                                 </div>  
    46.                                 <div class="col-sm-7">  
    47.                                     <a class='btn btn-primary'>Attachments<asp:FileUpload ID="AttachedFile" CssClass="upload" runat="server" /></a>  
    48.                                        
    49.                                                     <span class='label label-info' id="upload-file-info"></span>  
    50.                                     <asp:Button ID="PostButton" runat="server" OnClick="PostButton_Click" Text="Post" ValidationGroup="PostComment"   
    51. CssClass="btn btn-success" Style="float: right;" />  
    52.                                 </div>  
    53.                             </td>  
    54.                         </tr>  
    55.                     </table>  
    56.                 </div>  
    57.             </div>  
    58.         </EditItemTemplate>  
    59.     </asp:FormView>   
    The design page will look like this:
     
    Details Page
     
    Note: Add more fields to the code.
     
  • View Comments Information
     
    Now we will design the repeater to show the previous comments and in which we also can download the attachments. After Form view add the following code: 
    1. <div class="form-group">  
    2.         <div class="PostedComment form-horizontal">  
    3.             <asp:Repeater ID="CommentsRepeater" runat="server" ItemType="CollegeDataLibrary.Comment">  
    4.                 <HeaderTemplate>  
    5.                     <table>  
    6.                         <thead>  
    7.                             <tr>  
    8.                                 <th>Remarks</th>  
    9.                             </tr>  
    10.                         </thead>  
    11.                         <tbody>  
    12.                             <tr runat="server" id="itemPlaceholder" />  
    13.                         </tbody>  
    14.                     </table>  
    15.                 </HeaderTemplate>  
    16.                 <ItemTemplate>  
    17.                     <table style="width: 60%">  
    18.                         <tr>  
    19.                             <th colspan="2" style="width: 31%">  
    20.                                 <asp:Label ID="CreatedBy" runat="server" CssClass="col-md-9 control-label" Text='<%#Bind("CreatedBy") %>'></asp:Label>  
    21.                             </th>  
    22.                         </tr>  
    23.                         <tr>  
    24.                             <td style="width: 60%">  
    25.                                 <asp:Label ID="CommentDescription" CssClass="col-md-11 control-label" runat="server" Text='<%# Bind("CommentDescription") %>'></asp:Label>  
    26.                             </td>  
    27.                             <td>  
    28.                                 <asp:LinkButton ID="FileDownload" Text='<%# Bind("Files") %>' CommandArgument='<%# Eval("Files") %>' runat="server" OnClick="DownloadFile"></asp:LinkButton>  
    29.                             </td>  
    30.                             <td style="width: 16%; text-align: left;">  
    31.                                 <asp:Label ID="CreatedDate" CssClass="col-md-10 control-label" runat="server" Text='<%# Bind("CreatedDate", "{0:M-dd-yyyy}") %>'></asp:Label>  
    32.                             </td>  
    33.                         </tr>  
    34.                     </table>  
    35.                 </ItemTemplate>  
    36.                 <FooterTemplate>  
    37.                     <asp:Label ID="LblEmpty" Text="Be the first to comment" runat="server"  
    38.                         Visible='<%#bool.Parse((CommentsRepeater.Items.Count==0).ToString()) %>'></asp:Label>  
    39.                 </FooterTemplate>  
    40.             </asp:Repeater>  
    41.         </div>  
    42.     </div>  
    43. </asp:Content>  
Finally, we can show the entire details with the comments of any college data.
 
Step 4
Now 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.Web.ModelBinding;  
  5. using System.Web.UI.WebControls;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.IO;  
  9. using System.Net;  
  10. using System.Web.UI;  
  11.    
  12. namespace CollegeDetailsApplication  
  13. {  
  14.     public partial class ManageCollegeDetails : System.Web.UI.Page  
  15.     {  
  16.         CollegeDataOperations DataOperations = new CollegeDataOperations();  
  17.         Comment PostedComments = new Comment(); string MyFile;  
  18.         protected void Page_Load(object sender, EventArgs e)  
  19.         {  
  20.             if (!IsPostBack)  
  21.             {  
  22.                 GetCommentData();  
  23.             }  
  24.         }  
  25.    
  26.         // This is the Select method to selects a single College item with the id   
  27.         public CollegeDetail GetItem([QueryString]int? ID)  
  28.         {  
  29.             if (ID == null)  
  30.             {  
  31.                 return null;  
  32.             }             
  33.             var context = DataOperations.GetCollegeData(ID);  
  34.             return context;  
  35.         }  
  36.    
  37.         // This is the Select method to selects a comment data  
  38.         public void GetCommentData()  
  39.         {  
  40.             int ID = Convert.ToInt32(Request.QueryString["ID"]);  
  41.             CommentsRepeater.DataSource = DataOperations.GetCommentDetails(ID);   
  42.             CommentsRepeater.DataBind();  
  43.         }  
  44.    
  45.         // This is the ItemCommand method which works on Cancel  
  46.         protected void ItemCommand(object sender, FormViewCommandEventArgs e)  
  47.         {  
  48.             if (e.CommandName.Equals("Cancel", StringComparison.OrdinalIgnoreCase))  
  49.             {  
  50.                 Response.Redirect("~/College/CollegeDetails.aspx");  
  51.             }  
  52.         }  
  53.    
  54.         protected void PostButton_Click(object sender, EventArgs e)  
  55.         {  
  56.             InsertComment();  
  57.         }  
  58.    
  59.         // This is the InsertComment method which insert the comment and get the comment data  
  60.         void InsertComment()  
  61.         {  
  62.             if (IsValid)  
  63.             {  
  64.                 try  
  65.                 {  
  66.                     TextBox CommentDescription = DetailsFormView.FindControl("TxtPostComment") as TextBox;  
  67.                     PostedComments.CommentDescription = CommentDescription.Text;  
  68.                     PostedComments.CreatedBy = Session["UserName"].ToString();  
  69.                     PostedComments.CollegeID = Convert.ToInt32(Request.QueryString["ID"]);  
  70.                     PostedComments.Files = MyFile;  
  71.                     bool result = DataOperations.InsertCommentDetails(PostedComments);  
  72.                     if (result == true)  
  73.                     {  
  74.                         GetCommentData();  
  75.                     }  
  76.                     CommentDescription.Text = "";  
  77.                 }  
  78.                 catch (Exception ex)  
  79.                 {  
  80.                     ErrorMessage.Text = ex.Message;  
  81.                 }  
  82.             }  
  83.         }  
  84.         //This method is used to Upload the Files  
  85.         public void UploadFile()  
  86.         {  
  87.             FileUpload AttachedFile = DetailsFormView.FindControl("AttachedFile") as FileUpload;   
  88.             HttpPostedFile PostedFile = AttachedFile.PostedFile;  
  89.             if (PostedFile != null && PostedFile.ContentLength > 0)  
  90.             {  
  91.                 MyFile = Path.GetFileName(PostedFile.FileName);  
  92.                 PostedFile.SaveAs(Server.MapPath(Path.Combine("~/Data/", MyFile)));  
  93.             }  
  94.         }  
  95.    
  96.         //This method is used to download the Files  
  97.         protected void DownloadFile(object sender, EventArgs e)  
  98.         {  
  99.             WebClient req = new WebClient();  
  100.             string filePath = (sender as LinkButton).CommandArgument;  
  101.             Response.ContentType = ContentType;  
  102.             HttpResponse response = HttpContext.Current.Response;  
  103.             string file = Server.MapPath("~/Data/") + filePath;  
  104.             response.Clear();  
  105.             response.ClearContent();  
  106.             response.ClearHeaders();  
  107.             response.Buffer = true;  
  108.             response.AddHeader("Content-Disposition", "attachment;filename=" + filePath);  
  109.             byte[] data = req.DownloadData(file);  
  110.             response.BinaryWrite(data);  
  111.             response.End();  
  112.         }         
  113.     }  
  114. }  

Running the Application

 
Step 1
First login into the application as in the following:
 
Login in the application
 
Step 2
Click on the Details of any college data as in the following:
 
Details Page
 
Step 3
Now you can show the details and can post the new comment, enter the comment to post as in the following:
 
Comment in the Application
 
After entering the comment the page is updated.
 
Comment in the Details Page
 
That's it.
 
Note: This article is associated with the previous articles of this series. So please start with the first article.
 

Summary

 
This article series will help to work with the Stored Procedure mapped with the Entity Data Model. You can use the Stored Procedure with the Entity Framework. You can create the web application using the Web Forms Project Template. Thanks for reading and Happy Coding!!


Similar Articles