Introduction
This article explains how to fetch data from the database by passing the parameter from a URL in the MVC4 Web API. Here we need to create a database in SQL and insert the value in the table. We can fetch the data by the Id and when we pass the Id in the URL as a parameter then it fetches the data from the table.
Use the following procedure to create the sample application.
Step 1
First we need to create a database with a table in SQL.
create database Mudita
use Mudita
create table Employee(ID int IDENTITY,Name varchar(20), Address Varchar(40))
Insert into Employee Values('Mudita','Kanpur')
Insert into Employee Values('Tanya','Lucknow')
Select * from Employee
Drop table Employee
Now create a Web API Application:
- Start Visual Studio 2013.
- From the Start Window select "New Project".
- Select "Installed" -> "Templates" -> "Visual C#" -> "Web" -> "Visual Studio 2012" and select "ASP.NET MVC4 Web Application".
![Select MVC4 Web Application]()
- Click on the "OK" button.
- From the MVC4 project window select "Web API".
![Select Web API]()
- Click on the "Create Project" button.
Step 3
Add a Model Class.
- In the "Solution Explorer".
- Right-click on the Model Folder.
- Select "Add" -> "Class".
- Select "Installed" -> "Visual C#" and select "Class".
![Add Model Class]()
- Click on the "Ok" button.
Add the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
namespace URLParameterAPI.Models
{
public class Employee
{
public string Name { get; set; }
public string Address { get; set; }
SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP44\\SQLSERVER;Initial Catalog=Mudita;User ID=sa;Password=password@123");
public List<Employee>GetEmployee(int ID)
{
List<Employee> obj = new List<Employee>();
con.Open();
SqlCommand cmd = new SqlCommand("Select * from Employee where ID=" + ID, con);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
Employee e = new Employee();
e.Name = rd.GetString(1);
e.Address = rd.GetString(2);
obj.Add(e);
}
return obj;
}
}
}
Step 4
Add a Controller:
- In the "Solution Explorer".
- Right-click on the Controller folder, select "Add" -> "Controller".
- From the Template select "MVC Controoler".
![Add Controller]()
- Click on the "Add" button.
Add the following code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using URLParameterAPI.Models;
namespace URLParameterAPI.Controllers
{
public class EmployeeController : Controller
{
//
// GET: /Employee/
public ActionResult Index(int ID)
{
Employee obj1 = new Employee();
List<Employee> li = obj1.GetEmployee(ID);
return View(li.ToList());
}
}
}
Add a View:
- In the "EmployeeController".
- Right-click on the "Index" action Method.
- Select "Add View."
![Select Add View]()
- In the Add View dialog box select "Strongly Typed View" and select "Model class".
![Select View Setting]()
- Click on the "Add" button.
Add the following code:
@model IList<URLParameterAPI.Models.Employee>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<div>
<ul>
@{
foreach(var o in Model)
{
<li>Name:</li> @o.Name;
<li>Address:</li> @o.Address;
}
}
</ul>
</div>
</body>
</html>
Step 6
Execute the application:
![Execute the Home controller]()
Now set the URL as "http://localhost:14291/Employee/Index/2" and see the output. It fetches the data from the database.
![Display data from SQL]()