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

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.

Eddie Shipman: Thank you. I'm new to API and MVC, so my searching has not come up with much yet.

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

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

Select allOpen 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
        }
    }

Select allOpen in new window

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

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/

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?

"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.

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.

Post your full code, please, if possible.

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.

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
        }
    }

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.

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

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/

I really wish people would learn to use the code tags, geez...

// 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

Select allOpen 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'     

Select allOpen 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.

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!!!

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

Select allOpen in new window

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

Select allOpen in new window

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
        }
    } 

Select allOpen in new window

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.

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.

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.

Thanks.

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')

Select allOpen 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.

Use something like Google Drive, I suppose.

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.