Querying Oracle DB using query parameters Web API

Question

We are currently having a view in the Oracle DB. We need to create a Web API that accepts the input parameters and queries the view in the Oracle DB and returns the response in the JSON format. I am new to ASP.NET and the web services. Below is the code for the service

namespace TGSSample.Controllers
{
public class TGSSampDataController : ApiController
{
    public HttpResponseMessage Getdetails([FromUri] string id)
    {

        List<OracleParameter> prms = new List<OracleParameter>();
        List<string> selectionStrings = new List<string>();
        string connStr = ConfigurationManager.ConnectionStrings["TGSDataConnection"].ConnectionString;
        using (OracleConnection dbconn = new OracleConnection(connStr))
        {
            DataSet userDataset = new DataSet();
            var strQuery = "SELECT * from LIMS_SAMPLE_RESULTS_VW where JRS_NO =" + id;

            var returnObject = new { data = new OracleDataTableJsonResponses(connStr, strQuery, prms.ToArray()) };
            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;
        }
    }

I am trying to debug and in the URL I gave like http://localhost:6897/api/TGSSampData?id=379 but it throws error like enter image description here I havent changed anything with the RouteConfig.cs or WebApiConfig.cs.

namespace TGSSample
{
 public static class WebApiConfig
 {
    public static void Register(HttpConfiguration config)
    {
        config.MapHttpAttributeRoutes();

        config.Routes.MapHttpRoute(
            name: "DefaultApi",
            routeTemplate: "api/{controller}/{id}",
            defaults: new { id = RouteParameter.Optional }
        );        }}}

I am not sure why I am getting the error. I have changed anything or not renamed.Can anyone please help me with this

Your error message says you tried /api/TGSData not /api/TGSSampData - could that be the issue? — Beno
– Beno, Commented Nov 16, 2016 at 5:29
No controller is found on your path /api/TGSData, this is what the error saying to you. Also you have an error in building of your query. Your id should be in '', but better use OracleCommandParameters ! — mybirthname
– mybirthname, Commented Nov 16, 2016 at 5:31
@Beno My apologies. I tried changing the application and missed updating the screenshot. I have updated the question — trx
– trx, Commented Nov 16, 2016 at 5:35

Nkosi · Accepted Answer · 2016-11-16 05:45:24Z

1

Parameter Binding in ASP.NET Web API

Using [FromUri]

To force Web API to read a complex type from the URI, add the [FromUri] attribute to the parameter.

Remove the [FromUri] attribute and you can use the [HttpGet] attribute as well.

public class TGSSampDataController : ApiController {
    //according to convention-based route mapping in webapiconfig
    //api/{controller}/{id} should map the following to this action
    //GET api/TGSSampData?id=379 
    //GET api/TGSSampData/379 
    [HttpGet]
    public HttpResponseMessage Get(string id) { ... }
}

answered Nov 16, 2016 at 5:45

Nkosi

249k38 gold badges472 silver badges506 bronze badges

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

9 Comments

trx Over a year ago

NKosi I made the change but still getting the same error. I tried giving as api/TGSSampData?id=379 and also api/TGSSampData/379

Nkosi Over a year ago

Are there any other actions in the controller that was not included in the example?

trx Over a year ago

No this is the only one, I have other classes for dealing with the JSON

trx Over a year ago

How do we verify that. When I start the debugger http://localhost:24649/ the Home page comes up.

Nkosi Over a year ago

try calling an api controller via the default route in the browser or using postman or fiddler.

|

Collectives™ on Stack Overflow

Querying Oracle DB using query parameters Web API

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related