Call API to search a database from an ASP.Net MVC application

I have an ASP.Net Core MVC website. I also have an ASP.Net Web API. I created a simple search function within the API to search an Oracle database with a search term input by a user into a textbox. What I want is to do the search from the MVC application. So I need to call the API and send it the search term, then return any results for display back to the MVC app. I believe I need to use GET to bring back data. Is there an example I could use? I am using Visual Studio 2017 and C#. Any help would be appreciated.
dodgerfanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eddie ShipmanAll-around developerCommented:
You create an action in your WebApi controller to return the data and call it from the MVC app using HttpWebRequest or WebClient.
There really is no ready made example for that, however, I can whip one up for you later this evening.
0
dodgerfanAuthor Commented:
Eddie Shipman: Thank you. I'm new to API and MVC, so my searching has not come up with much yet.
0
Eddie ShipmanAll-around developerCommented:
Yeah, I've been doing MVC since around 2012 and wrote some WebApi2 services the last 2 yrs. I am currently working on ASP.NetCore microservices
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Eddie ShipmanAll-around developerCommented:
How are you going to return the search results? In an array of strings? This is coded off the top of my head but should get you started:

In your MVC application:
    public class SearchValue 
    {
        public string value { get; set; }
    }

    public class SearchResults 
    {
        public string[] Results { get; set; }
        public string Message { get; set; }
    }
    
    
    public IActionResult Search(string SearchVal) // this value comes from the HTML form
    {
    
        // We have the search value in Val.value Call the function to return
        // the search results to the ViewBag...
        // Call the WebAPI to do the Search
        var httpWebRequest = (HttpWebRequest)WebRequest.Create(BaseUrl + "/search/" + SearchVal);
        httpWebRequest.Method = "GET";
        try
        {
            SearchResults resp = new SearchResults();
            var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                var result = streamReader.ReadToEnd();
                resp.Results = JsonConvert.DeserializeObject<SearchResults>(result);
                resp.message = "success";                
            }
        }
        catch (Exception e)
        {
            SearchResults resp = new SearchResults();
            resp.message = e.Message;
            resp.Results = null;
        }
        ViewBag.SearchResults = resp;    
        return View("SearchResults");   
    }

Open in new window


In your WebApi:
    public class SearchValue 
    {
        public string value { get; set; }
    }

    public class SearchResults 
    {
        public string[] Results { get; set; }
        public string Message { get; set; }
    }
    

    [HttpPost("search")]
    [ProducesResponseType(typeof(SearchResults), 200)]
    public IActionResult Search(string SearchVal) // This value comes from the API call in yor MVC Application
    {
        try
        {
            SearchResults resp = new SearchResults();
            // Call function to get the results from Oracle with the SearchVal and put in resp.Results;
            rsp.results = OracleSearchFunction(SearchVal);
            resp.message = "success";
            return Ok(resp); // This automatically formats the response to JSON
        }
        catch (Exception e)
        {
            var r = new InternalServerErrorResult(e)
            {
                StatusCode = 500,
                Value = e.Message
            };
            return r; // This automatically formats the respons to JSON
        }
    }

Open in new window

0
Eddie ShipmanAll-around developerCommented:
I will say that I've seen some examples of doing searches using MVC5 but they all entail using EntityFramework to do the db connections and not having a WebApi backend. See them here:
https://www.google.com/search?q=MVC+5+search+function&spell=1&sa=X&ved=0ahUKEwj-4p6_w4rYAhWoRN8KHdjLCM4QBQgmKAA&biw=1920&bih=1014
0
dodgerfanAuthor Commented:
Thanks again for the help. This should get me going. And yes, an array of strings. In my searching I found an article discussing how to use Oracle with the EntityFramework. I intend to dive into that, too.

https://csharp.today/entity-framework-6-database-first-with-oracle/
0
dodgerfanAuthor Commented:
I'm still trying to get this problem working. I do have a search function in the API that works. I enter in a term and it searches every field in a table and returns the results to the view. I need to call that function from the MVC application. Right now I have a search page in the MVC app with a text box and a button. I have it calling the API, but I have it returning a string with the text from the search box. It calls http://localhost:00000/Values/GetData.

[HttpPost]
publci string GetData(string item)
{
return "Here are your results: " + item;
}

