Create Web API in Web Form to pull from SQL database table

Question

I am trying to create a web API for an ASP.NET application. I'm following this tutorial: https://blogs.msdn.microsoft.com/jasonz/2012/07/23/my-favorite-features-entity-framework-code-first-and-asp-net-web-api/

The issue that I'm encountering is that I'm trying to use an existing database, and to pull from a specific table in the database. In the link provided above, it walks through creating a single table in the local database and making an API for it.

However, in my scenario, I am trying to use an existing database with several hundred tables, but only want to GET from 2-3 tables.

Note: I'm very certain that my connection string is set up correctly (it pulls appropriately when I use it in SQLConnection type objects).

How does the code know what table I'm trying to pull from? Is there some way that I should/could be using a stored procedure to pull the data I want instead?

Here's a sample of my code:

Models.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace Test_Site_1.Models
{
public class Review
    {
        [Key]
        public int Rating_id { get; set; }
        public int site_id { get; set; }
        public int usr_id { get; set; }
        public string valid { get; set; }
        public DateTime review_date { get; set; }
        public float score { get; set; }
        public string terminal { get; set; }
        public string gate_info { get; set; }
        public string deparr { get; set; }
        public string comments { get; set; }
    }
}

ReviewsContext.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace Test_Site_1.Models
{
    public class ReviewsContext : DbContext
    {
        public ReviewsContext() : base("name=ECConnectionString")
        {
        }

        public System.Data.Entity.DbSet<Test_Site_1.Models.Review> Reviews { get; set; }
    }
}

ReviewsController.cs

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 Test_Site_1.Models;

namespace Test_Site_1.Controllers
{
    public class ReviewsController : ApiController
    {
        private ReviewsContext db = new ReviewsContext();

        // GET: api/Reviews
        public IQueryable<Review> GetReviews()
        {
            return db.Reviews;
        }

        public List<Review> GetReviews(int? id=null)
        {
            ReviewsContext db = new ReviewsContext();
            //var reviews = db.UserProfiles.ToList();
            return db.Reviews.ToList();
        }

Rutger Vk · Accepted Answer · 2018-08-13 17:25:48Z

1

I think the best way is to reverse engineer your database for entity framework code first

See this link: https://msdn.microsoft.com/en-us/library/jj200620(v=vs.113).aspx

This will reverse engineer the db, and with help of entity framework you would be able to query your database really easily.

note I see you are returning an IQueryable in your API. My guess is that you want to return an IEnumerable.

    public IQueryable<Review> GetReviews()
    {
        return db.Reviews.AsEnumerable();
    }

answered Aug 13, 2018 at 17:25

Rutger Vk

2005 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

phroureo Over a year ago

Will an Entity Data Model work with my Web Forms application? (It's a legacy application that we lack the manpower to update to anything newer right now).

Rutger Vk Over a year ago

I think the best way to do it is create a class library, in this library using EF query your database. add it as a reference and call these methodes from your web forms controller.

phroureo Over a year ago

Okay, I walked through the guide you showed me, but it didn't do anything besides recreate the classes I already had. I'm still not sure how to turn it into an API from here. :/

Rutger Vk Over a year ago

you have an API controller, return the data with your API controller, go to the browser and see if it return the data. it should be as easy as that

phroureo Over a year ago

Thanks! That helped me to figure some of it out. I also used parts of this walkthrough: c-sharpcorner.com/UploadFile/4b0136/… It helped me to re-generate the controller in a way that worked.

Collectives™ on Stack Overflow

Create Web API in Web Form to pull from SQL database table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related