Passing the Parameter in URL to Fetch Data From SQL

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:

  • 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:

  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 { getset; }  
  14.         public string Address { getset; }  
  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. }  
  33.           
  34.   
  35.     }   

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:

  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:

  • 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:

  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