The code you posted gave me issues with ResponseType(typeof. . . Is there a reference I missed?
0
Eddie ShipmanAll-around developerCommented:
"The code you posted gave me issues with ResponseType(typeof. . . Is there a reference I missed?"
My bad, the code I posted is for ASP.Net Core. It really is only for documentation purposes with Swagger (via Swashbuckle). You can actually take it out.
0
dodgerfanAuthor Commented:
The API is .net framework. The MVC is a ASP.Net Core Web app. I took this line out: [ProducesResponseType(typeof(SearchResults), 200)].
I get an error, HttpPostAttribute does not contain a constructor that takes 1 argument on this line: [HttpPost("search")].
I get an erro here to, on the status code lines: StatusCode = 500,
                Value = e.Message
In the MVC part, the only thing throwing an error is the Json line, resp.Results = JsonConvert.DeserializeObject<SearchResults>(result);. Cannot implicitly convert type to string. Progress, but slowly.Thanks.
0
Eddie ShipmanAll-around developerCommented:
Post your full code, please, if possible.
0
dodgerfanAuthor Commented:
Will do. I'll get it posted tomorrow. It's on a separate network, so I can't cut and paste. But I'll get it up. Thanks.
0
dodgerfanAuthor Commented:
This is the SearchController in the Asp.net core web app. I've noted the errors as comments:
ASP.Net Core Web App
SearchController.cs

using System;
using System.Net;
using System.IO;
using Netonsoft.Json;

namespace csweb.Controllers
{
public class SearchController: Controller
{
public class SearchValue
    {
        public string value { get; set; }
    }

    public class SearchResults
    {
        public string[] Results { get; set; }
        public string Message { get; set; }
    }

    public IActionResult Search(string SearchVal) // this value comes from the HTML form
    {
        var httpWebRequest = (HttpWebRequest)WebRequest.Create(BaseUrl + "/search/" + SearchVal);
        httpWebRequest.Method = "GET";
        try
        {
            SearchResults resp = new SearchResults();
            var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                var result = streamReader.ReadToEnd();
                resp.Results = JsonConvert.DeserializeObject<SearchResults>(result);   // error here: "cannot implicitly convert type 'cswen.Controllers.SearchController.SearchResults' to 'string[]'
                resp.message = "success";                
            }
        }
        catch (Exception e)
        {
            SearchResults resp = new SearchResults();
            resp.message = e.Message;
            resp.Results = null;
        }
        ViewBag.SearchResults = resp;    
        return View("SearchResults");  
    }

public IActionResult Index(string item)
{
return View();
}
}
}
}

This is the search controller in the web api part:
API
SearchController.cs

using Microsoft.AspNetCore.Mvc;
using System;
using System.Web.Http;
using System.Web.Http.Results;

