CODEWITHSUNDEEP
c#asp.netasp.net-web-apiwebforms
phroureo
phroureo

Reputation: 379

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

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();
        }

Upvotes: 0

Views: 586

Answers (1)

Rutger Vk
Rutger Vk

Reputation: 200

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();
    }

Upvotes: 1

Related Questions