Tuesday, February 18, 2014

How to invoke a stored procedure in Web API using Entity Framework

In this tutorial, we will go over sample code to retrieve data with Web API and Entity Framework (invoking a stored procedure to return data).

 Before getting started, I installed Entity Framework 4.3.0. To learn more about installation, click here....

 For this example, I had installed Northwind database and created a sample stored procedure that would return few records, which we will fetch through Web API methods in this illustration...

Create a sample procedure as below...



   To start with, create a folder called Data Model. Right click on Data Model and select add new item...


In the above, I created a new ADO.NET Entity Data Model named DBEntity. I will be using this one for the tutorial.

 I prefer to create an empty data model and add stored procedures as I go. (this works well with just stored procedures approach)...



Once you click finish, this should open up DBEntity.edmx file. Right click on Stored Procedures and click "update model from database"



Once you do that, make sure it has the connection to the DB server it needs to, if not click on New Connection and enter the server details and make sure the connection is working fine by clicking on Test Connection.

Finally we should see a window where it shows up with three tabs, Add, Refresh and Delete... In the Add tab, expand stored procedures and you should see the one created in first step. Check on the TestProc stored procedure and click finish... (refer below..)


Now, our DBEntity.edmx should be like the one below, with new stored procedure added to it...  To include a stored procedure, right click on Function Import  and select Add function import to add stored procedure.




In the window that appears,  select TestProc from stored procedure name and give "TestProc" for Function import name..

Once you click on get column information, this should give the set of columns that will be returned. Now click on the button "Create New Complex type" and this should create a new result-set as "TestProc_Result". Finally the screen should look like the below image.....  If so, then click ok...



Now, its time to get started on invoking the stored procedure and testing the results....  You can find the entity name in web.config and use it for data retrieval... (here the name is DBEntityContainer)

In my WebApiTestController, will add the following code (class and implementation to retrieve data).

 public class CategoryList
        {
            public int CategoryID { get; set; }
            public string CategoryName { get; set; }
            public string CategoryDescription { get; set; }
        }

        public IList<CategoryList> GetSampleData()
        {

            List<CategoryList> catList = new  List<CategoryList>();

            using(WebAPI.DataModel.DBEntityContainer test = new DataModel.DBEntityContainer())
            {
                var result = test.TestProc().ToList();

                foreach (var item in result.ToList())
                {
                    catList.Add(new CategoryList { CategoryID = item.CategoryID, CategoryName = item.CategoryName, CategoryDescription = item.Description});
                }
                return catList;
            }

        }


Now press F5 and you should be able to see the output as below...



Happy coding...



9 comments:

  1. Replies
    1. So the code at the bottom of this page goes in WebApiTestController? Am I missing something? Am getting a bunch of errors putting it in the controller. Wouldn't that go in the model and be called by the controller? Please help me get this to work. Spent too much time building it to quit now. Thanks.

      Delete
    2. Complete Code:


      using System;
      using System.Collections.Generic;
      using System.Data;
      using System.Data.Entity;
      using System.Data.Entity.Infrastructure;
      using System.Linq;
      using System.Net;
      using System.Net.Http;
      using System.Web.Http;
      using System.Web.Http.Description;
      using WebApplication1.Models;

      namespace WebApplication1.Controllers
      {
      public class GetRoomsController : ApiController
      {
      public class RoomsList
      {
      public int room_id { get; set; }
      public string room_name { get; set; }
      }

      public IList Get(int id)
      {
      List RoomList = new List();
      var result = new DBEntity().GetRooms(id).ToList();

      foreach (var item in result.ToList())
      {
      RoomList.Add(new RoomsList { room_id = item.room_id, room_name = item.room_name });
      }
      return RoomList;
      }
      }
      }

      Delete
  2. Hi, did you get it to work? sorry for the late response, hope you got an answer by now.

    ReplyDelete
  3. could you upload your project in github or email to me ?

    ReplyDelete
    Replies
    1. Not sure if I have it yet, as I keep changing my projects for testing different options. Are you having issues implementing the above steps?

      Delete
  4. Can you give me full screenshoot your WebApiTestController? thanks.

    ReplyDelete
    Replies
    1. Hi, I dont have that source code/environment setup with me. Let me know if you have any errors in specific.

      Thanks,
      Siva

      Delete
  5. I think REST API and SQL both help in solving complex IT problems and are actually very useful in their own aspects.

    SQL Server Load Rest Api

    ReplyDelete