In this article I will explain with an example, how to create and consume a Web API that gets data from SQL Server Database in ASP.Net MVC Razor.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Entity Framework Model
Once the Entity Framework is configured and connected to the database table, the Model will look as shown below.
![Web API Database example in ASP.Net MVC]()
The Web API Project
Controller
In order to add a Web API Controller you will need to Right Click the Controllers folder in the Solution Explorer and click on Add and then Controller.
Now from the Add Scaffold window, choose the Web API 2 Controller – Empty option as shown below.
![Web API Database example in ASP.Net MVC]()
Then give it a suitable name and click OK.
![Web API Database example in ASP.Net MVC]()
The next task is to register the Configuration for Web API in the Global.asax file so that the Web API is available for accessing on Web.
In order to do so open Global.asax file and add the following line.
System.Web.Http.GlobalConfiguration.Configure(WebApiConfig.Register);
Make sure you add it in the same order as shown below.
public class MvcApplication : System.Web.HttpApplication
{
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
System.Web.Http.GlobalConfiguration.Configure(WebApiConfig.Register);
RouteConfig.RegisterRoutes(RouteTable.Routes);
}
}
The next step is to code the Web API Controller. The Web API Controller consists of a method named GetCustomers which accepts a string parameter i.e. name.
The records of the Customers are fetched using Entity Framework and are filtered using the StartsWith function based on the value of the Name property.
Finally, the records are returned as Generic List Collection.
This method is decorated with Route attribute which defines its Route for calling the Web API method and HttpGet attribute which signifies that the method will accept HTTP GET requests.
public class CustomerAPIController : ApiController
{
[Route("api/CustomerAPI/GetCustomers")]
[HttpGet]
public List<Customer> GetCustomers(string name)
{
NorthwindEntities entities = new NorthwindEntities();
return (from c in entities.Customers.Take(10)
where c.ContactName.StartsWith(name) || string.IsNullOrEmpty(name)
select c).ToList();
}
}
The MVC Project
Namespaces
You will need to import the following namespaces.
using System.Net.Http;
using System.Web.Script.Serialization;
Model
The CustomerModel class consists of the following properties.
public class CustomerModel
{
public string CustomerID { get; set; }
public string ContactName { get; set; }
public string City { get; set; }
}
Controller
The Controller consists of following two Actions method.
Action method for handling GET operation
Inside this Action method, the SearchCustomers method is called with empty string value as parameter and a Generic List of CustomerModel class objects is returned to the View.
Inside the SearchCustomers method, the URL of the Web API along with its Controller method and the value of the Name parameter is passed to the GetAsync method of the HttpClient class.
The GetAsync method of the HttpClient class calls the Web API’s Controller method i.e. the GetCustomers method and returns the JSON string which is then de-serialized to Generic List of Customer class objects.
Action method for handling POST operation
This Action method is called when the Form is submitted. Here also the SearchCustomers method is called, but the only difference is that the value of the TextBox received from the View is passed as parameter.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
List<CustomerModel> customers = SearchCustomers("");
return View(customers);
}
[HttpPost]
public ActionResult Index(string name)
{
List<CustomerModel> customers = SearchCustomers(name);
return View(customers);
}
private static List<CustomerModel> SearchCustomers(string name)
{
List<CustomerModel> customers = new List<CustomerModel>();
string apiUrl = "http://localhost:26404/api/CustomerAPI";
HttpClient client = new HttpClient();
HttpResponseMessage response = client.GetAsync(apiUrl + string.Format("/GetCustomers?name={0}", name)).Result;
if (response.IsSuccessStatusCode)
{
customers = (new JavaScriptSerializer()).Deserialize<List<CustomerModel>>(response.Content.ReadAsStringAsync().Result);
}
return customers;
}
}
View
Inside the View, in the very first line the CustomerModel class is declared as IEnumerable which specifies that it will be available as a Collection.
For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
The View also consists of an HTML Form which has been created using the Html.BeginForm method with the following parameters.
ActionName – Name of the Action. In this case the name is Index.
ControllerName – Name of the Controller. In this case the name is Home.
FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
Inside the Form, there is a TextBox and a Submit Button.
@using MVC_App.Models
@model IEnumerable<CustomerModel>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
<span>Name:</span>
<input type="text" name="name"/>
<input type="submit" value="Search"/>
}
<hr/>
<table cellpadding="0" cellspacing="0">
<tr>
<th>CustomerID</th>
<th>ContactName</th>
<th>City</th>
</tr>
@foreach (CustomerModel customer in Model)
{
<tr>
<td>@customer.CustomerID</td>
<td>@customer.ContactName</td>
<td>@customer.City</td>
</tr>
}
</table>
</body>
</html>
Screenshot
![Web API Database example in ASP.Net MVC]()
Downloads
