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:

• Working with Stored Procedures using Entity Framework Part 1
• Working with Stored Procedures using Entity Framework Part 2

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:

<div class="navbar navbar-inverse navbar-fixed-top">
    <div class="container">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
           </button>
            <p class="navbar-brand" runat="server">College Lead Tracker</p>
        </div>
    </div>
</div>
<div class="col-md-4">
</div>
<div class="mind">
</div>
<div class="container body-content">
    <div class="row">
        <div class="log  col-md-12">
            <section id="loginForm" class="col-md-4 ">
                <div class="form-horizontal">
                    <hr />
                    <div class="form-group">
                        <div class="col-md-10">
                            <asp:TextBox runat="server" ID="UserID" CssClass="form-control" placeholder="User Name" />
                            <asp:RequiredFieldValidator runat="server" ControlToValidate="UserID"
                                CssClass="text-danger" ErrorMessage="The UserID field is required." />
                        </div>
                    </div>
                    <div class="form-group">
                         <div class="col-md-10">
                             <asp:TextBox runat="server" ID="Password" TextMode="Password" CssClass="form-control" placeholder="Password" />
                             <asp:RequiredFieldValidator runat="server" ControlToValidate="Password" CssClass="text-danger" ErrorMessage="The password field is required." />
                        </div>
                    </div>
                    <div class="form-group">
                        <div class="col-md-offset-2 col-md-10">
                            <asp:Button runat="server" Text="Log in" ID="Login" OnClick="Login_Click" CssClass="btn btn-info" />
                         </div>
                     </div>
                </div>
                <p class="text-danger">
                    <asp:Literal runat="server" ID="FailureText" />
                </p>
            </section>
        </div>
    </div>
    <hr />
     <footer>
         <p>© <%: DateTime.Now.Year %> - College Details App</p>
     </footer>
</div>

After using the code the page will look such as the following:

Step 3

In the code behind replace the code with the following code:

using CollegeDataLibrary;
using System;

namespace CollegeDetailsApplication.Account
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        CollegeDataOperations obj = new CollegeDataOperations();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Login_Click(object sender, EventArgs e)
        {
            Check_LoginDetails();
            Reset();
        }

        //This method is used to check the login details
        void Check_LoginDetails()
        {
            try
            {
                string UserName = UserID.Text;
                string UserPassword = Password.Text;
                bool Result = obj.GetLoginDetails(UserName, UserPassword);
                if (Result == true)
                {
                    Session["UserName"] = UserName;
                    Response.Redirect("~/College/CollegeDetails.aspx");
                }
                else
                {
                    FailureText.Text = "Invalid User Name or Password!!";
                }
            }
            catch (Exception ex)
            {
                FailureText.Text = ex.Message;
            }
        }

        void Reset()
        {
            UserID.Text = "";
            Password.Text = "";
        }
    }
}

Step 4

You can also implement the registration functionality in 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.

Step 2

Now add a new web form with master page named ManageCollegeDetails in the College folder.

Step 3

Design the web form with the following code:

• Creating FormView
  
  Add a FormView and design the FormView with the following code: 

  <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
  
      <p class="text-danger">
          <asp:Literal runat="server" ID="ErrorMessage" />
      </p>
      <asp:FormView runat="server" ID="DetailsFormView"
          ItemType="CollegeDataLibrary.CollegeDetail" DefaultMode="Edit" DataKeyNames="CollegeID"
          SelectMethod="GetItem" OnItemCommand="ItemCommand" RenderOuterTable="false">
          <EmptyDataTemplate>
              Cannot find the College with ID <%: Request.QueryString["ID"] %>
          </EmptyDataTemplate>
  
          <EditItemTemplate>
              <div class="college form-horizontal ">
                  <h4 style="margin-top: 52px;">College Details Summary</h4>
                  <div class="col-sm-7">
                      <asp:Button runat="server" PostBackUrl="~/College/CollegeDetails.aspx" Text="Back" CssClass="btn btn-info" Style="float: right; margin: -33px 0 0 0" />
                  </div>
                  <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-8 control-label "></asp:Label>
                      </div>
                  </div>
                  <div class="form-group">
                      <asp:Label runat="server" AssociatedControlID="ContactPerson" CssClass="col-md-2 control-label">Contact Person</asp:Label>
                      <div class="col-md-10">
                          <asp:Label runat="server" ID="ContactPerson" Text='<%#Bind("ContactPerson") %>' CssClass="col-md-8 control-label"></asp:Label>
                      </div>
                  </div>
                  <div class="form-group">
                      <table style="width: 100%;">
                          <tr>
                              <td id="cmmnts">
                                  <asp:Label runat="server" ID="LblComments" CssClass="col-md-2 control-label">Remarks</asp:Label>
                              </td>
                          </tr>
                          <tr>
                              <td>
                                  <div class="col-md-7">
                                      <asp:TextBox runat="server" ID="TxtPostComment" CssClass="form-control Comments" placeholder="Post Your Comment"
  TextMode="MultiLine" ValidationGroup="PostComment" />
                                  </div>
                                  <div class="col-sm-7">
                                      <a class='btn btn-primary'>Attachments<asp:FileUpload ID="AttachedFile" CssClass="upload" runat="server" /></a>
  
                                                      <span class='label label-info' id="upload-file-info"></span>
                                      <asp:Button ID="PostButton" runat="server" OnClick="PostButton_Click" Text="Post" ValidationGroup="PostComment"
  CssClass="btn btn-success" Style="float: right;" />
                                  </div>
                              </td>
                          </tr>
                      </table>
                  </div>
              </div>
          </EditItemTemplate>
      </asp:FormView>

  The design page will look like this:
  
  
  
  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: 

  <div class="form-group">
          <div class="PostedComment form-horizontal">
              <asp:Repeater ID="CommentsRepeater" runat="server" ItemType="CollegeDataLibrary.Comment">
                  <HeaderTemplate>
                      <table>
                          <thead>
                              <tr>
                                  <th>Remarks</th>
                              </tr>
                          </thead>
                          <tbody>
                              <tr runat="server" id="itemPlaceholder" />
                          </tbody>
                      </table>
                  </HeaderTemplate>
                  <ItemTemplate>
                      <table style="width: 60%">
                          <tr>
                              <th colspan="2" style="width: 31%">
                                  <asp:Label ID="CreatedBy" runat="server" CssClass="col-md-9 control-label" Text='<%#Bind("CreatedBy") %>'></asp:Label>
                              </th>
                          </tr>
                          <tr>
                              <td style="width: 60%">
                                  <asp:Label ID="CommentDescription" CssClass="col-md-11 control-label" runat="server" Text='<%# Bind("CommentDescription") %>'></asp:Label>
                              </td>
                              <td>
                                  <asp:LinkButton ID="FileDownload" Text='<%# Bind("Files") %>' CommandArgument='<%# Eval("Files") %>' runat="server" OnClick="DownloadFile"></asp:LinkButton>
                              </td>
                              <td style="width: 16%; text-align: left;">
                                  <asp:Label ID="CreatedDate" CssClass="col-md-10 control-label" runat="server" Text='<%# Bind("CreatedDate", "{0:M-dd-yyyy}") %>'></asp:Label>
                              </td>
                          </tr>
                      </table>
                  </ItemTemplate>
                  <FooterTemplate>
                      <asp:Label ID="LblEmpty" Text="Be the first to comment" runat="server"
                          Visible='<%#bool.Parse((CommentsRepeater.Items.Count==0).ToString()) %>'></asp:Label>
                  </FooterTemplate>
              </asp:Repeater>
          </div>
      </div>
  </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:

