Implement the Entity Framework in the Web API

Question

I am Newbie to ASP.NET, created the MVC Framework Web API which accepts the array of ID's as input parameter and queries the Oracle DB, this should return the result in the JSON format.Our query is like

SELECT STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, 
      STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, 
      Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE
      from STCD_PRIO_CATEGORY 
      where STCD_PRIO_CATEGORY_DESCR.STD_REF IN(X,Y,Z)

where X,Y,Z are the values we will be passing as input parameters

I created the API controller as

  public class PDataController : ApiController
   {
    public HttpResponseMessage Getdetails([FromUri] string[] id)
    {
     List<OracleParameter> prms = new List<OracleParameter>();
     string connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
     using (OracleConnection dbconn = new OracleConnection(connStr))
    {
    var inconditions = id.Distinct().ToArray();
    var srtcon = string.Join(",", inconditions);
    DataSet userDataset = new DataSet();
    var strQuery = @"SELECT STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, 
      STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, 
      Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE          
      from STCD_PRIO_CATEGORY 
      where STCD_PRIO_CATEGORY_DESCR.STD_REF IN(";
    StringBuilder sb = new StringBuilder(strQuery);
     for(int x = 0; x < inconditions.Length; x++)
         {
           sb.Append(":p" + x + ",");
           OracleParameter p = new OracleParameter(":p" + x,OracleDbType.NVarchar2);
           p.Value = inconditions[x];
           prms.Add(p);
         }
    if(sb.Length > 0) sb.Length--;
    strQuery = sb.ToString() + ")"; 
    using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
      {
       selectCommand.Parameters.AddRange(prms.ToArray());
         using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
        {
            DataTable selectResults = new DataTable();
            adapter.Fill(selectResults);
            var returnObject = new { data = selectResults };
            var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
            ContentDispositionHeaderValue contentDisposition = null;
            if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
            {
                response.Content.Headers.ContentDisposition = contentDisposition;
            }
             return response;
}}}}}}

It works perfectly and returns the result as {"data":[{"CATEGORY":"Internal Study","SESSION_NUMBER":7,"SESSION_START_DATE":"2015-02-13T00:00:00"}]}

But would like to implement the entity framework here by using the Model and the DBContext.I created the model class and the DataContext Class as follows

namespace PSData.Models
{ public class StudyDataModel
{ [Key]
public string CATEGORY { get; set; }
public int SESSION_NUMBER { get; set; }
public DateTime SESSION_START_DATE { get; set; }
}}

And

namespace PSData.Models
{
 public class StudyDataContext:DbContext
 {
 public DbSet<StudyDataModel> details { get; set; }
 }}

I am not sure how to implement them in the controller. When I tried to create the Controller using Web API 2 Controller with actions,using Entity Framework selected both the Model Class and the DB Context Class it creates controller with

 private StudyDataContext db = new StudyDataContext();
 // GET: api/StdData
 public IQueryable<StudyDataModel> Getdetails()

I am not sure how to proceed as the return type is the HttpResponseMessage in my other Controller where I am returning the JSON message. Any help is greatly appreciayed

You need to create an object of your StudyDataContext and then you can access details property. I suggest you spent at-least 30 minutes going through the EF basics tutotials before building big systems. — Shyju
– Shyju, Commented Aug 12, 2016 at 4:15
@Shyju Yes I created the object as private StudyDataContext db = new StudyDataContext() So I understand if I use return db.details; it returns all the keys in the models class. But not sure how will be convertng them in to JSON before returning — trx
– trx, Commented Aug 12, 2016 at 4:19

Shyju · Accepted Answer · 2016-08-12 04:35:11Z

1

You do not need to explicitly convert it to json format. The content negotiation module and media formatter will take care of converting the data to the needed format (XML/JSON) based on the request. By default it returns JSON.

Assuming you have a DTO class like this

public class CategoryDto
{
  public string Category { get; set; }
  public int SessionNumber { get; set; }
  public DateTime SessionStartDate { get; set; }
}

and in your action method, you can use Request.CreateResponse method.

public HttpResponseMessage Get()
{
   var db = new StudyDataContext();
   var data = db.details.Select(x => new CategoryDto { 
                                                    Category = x.Category,
                                                    SessionStartDate  = x.SessionStartDate,
                                                    SessionNumber = x.SessionNumber }
                               ).ToList();
   return Request.CreateResponse(HttpStatusCode.OK, data);
}

answered Aug 12, 2016 at 4:35

Shyju

220k106 gold badges421 silver badges499 bronze badges

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

11 Comments

trx Over a year ago

Thanks. But rest of the things should be all the same in the Controller right. Like the Oracle Connection, giving the query

Shyju Over a year ago

I thought you wanted to replace all that data access code with your EF implemenation

trx Over a year ago

Yes I want to replace them. So we dont need any of the ` using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn)) { selectCommand.Parameters.AddRange(prms.ToArray()); using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand)) { DataTable selectResults = new DataTable(); adapter.Fill(selectResults);` May be I am little confused

Shyju Over a year ago

No. Just use the method i posted in the answer.

trx Over a year ago

How does it know the query

SELECT STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY,        STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER,        Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE       from STCD_PRIO_CATEGORY        where STCD_PRIO_CATEGORY_DESCR.STD_REF IN(X,Y,Z)

where will we be giving them

|

Collectives™ on Stack Overflow

Implement the Entity Framework in the Web API

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related