namespcae csapi.Controllers
{
public class SearchController: ApiController
{
 public class SearchValue
    {
        public string value { get; set; }
    }

    public class SearchResults
    {
        public string[] Results { get; set; }
        public string Message { get; set; }
    }
   
    [HttpPost("search")]   // 'error: HttpPostAttribute' does not contain a constructor that takes 1 arguments
    public IActionResult Search(string SearchVal) // This value comes from the API call in yor MVC Application
    {
        try
        {
            SearchResults resp = new SearchResults();
            // Call function to get the results from Oracle with the SearchVal and put in resp.Results;
            rsp.results = OracleSearchFunction(SearchVal);
            resp.message = "success";
            return Ok(resp); // error: Cannot implicitly convert type 'System.Web.Http.Results.OkNegotiatedContentResult<csapi.Controllers.SearchCpntroller.SearchResults> to

Microsoft.AspNetCore.Mvc.IActionResult. An explicit conversion exits (are you missing a cast?)
        }
        catch (Exception e)
        {
            var r = new InternalServerErrorResult(e)
            {
                StatusCode = 500,  // error: InternalServerErrorResults does not contain a definition for 'StatusCode'
                Value = e.Message // error: InternalServerErrorResults does not contain a definition for 'Value'
            };
            return r; // This automatically formats the respons to JSON
        }
    }
0
dodgerfanAuthor Commented:
My database connection sits in another controller in the API but I can move that to the search controller in the API. I used code from a SQL Server example and converted it to Oracle. I'm not quite sure how to integrate it with what you posted. I also have a View, a Search.cshtml page in the MVC app that is supposed to display the results from the search.
0
dodgerfanAuthor Commented:
This is the database code:
public class ShowDetail
     {
         public List<Detail> Search(List<string> Information)
         {
              StringBuilder Buildsql = new StringBuilder();
              Buildsql.Append("select * from MyData where ");
              foreach (string value in Information)
             {
               Buildsql.AppendFormat("([Name] like '%{0}%' or [Appointment] like '%{0}%' or[Technology] like '%{0}%' or[Task] like '%{0}%') and ", value);
             }
            string datasql = Buildsql.ToString(0, Buildsql.Length - 5);
            return QueryList(datasql);
         }

            protected List<Detail> QueryList(string text)
         {
            List<Detail> lst = new List<Detail>();
           OracleCommand cmd = GenerateSqlCommand(text);
           using (cmd.Connection)
             {
                 OracleDataReader reader = cmd.ExecuteReader();
                 if (reader.HasRows)
                 {
                    while (reader.Read())
                     {
                      lst.Add(ReadValue(reader));
                     }
                 }
             }
             return lst;
         }

protected SqlCommand GenerateSqlCommand(string cmdText)
         {
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString);
OracleCommand cmd = new OracleCommand(cmdText, con);
cmd.Connection.Open();
return cmd;
         }

 protected Detail ReadValue(OracleDataReader reader)
         {
Detail dt = new Detail();
dt.bID = (int)reader["ID"];
dt.bName = (string)reader["Name"];
dt.bAppointment = (string)reader["Appointment"];
dt.bTechnology = (string)reader["Technology"];
dt.bTask = (string)reader["Task"];
return dt;
 }
0
dodgerfanAuthor Commented:
I used this example to get me started orginally, which is where some of my code comes from:
http://www.c-sharpcorner.com/UploadFile/2b481f/searching-data-from-the-database-in-Asp-Net-web-api/
0
Eddie ShipmanAll-around developerCommented:
I really wish people would learn to use the code tags, geez...
0
Eddie ShipmanAll-around developerCommented:
// error here: "cannot implicitly convert type 'cswen.Controllers.SearchController.SearchResults' to 'string[]'
// Change the the line below until you show me what the value actually is.
// resp.Results = JsonConvert.DeserializeObject<SearchResults>(result); 
resp.Results =  results;
resp.Message = "success";  // Capitalize the 'M' in messages

Open in new window


This is coming from the SearchResults.Results being an array of strings. Do you know how your result is coming over? Can you show me a result from the call?

// error: HttpPostAttribute' does not contain a constructor that takes 1 arguments
// error: Cannot implicitly convert type 'System.Web.Http.Results.OkNegotiatedContentResult<csapi.Controllers.SearchCpntroller.SearchResults> 
//        to  Microsoft.AspNetCore.Mvc.IActionResult. An explicit conversion exits (are you missing a cast?)                                          
// error: InternalServerErrorResults does not contain a definition for 'StatusCode'
// error: InternalServerErrorResults does not contain a definition for 'Value'     

Open in new window

The errors above tell me that you must not be using Microsoft.AspNetCore.Mvc in your ASP.Net Core WebApi solution.
The InternalServerErrorResults and the other errors are being caused because you are using System.Web.Http instead.
0
Eddie ShipmanAll-around developerCommented:
Also, can you show me some example SQL + results that you are using? I may be able to write a query to return JSON directly from Oracle for you using XMLAgg.

Oh, BTW, I've been an AstrosFan my whole life!!!
0
dodgerfanAuthor Commented:
I will try to remember to use the code tags. Sorry about that. To clarify one thing, the API is built using the full .net framework (4.6.1). The MVC is a ASP.Net Core web application. The API is the part talking to the Oracle database. The .Net Core app calls the API to do database activities. In this case, trying to search a table.
// error here: "cannot implicitly convert type 'cswen.Controllers.SearchController.SearchResults' to 'string[]'
// Change the the line below until you show me what the value actually is. I changed the line below to = results but I get the error the name results does not exist in the current context.
// resp.Results = JsonConvert.DeserializeObject<SearchResults>(result); 
resp.Results =  results;
resp.Message = "success";  // Capitalize the 'M' in messages

Open in new window

0
dodgerfanAuthor Commented:
public List<Detail> Search(List<string> Information)
         {
              StringBuilder Buildsql = new StringBuilder();
              Buildsql.Append("select * from EmpData where ");
              foreach (string value in Information)
             {
               Buildsql.AppendFormat("([LName] like '%{0}%' or [FName] like '%{0}%' or[Title] like '%{0}%' or[Task] like '%{0}%') and ", value);
             }
            string datasql = Buildsql.ToString(0, Buildsql.Length - 5);
            return QueryList(datasql);
         }

Open in new window

0
dodgerfanAuthor Commented:
Ok, in my API, I moved the SearchController code over to an existing controller that has other code in it. It is now in the ValuesController and looks like this:
public class SearchValue
    {
        public string value { get; set; }
    }

    public class SearchResults
    {
        public string[] Results { get; set; }
        public string Message { get; set; }
    }
   
    [HttpPost]
    public string Search(string SearchVal)
    {
        try
        {
            SearchResults resp = new SearchResults();
            // Call function to get the results from Oracle with the SearchVal and put in resp.Results;
            rsp.results = OracleSearchFunction(SearchVal);
            resp.message = "success";
            return Ok(resp); // error: now the error is Ok does not exist in the current context
        }
        catch (Exception e)
        {
            var r = new InternalServerErrorResult(e)  // error: this one is still here but the other 2 below are gone.
            {
                StatusCode = 500,  
                Value = e.Message 
            };
            return r; // This automatically formats the respons to JSON
        }
    } 

Open in new window

0
Eddie ShipmanAll-around developerCommented:
To alleviate these
// error: InternalServerErrorResults does not contain a definition for 'StatusCode'
// error: InternalServerErrorResults does not contain a definition for 'Value'  

Open in new window

try this
catch (Exception e)
{
    return InternalServerError(e.Message);
}

Open in new window


So, how much did you actually follow the article you mentioned above? You are doing some things in a haphazard manner.
I would begin by writing the WebAPI controller to get the data from Oracle, first. Create a test to make sure that you are getting the data correctly and returning it as JSON. Then, I'd write my MVC controller to call the WebAPI function.

It seems that you are trying to do it the other way around here.  I'd also make sure that they were both written using the same framework as you can see the inconsistencies between them. Without having access to the Oracle table or, at lease, some example data, I can't really help any more due to the disparities between the projects.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dodgerfanAuthor Commented:
You're right, I have approached this haphazardly. I used that article to build a page within the API project to do the search. The changes I made were only to the database parts to substitute Oracle for SQL Server and my table name and field names. It works, and my display page looks just like the screen shots at the end of the article. The result is a table displayed in HTML with the fields and data. But it is not JSON. My problem is that i want to move that display page to the asp.net core MVC app and call that API functionality. I should have explained what i currently have working better when i posted here for help, rather than try to start from scratch. I apologize for that and wasting any of your time and effort. I will try to figure out how to move what I have working over to the asp.net core app.
0
Eddie ShipmanAll-around developerCommented:
If you'd like, we can take this off-line to finish up. I  would need both projects and some sample data ans table create scripts.
0
dodgerfanAuthor Commented:
I can't post what I have here. What I can do is recreate it using a SQL Server DB and post that. I don't care about the DB part, I have that figured out. It's the API and MVC stuff that I am having problems with. I'm just starting to use it now. I can create the functionality I have going with the 2 projects and get it posted. I will get the projects and sample data. Thanks.
0
Eddie ShipmanAll-around developerCommented:
Thanks.
0
dodgerfanAuthor Commented:
Here is the database SQL script:
create database Demo
use Demo
create table Baseball (ID int IDENTITY PRIMARY KEY,Name varchar(50),Position varchar(50),Talent varchar(50), Task varchar(MAX) )

Insert into Baseball values ('Clayton Kershaw',	'Pitcher', 'Best ever',	'Destroy competition')
Insert into Baseball values ('Cody Bellinger', 'First Base', 'About to be best ever', 'Destroy baseballs')
Insert into Baseball values ('Corey Seager', 'Short Stop', 'Human Vacuum Cleaner', 'Subject competition to humiliation')
Insert into Baseball values ('Kenley Jansen', 'Relief Pitcher', 'Closer	Destory hopes of competition')

Open in new window


How do I share the solution? I zipped it but it will not upload because of file extensions that are not allowed.
0
Eddie ShipmanAll-around developerCommented:
Use something like Google Drive, I suppose.
0
dodgerfanAuthor Commented:
Google drive link: https://drive.google.com/open?id=1HrHNUjThqe_0DoiNuiB2afS0R69O4RMv

The solution has 2 projects. The csapi has the code that works, where you load it and can search with the form. I need that form to be in the csweb (the ASP.Net Core mvc application). I want the .net core app to open up to a page and dispaly like the api page, but be able to call the api to perform the search and display the results. That's where I'm hitting the wall. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.