using CollegeDataLibrary;
using System;
using System.Collections.Generic;
using System.Web.ModelBinding;
using System.Web.UI.WebControls;
using System.Linq;
using System.Web;
using System.IO;
using System.Net;
using System.Web.UI;

namespace CollegeDetailsApplication
{
    public partial class ManageCollegeDetails : System.Web.UI.Page
    {
        CollegeDataOperations DataOperations = new CollegeDataOperations();
        Comment PostedComments = new Comment(); string MyFile;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GetCommentData();
            }
        }

        // 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 Select method to selects a comment data
        public void GetCommentData()
        {
            int ID = Convert.ToInt32(Request.QueryString["ID"]);
            CommentsRepeater.DataSource = DataOperations.GetCommentDetails(ID);
            CommentsRepeater.DataBind();
        }

        // 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");
            }
        }

        protected void PostButton_Click(object sender, EventArgs e)
        {
            InsertComment();
        }

        // This is the InsertComment method which insert the comment and get the comment data
        void InsertComment()
        {
            if (IsValid)
            {
                try
                {
                    TextBox CommentDescription = DetailsFormView.FindControl("TxtPostComment") as TextBox;
                    PostedComments.CommentDescription = CommentDescription.Text;
                    PostedComments.CreatedBy = Session["UserName"].ToString();
                    PostedComments.CollegeID = Convert.ToInt32(Request.QueryString["ID"]);
                    PostedComments.Files = MyFile;
                    bool result = DataOperations.InsertCommentDetails(PostedComments);
                    if (result == true)
                    {
                        GetCommentData();
                    }
                    CommentDescription.Text = "";
                }
                catch (Exception ex)
                {
                    ErrorMessage.Text = ex.Message;
                }
            }
        }
        //This method is used to Upload the Files
        public void UploadFile()
        {
            FileUpload AttachedFile = DetailsFormView.FindControl("AttachedFile") as FileUpload;
            HttpPostedFile PostedFile = AttachedFile.PostedFile;
            if (PostedFile != null && PostedFile.ContentLength > 0)
            {
                MyFile = Path.GetFileName(PostedFile.FileName);
                PostedFile.SaveAs(Server.MapPath(Path.Combine("~/Data/", MyFile)));
            }
        }

        //This method is used to download the Files
        protected void DownloadFile(object sender, EventArgs e)
        {
            WebClient req = new WebClient();
            string filePath = (sender as LinkButton).CommandArgument;
            Response.ContentType = ContentType;
            HttpResponse response = HttpContext.Current.Response;
            string file = Server.MapPath("~/Data/") + filePath;
            response.Clear();
            response.ClearContent();
            response.ClearHeaders();
            response.Buffer = true;
            response.AddHeader("Content-Disposition", "attachment;filename=" + filePath);
            byte[] data = req.DownloadData(file);
            response.BinaryWrite(data);
            response.End();
        }
    }
}

Running the Application

Step 1

First login into the application as in the following:

Step 2

Click on the Details of any college data as in the following:

Step 3

Now you can show the details and can post the new comment, enter the comment to post as in the following:

After entering the comment the page is updated.

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