stored procedure in sql with EF and web API

Dear Mr,

In case I need to use a stored procedure from DB in sql server and this stored procedure has many parameters and many join to many tables and return many fields from many table how to call and use it with WEB API Controller?

Many thanks and best regards,

Example : EF

public virtual DbSet T_Clubs { get; set; }

public virtual DbSet T_Games { get; set; }

public virtual DbSet T_Reservations { get; set; }

//HOW TO USE THIS WITH WEB API Controller this a stored procedure in DB ?!! PLease advice

public virtual int GetReservationsByClubId(Nullable clubId)

{

var clubIdParameter = clubId.HasValue ?

new ObjectParameter("ClubId", clubId) :

new ObjectParameter("ClubId", typeof(int));

return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("GetReservationsByClubId", clubIdParameter);

}

//HOW TO USE THIS WITH WEB API Controller this a stored procedure in DB ?!! PLease advice

public virtual ObjectResult GetReservations()

{

return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("GetReservations");

}

//HOW TO USE THIS WITH WEB API Controller this a stored procedure in DB ?!! PLease advice?

Thank you and best regards

HI ,

I tried out you requirement by creating simple sp. Again from web API side i didn't create any layer or any model in between just created on reservation controller with 2 action as following :

[Route("api/Reservation/GetReservation")]
[HttpGet]
public IEnumerable GetReservations()
{
List res = new List();
using (var dbContext = new PracticseEntities())
{
foreach(var item in dbContext.GetReservations())
{
res.Add(item);
}
}
return res;
}
[Route("api/Reservation/GetReservation/{clubID}")]
[HttpGet]
public IHttpActionResult GetReservationByID(int clubID)
{
List res = new List();
using (var dbContext = new PracticseEntities())
{
foreach (var item in dbContext.GetReservationsByClubId(clubID))
{
res.Add(item);
}
}
return Ok(res);
}

And According to my sp i am gettting following results :

Hope this will help

Regards,

Mukesh Nayak

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mukesh Nayak8y ago
HI ,

I tried out you requirement by creating simple sp. Again from web API side i didn't create any layer or any model in between just created on reservation controller with 2 action as following :
[Route("api/Reservation/GetReservation")]
        [HttpGet]
        public IEnumerable GetReservations()
        {
            List res = new List();
            using (var dbContext = new PracticseEntities())
            {
               foreach(var item in  dbContext.GetReservations())
                {
                    res.Add(item);
                }
            }

            return res;
        }


[Route("api/Reservation/GetReservation/{clubID}")]
        [HttpGet]
        public IHttpActionResult GetReservationByID(int clubID)
        {
            List res = new List();
            using (var dbContext = new PracticseEntities())
            {
                foreach (var item in dbContext.GetReservationsByClubId(clubID))
                {
                    res.Add(item);
                }
            }

            return Ok(res);
        }
And According to my sp i am gettting following results :
Hope this will help

Regards,
Mukesh Nayak