Introduction

This article shows how to use a Stored Procedure in the Web API. Here we use the select query of SQL for the procedure.

Now the following procedure will create the application.

Step 1

First we create a procedure. In SQL Server write the code for the procedure:

  1. create Procedure info_book
  2. AS
  3. BEGIN
  4. Select Name, Appointment from info
  5. END
  6. Go
Now for selecting the values of the procedure we use this query:
  1. EXEC info_book

The window looks like this:

pr15.jpg

Step 2

Now create the Web API Application:

Step 3

Add a Model class "InfoModel.cs".

Add the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. namespace MvcApplication7.Models
  6. {
  7. public class InfoModel
  8. {
  9. public List<info> infoData { get; set; }
  10. }
  11. public class info
  12. {
  13. public string Name { get; set; }
  14. public string Appointment { get; set; }
  15. }
  16. }

Step 4

Now we add the entity file for adding the procedure.

In the application there is add an "Model1.edmx" Diagram that looks like this.

pr6.jpg

Select "Model Browser". Now we will see that in the model browser, in the Function imports folder there is an info_book procedure.

pr7.jpg

Now right-click on the "function Imports" folder and select "Add Function Import". Open a window .

pr8.jpg

pr9.jpg

Write the function Import name "info_book1" and select "Stored Procedure /function Name". Now select the Complex radio button. Click on "Get Column Information"

then generate a table. And then click on "Create new Type". Finally click the "Ok" button.

Step 5

In the "HomeController" write some code. This file exists:

Add the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.Mvc;
  6. using MvcApplication7.Models;
  7. namespace MvcApplication7.Controllers
  8. {
  9. public class HomeController : Controller
  10. {
  11. public ActionResult Index()
  12. {
  13. DemoEntities objdemoentity = new DemoEntities();
  14. var studentercord = objdemoentity.info_book().ToList();
  15. InfoModel objmodel = new InfoModel();
  16. objmodel.infoData = new List<info>();
  17. foreach (var item in studentercord.ToList())
  18. {
  19. objmodel.infoData.Add(new info { Name = item.Name, Appointment = item.Appointment });
  20. }
  21. return View(objmodel);
  22. }
  23. }
  24. }

Step 6

Now in the "View" write some code. This exists:

Add the following code:

  1. @model MvcApplication7.Models.InfoModel
  2. @{
  3. ViewBag.Title = "Use of stored procedure with Web API";
  4. }
  5. <style>
  6. table
  7. {
  8. border-collapse:collapse;
  9. width:30%;
  10. }
  11. table,th, td
  12. {
  13. border: 1px solid black;
  14. }
  15. th,a
  16. {
  17. background-color:Gray;
  18. color:White;
  19. }
  20. </style>
  21. <h2>Use StoredProcedure With Web API <br />Stored Procedure in Web API</h2>
  22. @{
  23. var grid = new WebGrid(source: Model.infoData,rowsPerPage: 10);
  24. }
  25. @grid.GetHtml(alternatingRowStyle: "even",
  26. columns: grid.Columns(
  27. grid.Column("Name", header: "NAME"),
  28. grid.Column("Appointment", header: "APPOINTMENT")
  29. ))

Step 7

Now execute the application by pressing "F5".

pr10.jpg