Overview

In the ASP.Net MVC 5 project template there are various types of techniques by which we can perform the database operations like Create, Read, Update and Delete (CRUD) in the web application. We can use the Entity Framework approaches in the controller using an Entity Data Model and display it from the View. We can create the service for doing data operations and using a service reference we can call the operations using JavaScript like Angular, Knockout or jQuery in the web application.

This article shows how to create a web application using the ASP.NET Web API 2 project template and do some data operations like reading the data from the database and calling the Web API Controller method in the MVC Controller and call the adjacent view. I have created n layers of the application when creating this project. You will see multiple layers of the application in this project. I have applied the simple ADO.Net approach for doing the database operations.

Prerequisites

I am creating an article in which the web applications are created with the new Project Templates like ASP.Net Web API 2 and ASP.Net MVC 5 in the Visual Studio 2013, so there are the following prerequisites before getting started:

Getting Started

Let's begin with the following sections:

Creating Database

In this section we will create the database for the application. Begin with the following procedure.

Step 1

Create the database architecture with the following code:

  1. CREATE DATABASE [CollegeTracker]
  3. USE CollegeTracker
  5. CREATE TABLE [dbo].[CollegeDetails](
  6. [CollegeID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
  7. [CollegeName] [varchar](100) NULL,
  8. [CollegeAddress] [nvarchar](150) NULL,
  9. [CollegePhone] [bigint] NULL,
  10. [CollegeEmailID] [nvarchar](50) NULL,
  11. [ContactPerson] [varchar](50) NULL,
  12. [State] [varchar](100) NULL,
  13. [City] [varchar](100) NULL,
  14. )

Note: Insert some values into the table.

Step 2

Now, we'll create the Stored Procedure for reading the data with the following code:

  1. USE [CollegeTracker]
  3. Create Proc [dbo].[CT_CollegeDetails_Select]
  5. As
  6. Begin
  7. Select * From CollegeDetails
  8. End

Creating Project

In this section, we will create and add the application for doing the data operation and create the ASP.NET web application using the ASP.NET Web API 2 project template. We will create the separate project for the API and DAL to better understand the code.

At first we will create the main project to start the application. Let's follow the procedure given below.

Step 1

Open Visual Studio 2013 and click on "New Project".

Visual Studio 2013 Start Page

Step 2

Create the "ASP.Net Web Application" named "CollegeTracker" using the MVC Project Template.

Mvc Project Template

Visual Studio creates the web application with the MVC template.

Step 3

Create a new folder named "Web" in the Solution Explorer and move the MVC project into that folder.

Creating Model

At first we will create the Model for the application. A Model is the very essential part for the project. In the Model, we define the classes for the application. Use the procedure given below.

Step 1

Add a new folder named "Models" in the Solution Explorer.

Step 2

Add a new class library project named "CollegeTrackerModels" to the Models folder.

Creating Model

Note: Remove the automatically created class.

Step 3

Add a new class named "CollegeDetails" to the model project.

Step 4

Replace the code with the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel.DataAnnotations;
  5. namespace CollegeTrackerModels
  6. {
  7. /// <summary>
  8. /// class for College Details
  9. /// </summary>
  10. public class CollegeDetails
  11. {
  12. #region Properties
  13. ///<summary>
  14. ///get and set the College ID
  15. ///</summary>
  16. public int CollegeID { get; set; }
  17. ///<summary>
  18. ///get and set the College Name
  19. ///</summary>
  20. [Display(Name = "College Name")]
  21. public string CollegeName { get; set; }
  22. ///<summary>
  23. ///get and set the College Address
  24. ///</summary>
  25. [Display(Name = "College Address")]
  26. public string CollegeAddress { get; set; }
  27. ///<summary>
  28. ///get and set the College Phone
  29. ///</summary>
  30. [Display(Name = "College Phone")]
  31. public Int64 CollegePhone { get; set; }
  32. ///<summary>
  33. ///get and set the College Email ID
  34. ///</summary>
  35. [Display(Name = "College EmailID")]
  36. public string CollegeEmailID { get; set; }
  37. ///<summary>
  38. ///get and set the Contact Person
  39. ///</summary>
  40. [Display(Name = "Contact Person")]
  41. public string ContactPerson { get; set; }
  42. ///<summary>
  43. ///get and set the State
  44. ///</summary>
  45. public string State { get; set; }
  46. ///<summary>
  47. ///get and set the City
  48. ///</summary>
  49. public string City { get; set; }
  50. #endregion
  51. }
  52. }

Creating Data Access Layer

Step 1

Create another folder named "Modules" by just right-clicking on the solution in the Solution Explorer and add a new Library Project to the Modules folder named "CollegeTrackerCore".

Creating Class Library

Note: Remove the automatically created class.

Step 2

Add two new folders named "BLL" and "DAL" to the project.

Step 3

Right-click on the DAL folder and add a new class named "CollegeDAL".

Creating DAL Class

Step 4

Replace the code with the following code in the CollegeDAL:

  1. using System;
  2. using System.Collections.Generic;
  3. using CollegeTrackerModels;
  4. using System.Configuration;
  5. using System.Data.SqlClient;
  6. using System.Data;
  8. namespace CollegeTrackerCore.DAL
  9. {
  10. public class CollegeDAL
  11. {
  12. #region Variables
  13. SqlConnection con;
  14. SqlCommand cmd;
  15. SqlDataAdapter adap;
  16. DataTable dt;
  17. DataSet ds;
  18. string connectionstring = ConfigurationManager.ConnectionStrings["CollegeTrackerConnectionString"].ConnectionString;
  19. #endregion
  21. #region Constructor
  22. public CollegeDAL()
  23. {
  24. con = new SqlConnection(this.connectionstring);
  25. }
  26. #endregion
  28. #region Public Method
  29. /// <summary>
  30. /// This method is used to get all College Details.
  31. /// </summary>
  32. /// <returns></returns>
  33. public List<CollegeDetails> GetAllCollegeDetails()
  34. {
  35. List<CollegeDetails> objCollegeDetails = new List<CollegeDetails>();
  36. using (cmd = new SqlCommand("CT_CollegeDetails_Select", con))
  37. {
  38. try
  39. {
  40. cmd.CommandType = CommandType.StoredProcedure;
  41. con.Open();
  42. adap = new SqlDataAdapter();
  43. adap.SelectCommand = cmd;
  44. dt = new DataTable();
  45. adap.Fill(dt);
  47. foreach (DataRow row in dt.Rows)
  48. {
  49. CollegeDetails col = new CollegeDetails();
  50. col.CollegeID = Convert.ToInt32(row["CollegeID"]);
  51. col.CollegeName = row["CollegeName"].ToString();
  52. col.CollegeAddress = row["CollegeAddress"].ToString();
  53. col.CollegePhone = Convert.ToInt64(row["CollegePhone"]);
  54. col.CollegeEmailID = row["CollegeEmailID"].ToString();
  55. col.ContactPerson = row["ContactPerson"].ToString();
  56. col.State = row["State"].ToString();
  57. col.City = row["City"].ToString();
  58. objCollegeDetails.Add(col);
  59. }
  60. }
  61. catch (Exception ex)
  62. {
  63. con.Close();
  64. }
  65. return objCollegeDetails;
  66. }
  67. }
  68. #endregion
  69. }
  70. }

Step 5

Now in the BLL folder add a new class named "CollegeBLCore".

Adding Class

Step 6

Replace the code with the following code:

  1. using CollegeTrackerCore.DAL;
  2. using CollegeTrackerModels;
  3. using System;
  4. using System.Collections.Generic;
  6. namespace CollegeTrackerCore.BLL
  7. {
  8. public abstract class CollegeBLCore
  9. {
  10. #region Public Method
  11. /// <summary>
  12. /// This method is used to get the College Details
  13. /// </summary>
  14. /// <returns></returns>
  15. protected List<CollegeDetails> GetAllCollegeDetails()
  16. {
  17. List<CollegeDetails> objCollegeDetails = null;
  18. try
  19. {
  20. objCollegeDetails = new CollegeDAL().GetAllCollegeDetails();
  21. }
  22. catch (Exception ex)
  23. {
  24. throw ex;
  25. }
  26. return objCollegeDetails;
  27. }
  28. #endregion
  29. }
  30. }

Creating API Project

In this project we'll create the API project for the application. We will define the controller in the Area in the application. Let's start with the following procedure.

Step 1

Create a new folder on the Solution Explorer named "API" and in it add a new project using the Web API Project Template named "CollegeTrackerAPI".

Creating API Project

Step 2

Add an Area by right-clicking on the API project and select Add -> Area.

Adding Area

Step 3

Remove all except the controller folder and add a new folder named "BLL".

Step 4

Add a new class in the BLL folder named "CollegeBL".

Adding Class in API

Step 5

Replace the code with the following code:

  1. using System.Collections.Generic;
  2. using CollegeTrackerCore.BLL;
  3. using CollegeTrackerModels;
  5. namespace CollegeTrackerAPI.Areas.BLL
  6. {
  7. internal sealed class CollegeBL : CollegeBLCore
  8. {
  9. /// <summary>
  10. /// This method is used to get the College Details
  11. /// </summary>
  12. /// <returns></returns>
  13. internal new List<CollegeDetails> GetAllCollegeDetails()
  14. {
  15. return base.GetAllCollegeDetails();
  16. }
  17. }
  18. }

Step 6

Right-click on the controller and go to Add -> Controller and select empty Web API 2 Controller as shown below:

Adding Web API 2 Empty Controller

Step 7

Enter the controller named "CollegeDetails".

Step 8

Replace the code with the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Net;
  4. using System.Net.Http;
  5. using System.Web.Http;
  6. using CollegeTrackerModels;
  7. using CollegeTrackerAPI.Areas.BLL;
  9. namespace CollegeTrackerAPI.Areas.Controller
  10. {
  11. public class CollegeDetailsController : ApiController
  12. {
  13. #region Variable
  14. /// <summary>
  15. /// varibale for CollegeBL
  16. /// </summary>
  17. private CollegeBL objCollegeBL;
  18. /// <summary>
  19. /// variable for HttpResponseMessage
  20. /// </summary>
  21. HttpResponseMessage response;
  22. #endregion
  24. #region Response Method
  25. /// <summary>
  26. /// This method is used to fetch the College Details
  27. /// </summary>
  28. /// <returns></returns>
  29. [HttpGet, ActionName("GetAllCollegeDetails")]
  30. public HttpResponseMessage GetAllCollegeDetails()
  31. {
  32. objCollegeBL = new CollegeBL();
  33. HttpResponseMessage response;
  34. try
  35. {
  36. var detailsResponse = objCollegeBL.GetAllCollegeDetails();
  37. if (detailsResponse != null)
  38. response = Request.CreateResponse<List<CollegeDetails>>(HttpStatusCode.OK, detailsResponse);
  39. else
  40. response = new HttpResponseMessage(HttpStatusCode.NotFound);
  41. }
  42. catch (Exception ex)
  43. {
  44. response = Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
  45. }
  46. return response;
  47. }
  48. #endregion
  49. }
  50. }

Step 9

Open the Web.Config file and add the following code in the Connection String block:

  1. <add name="CollegeTrackerConnectionString" connectionString="Data Source=.; Initial Catalog=CollegeTracker; providerName="System.Data.SqlClient"/>

Creating User Interface Layer

We have almost created the application, now finally in this section we will create the User Interface and call the API Controller action method from the MVC Controller. To do this, follow the instruction below.

Step 1

Add a new controller by going to the Web Folder and in the CollegeTracker project go to the Controller folder and add a new controller as shown below:

Adding New Scaffolded Item

Step 2

Define the controller named "CollegeDetails" and add the following code to it:

  1. using CollegeTrackerModels;
  2. using System.Collections.Generic;
  3. using System.Net.Http;
  4. using System.Net.Http.Headers;
  5. using System.Threading.Tasks;
  6. using System.Web.Mvc;
  8. namespace CollegeTracker.Controllers
  9. {
  10. public class CollegeDetailsController : Controller
  11. {
  12. // GET: CollegeDetails
  13. public ActionResult Index()
  14. {
  15. return View();
  16. }
  18. [HttpGet, ActionName("getcollegelist")]
  19. public ActionResult GetCollegeList()
  20. {
  21. var list = new List<CollegeTrackerModels.CollegeDetails>();
  22. var httpClient = new HttpClient();
  23. httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
  24. HttpResponseMessage response;
  25. response = httpClient.GetAsync("http://localhost:59866/api/" + "CollegeDetails/GetAllCollegeDetails/").Result;
  26. response.EnsureSuccessStatusCode();
  27. List<CollegeDetails> cd = response.Content.ReadAsAsync<List<CollegeDetails>>().Result;
  28. return View("~/Views/CollegeDetails/CollegeDetails.cshtml",cd );
  29. }
  30. }
  31. }

Step 3

Go to Views/CollegeDetails and add an empty view named "CollegeDetails" and replace the code with the following code:

  1. @model IEnumerable<CollegeTrackerModels.CollegeDetails>
  2. @{
  4. ViewBag.Title = "College Details";
  5. }
  7. <table class="table">
  8. <tr>
  9. <th>
  10. @Html.DisplayNameFor(model=> model.CollegeName)
  11. </th>
  12. <th>
  13. @Html.DisplayNameFor(model => model.CollegeAddress)
  14. </th>
  15. <th>
  16. @Html.DisplayNameFor(model => model.CollegePhone)
  17. </th>
  18. <th>
  19. @Html.DisplayNameFor(model => model.CollegeEmailID)
  20. </th>
  21. <th>
  22. @Html.DisplayNameFor(model => model.ContactPerson)
  23. </th>
  24. <th>
  25. @Html.DisplayNameFor(model => model.State)
  26. </th>
  27. <th>
  28. @Html.DisplayNameFor(model => model.City)
  29. </th>
  30. </tr>
  31. @foreach (var item in Model) {
  32. <tr>
  33. <td>
  34. @Html.DisplayFor(modelItem=>item.CollegeName)
  35. </td>
  36. <td>
  37. @Html.DisplayFor(modelItem=> item.CollegeAddress)
  38. </td>
  39. <td>
  40. @Html.DisplayFor(modelItem => item.CollegePhone)
  41. </td>
  42. <td>
  43. @Html.DisplayFor(modelItem => item.CollegeEmailID)
  44. </td>
  45. <td>
  46. @Html.DisplayFor(modelItem => item.ContactPerson)
  47. </td>
  48. <td>
  49. @Html.DisplayFor(modelItem => item.State)
  50. </td>
  51. <td>
  52. @Html.DisplayFor(modelItem => item.City)
  53. </td>
  54. <td>
  55. @Html.ActionLink("Edit", "Edit", new { id = item.CollegeID }) |
  56. @Html.ActionLink("Details", "Details", new { id = item.CollegeID }) |
  57. @Html.ActionLink("Delete", "Delete", new { id = item.CollegeID })
  58. </td>
  59. </tr>
  60. }
  61. </table>

Step 4

Go to the Views/Shared/_Layout.cshtml page and add an ActionLink with the following code:

  1. <li>@Html.ActionLink("College", "GetCollegeList", "CollegeDetails")</li>

Step 5

Go to the solution and right-click on it and select Set Startup Projects.

Set Startup Projects

Step 6

Since we have multiple projects we need to start the API Project and Web Project simultaneously. Select Start for both of the API and Web Projects.

Solution Property Pages

Step 7

Run the application and click on the College link.

Opening College View

Step 8

You can see the returned data from the API Controller.

College View

Summary

This article described how to call the Web API Controller method from the MVC Controller and view the returned data in the page. We will do the other operations in the next articles. Thanks for reading the article.