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 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);
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments