Using The StoredProcedure With Select SQL Query in Web API

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:

create Procedure info_book
AS
BEGIN
Select Name, Appointment from info
END
Go

Now for selecting the values of the procedure we use this query:

EXEC info_book

The window looks like this:

Step 2

Now create the Web API Application: 

• Start Visual Studio 2012.

• From the start window select "New Project".

• From the new Project window select "Installed" -> "Visual C#" -> "Web".

• Select "ASP.NET MVC4 Web Application" and click the "OK" button.
  
  
  

• From the "MVC4 Project" window select "Web API".
  
  
  

• Click the "Ok" button.

Step 3

Add a Model class "InfoModel.cs".

• In the "Solution Explorer".

• Right-click on the Model folder.

• Select "Add" -> "Class".

• From the Add item window select "Installed" -> "Visual C#".
  
  
  

• Select "Class" and click the "Add" button.

Add the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MvcApplication7.Models
{
    public class InfoModel
    {
        public List<info> infoData { get; set; }
    }
    public class info
    {
        public string Name { get; set; }
        public string Appointment { get; set; }
    }
}

Step 4

Now we add the entity file for adding the procedure.

• In the "Solution Explorer".

• Right-click on the Project "MvcApplication7" then select "Add" -> "New Item".

• From add new item window select "Installed" -> "Visual C#" -> "Data".

• Select "ADO.NET Entity DataModel".

• Click the "Add" button.
  
  

• Now open a window; from that select "Generate from database".
  
  

• Click the "Next" button.

• Open a Entity Data Model wizard.
  
  

• Click the "New Connection" button. Open a Connection Property window.
  
  

• Click the "OK" button. Open a window.
  
  

• Select the Radio button "Yes, include the sensitive data in the connection string" and click the "Next" button. Open another window:
  
  

• Select the procedure and click the "Finish" button.

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

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

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

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:

• In the "Solution Explorer".

• Expand the "Controller" folder.

• Select the "HomeController".

Add the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcApplication7.Models;
namespace MvcApplication7.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            DemoEntities objdemoentity = new DemoEntities();
            var studentercord = objdemoentity.info_book().ToList();
            InfoModel objmodel = new InfoModel();
            objmodel.infoData = new List<info>();
            foreach (var item in studentercord.ToList())
            {
                objmodel.infoData.Add(new info { Name = item.Name, Appointment = item.Appointment });
            }
            return View(objmodel);
        }
    }
}

Step 6

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

• In the "Solution Explorer".

• Expand the "Views" folder.

• Select "Home" -> "index.cshtml".
  
  
  

Add the following code:

@model MvcApplication7.Models.InfoModel
@{
    ViewBag.Title = "Use of stored procedure with Web API";
}
<style>
table
{
border-collapse:collapse;
width:30%;
}
table,th, td
{
border: 1px solid black;
}
th,a
{
background-color:Gray;
color:White;
}
</style>
<h2>Use StoredProcedure With Web API <br />Stored Procedure in Web API</h2>
@{
var grid = new WebGrid(source: Model.infoData,rowsPerPage: 10);
}
@grid.GetHtml(alternatingRowStyle: "even",
    columns: grid.Columns(
            grid.Column("Name", header: "NAME"),
            grid.Column("Appointment", header: "APPOINTMENT")
))

Step 7

Now execute the application by pressing "F5".