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:
- Visual Studio 2012 or later
- ASP.NET Web API 2 Project Template
- ASP.Net MVC 5 Project Template
Getting Started
Let's begin with the following sections:
- Creating Database
- Creating Project
- Creating Model
- Creating Data Access Layer
- Creating API Project
- Creating User Interface Layer
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:
- CREATE DATABASE [CollegeTracker]
-
- USE CollegeTracker
-
- CREATE TABLE [dbo].[CollegeDetails](
- [CollegeID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
- [CollegeName] [varchar](100) NULL,
- [CollegeAddress] [nvarchar](150) NULL,
- [CollegePhone] [bigint] NULL,
- [CollegeEmailID] [nvarchar](50) NULL,
- [ContactPerson] [varchar](50) NULL,
- [State] [varchar](100) NULL,
- [City] [varchar](100) NULL,
- )
Note: Insert some values into the table.
Step 2
Now, we'll create the Stored Procedure for reading the data with the following code:
- USE [CollegeTracker]
-
- Create Proc [dbo].[CT_CollegeDetails_Select]
-
- As
- Begin
- Select * From CollegeDetails
- 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:
- using System;
- using System.Collections.Generic;
- using System.ComponentModel.DataAnnotations;
-
- namespace CollegeTrackerModels
- {
-
-
-
- public class CollegeDetails
- {
- #region Properties
-
-
-
- public int CollegeID { get; set; }
-
-
-
- [Display(Name = "College Name")]
- public string CollegeName { get; set; }
-
-
-
- [Display(Name = "College Address")]
- public string CollegeAddress { get; set; }
-
-
-
- [Display(Name = "College Phone")]
- public Int64 CollegePhone { get; set; }
-
-
-
- [Display(Name = "College EmailID")]
- public string CollegeEmailID { get; set; }
-
-
-
- [Display(Name = "Contact Person")]
- public string ContactPerson { get; set; }
-
-
-
- public string State { get; set; }
-
-
-
- public string City { get; set; }
- #endregion
- }
- }
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".
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:
- using System;
- using System.Collections.Generic;
- using CollegeTrackerModels;
- using System.Configuration;
- using System.Data.SqlClient;
- using System.Data;
-
- namespace CollegeTrackerCore.DAL
- {
- public class CollegeDAL
- {
- #region Variables
- SqlConnection con;
- SqlCommand cmd;
- SqlDataAdapter adap;
- DataTable dt;
- DataSet ds;
- string connectionstring = ConfigurationManager.ConnectionStrings["CollegeTrackerConnectionString"].ConnectionString;
- #endregion
-
- #region Constructor
- public CollegeDAL()
- {
- con = new SqlConnection(this.connectionstring);
- }
- #endregion
-
- #region Public Method
-
-
-
-
- public List<CollegeDetails> GetAllCollegeDetails()
- {
- List<CollegeDetails> objCollegeDetails = new List<CollegeDetails>();
- using (cmd = new SqlCommand("CT_CollegeDetails_Select", con))
- {
- try
- {
- cmd.CommandType = CommandType.StoredProcedure;
- con.Open();
- adap = new SqlDataAdapter();
- adap.SelectCommand = cmd;
- dt = new DataTable();
- adap.Fill(dt);
-
- foreach (DataRow row in dt.Rows)
- {
- CollegeDetails col = new CollegeDetails();
- col.CollegeID = Convert.ToInt32(row["CollegeID"]);
- col.CollegeName = row["CollegeName"].ToString();
- col.CollegeAddress = row["CollegeAddress"].ToString();
- col.CollegePhone = Convert.ToInt64(row["CollegePhone"]);
- col.CollegeEmailID = row["CollegeEmailID"].ToString();
- col.ContactPerson = row["ContactPerson"].ToString();
- col.State = row["State"].ToString();
- col.City = row["City"].ToString();
- objCollegeDetails.Add(col);
- }
- }
- catch (Exception ex)
- {
- con.Close();
- }
- return objCollegeDetails;
- }
- }
- #endregion
- }
- }
Step 5
Now in the BLL folder add a new class named "CollegeBLCore".
![Adding Class]()
Step 6
Replace the code with the following code:
- using CollegeTrackerCore.DAL;
- using CollegeTrackerModels;
- using System;
- using System.Collections.Generic;
-
- namespace CollegeTrackerCore.BLL
- {
- public abstract class CollegeBLCore
- {
- #region Public Method
-
-
-
-
- protected List<CollegeDetails> GetAllCollegeDetails()
- {
- List<CollegeDetails> objCollegeDetails = null;
- try
- {
- objCollegeDetails = new CollegeDAL().GetAllCollegeDetails();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- return objCollegeDetails;
- }
- #endregion
- }
- }
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:
- using System.Collections.Generic;
- using CollegeTrackerCore.BLL;
- using CollegeTrackerModels;
-
- namespace CollegeTrackerAPI.Areas.BLL
- {
- internal sealed class CollegeBL : CollegeBLCore
- {
-
-
-
-
- internal new List<CollegeDetails> GetAllCollegeDetails()
- {
- return base.GetAllCollegeDetails();
- }
- }
- }
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:
- using System;
- using System.Collections.Generic;
- using System.Net;
- using System.Net.Http;
- using System.Web.Http;
- using CollegeTrackerModels;
- using CollegeTrackerAPI.Areas.BLL;
-
- namespace CollegeTrackerAPI.Areas.Controller
- {
- public class CollegeDetailsController : ApiController
- {
- #region Variable
-
-
-
- private CollegeBL objCollegeBL;
-
-
-
- HttpResponseMessage response;
- #endregion
-
- #region Response Method
-
-
-
-
- [HttpGet, ActionName("GetAllCollegeDetails")]
- public HttpResponseMessage GetAllCollegeDetails()
- {
- objCollegeBL = new CollegeBL();
- HttpResponseMessage response;
- try
- {
- var detailsResponse = objCollegeBL.GetAllCollegeDetails();
- if (detailsResponse != null)
- response = Request.CreateResponse<List<CollegeDetails>>(HttpStatusCode.OK, detailsResponse);
- else
- response = new HttpResponseMessage(HttpStatusCode.NotFound);
- }
- catch (Exception ex)
- {
- response = Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
- }
- return response;
- }
- #endregion
- }
- }
Step 9
Open the Web.Config file and add the following code in the Connection String block:
- <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:
- using CollegeTrackerModels;
- using System.Collections.Generic;
- using System.Net.Http;
- using System.Net.Http.Headers;
- using System.Threading.Tasks;
- using System.Web.Mvc;
-
- namespace CollegeTracker.Controllers
- {
- public class CollegeDetailsController : Controller
- {
-
- public ActionResult Index()
- {
- return View();
- }
-
- [HttpGet, ActionName("getcollegelist")]
- public ActionResult GetCollegeList()
- {
- var list = new List<CollegeTrackerModels.CollegeDetails>();
- var httpClient = new HttpClient();
- httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
- HttpResponseMessage response;
- response = httpClient.GetAsync("http://localhost:59866/api/" + "CollegeDetails/GetAllCollegeDetails/").Result;
- response.EnsureSuccessStatusCode();
- List<CollegeDetails> cd = response.Content.ReadAsAsync<List<CollegeDetails>>().Result;
- return View("~/Views/CollegeDetails/CollegeDetails.cshtml",cd );
- }
- }
- }
Step 3
Go to Views/CollegeDetails and add an empty view named "CollegeDetails" and replace the code with the following code:
- @model IEnumerable<CollegeTrackerModels.CollegeDetails>
- @{
-
- ViewBag.Title = "College Details";
- }
-
- <table class="table">
- <tr>
- <th>
- @Html.DisplayNameFor(model=> model.CollegeName)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.CollegeAddress)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.CollegePhone)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.CollegeEmailID)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.ContactPerson)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.State)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.City)
- </th>
- </tr>
- @foreach (var item in Model) {
- <tr>
- <td>
- @Html.DisplayFor(modelItem=>item.CollegeName)
- </td>
- <td>
- @Html.DisplayFor(modelItem=> item.CollegeAddress)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.CollegePhone)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.CollegeEmailID)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.ContactPerson)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.State)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.City)
- </td>
- <td>
- @Html.ActionLink("Edit", "Edit", new { id = item.CollegeID }) |
- @Html.ActionLink("Details", "Details", new { id = item.CollegeID }) |
- @Html.ActionLink("Delete", "Delete", new { id = item.CollegeID })
- </td>
- </tr>
- }
- </table>
Step 4
Go to the Views/Shared/_Layout.cshtml page and add an ActionLink with the following code:
- <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.