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.

  1. create database Mudita
  2. use Mudita
  3. create table Employee(ID int IDENTITY,Name varchar(20), Address Varchar(40))
  4. Insert into Employee Values('Mudita','Kanpur')
  5. Insert into Employee Values('Tanya','Lucknow')
  6. Select * from Employee
  7. Drop table Employee

Step 2

Now create a Web API Application:

Step 3

Add a Model Class.

Add the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Data;
  6. using System.Text;
  7. using System.Configuration;
  8. using System.Data.SqlClient;
  9. namespace URLParameterAPI.Models
  10. {
  11. public class Employee
  12. {
  13. public string Name { get; set; }
  14. public string Address { get; set; }
  15. SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP44\\SQLSERVER;Initial Catalog=Mudita;User ID=sa;Password=password@123");
  16. public List<Employee>GetEmployee(int ID)
  17. {
  18. List<Employee> obj = new List<Employee>();
  19. con.Open();
  20. SqlCommand cmd = new SqlCommand("Select * from Employee where ID=" + ID, con);
  21. SqlDataReader rd = cmd.ExecuteReader();
  22. while (rd.Read())
  23. {
  24. Employee e = new Employee();
  25. e.Name = rd.GetString(1);
  26. e.Address = rd.GetString(2);
  27. obj.Add(e);
  28. }
  29. return obj;
  30. }
  31. }
  32. }
  35. }

Step 4

Add a Controller:

Add the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.SqlClient;
  5. using System.Linq;
  6. using System.Web;
  7. using System.Web.Mvc;
  8. using URLParameterAPI.Models;
  9. namespace URLParameterAPI.Controllers
  10. {
  11. public class EmployeeController : Controller
  12. {
  13. //
  14. // GET: /Employee/
  15. public ActionResult Index(int ID)
  16. {
  17. Employee obj1 = new Employee();
  18. List<Employee> li = obj1.GetEmployee(ID);
  19. return View(li.ToList());
  20. }
  21. }
  22. }
Step 5

Add a View:

Add the following code:

  1. @model IList<URLParameterAPI.Models.Employee>
  2. @{
  3. Layout = null;
  4. }
  5. <!DOCTYPE html>
  6. <html>
  7. <head>
  8. <meta name="viewport" content="width=device-width" />
  9. <title>Index</title>
  10. </head>
  11. <body>
  12. <div>
  13. <ul>
  14. @{
  15. foreach(var o in Model)
  16. {
  17. <li>Name:</li> @o.Name;
  18. <li>Address:</li> @o.Address;
  19. }
  20. }
  21. </ul>
  22. </div>
  23. </body>
  24. </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