problem with returning some lists in Json : ASP.NET Core

Question

I am creating an API which returns some results from a MySql database. I have a table including 2 rows and some fields. The main factor for query is a field called Title. The application should return results based on the "title" field. The titles of these two records are Sweet Street and Sweet Street 2.

for returning the results as JSON, I have created a class:

using System.Collections.Generic;
namespace IMES_Backend.Controllers
{
    internal class Movie
    {
        public class BaseResponse
        {
            public List<Item> search { get; set; } = new List<Item>();
            public bool response { get; set; }

        }

        public class Item
        {
            public string title { get; set; }
            public string year { get; set; }
            public string released { get; set; }
            public string runTime { get; set; }
            public string genre { get; set; }
            public string director { get; set; }
            public string writer { get; set; }
            public string actors { get; set; }
            public string language { get; set; }
            public string country { get; set; }
            public string awards { get; set; }
            public string poster { get; set; }
            public string imdbScore { get; set; }
            public string production { get; set; }
            public string dl480p { get; set; }
            public string dl720p { get; set; }
            public string dl1080p { get; set; }
            public string subtitleLink { get; set; }
            public string dubLink { get; set; }
            public string description { get; set; }
            public string state { get; set; }

        }
    }
}

Then I select that 2 rows as previously mentioned and return the results in JSON:

    [Route("IMES/api/GET/search/t={movieTitle}")]
    [HttpGet]
    public IActionResult MovieSearch(string movieTitle)
    {
        string searchKeyword = movieTitle.Replace("%20", " ");

        //Try to connect to the database
        try
        {
            DB.dbConnection.Open();
            MySqlCommand cmd = new MySqlCommand("select * from Movies where Title LIKE '%" + searchKeyword + "%'",DB.dbConnection);
            DB.dataReader = cmd.ExecuteReader();

            while (DB.dataReader.Read())
            {
                baseResponse.response = true;

                //Create a list for storing the movie's data
                baseResponse.search.Add(new Movie.Item
                {
                    title = DB.dataReader.GetString(1),
                    year = DB.dataReader.GetString(2),
                    released = DB.dataReader.GetString(3),
                    runTime = DB.dataReader.GetString(4),
                    genre = DB.dataReader.GetString(5),
                    director = DB.dataReader.GetString(6),
                    writer = DB.dataReader.GetString(7),
                    actors = DB.dataReader.GetString(8),
                    language = DB.dataReader.GetString(9),
                    country = DB.dataReader.GetString(10),
                    awards = DB.dataReader.GetString(11),
                    poster = DB.dataReader.GetString(12),
                    imdbScore = DB.dataReader.GetString(13),
                    production = DB.dataReader.GetString(14),
                    dl480p = DB.dataReader.GetString(15),
                    dl720p = DB.dataReader.GetString(16),
                    dl1080p = DB.dataReader.GetString(17),
                    subtitleLink = DB.dataReader.GetString(18),
                    dubLink = DB.dataReader.GetString(19),
                    description = DB.dataReader.GetString(20),
                    state = DB.dataReader.GetString(21),
                });

                string response = JsonConvert.SerializeObject(baseResponse);
                return Ok(response);

            }
        }
        catch(MySqlException ex) //MySql connection problem
        {
            return Ok(ex.Message);
        }
        finally
        {
            DB.dbConnection.Close();
            DB.dataReader.Close();
        }
        baseResponse.response = false;
        string error = JsonConvert.SerializeObject(baseResponse.response);
        return Ok(error);
    }

When I browse the .../IMES/api/GET/search/t=sweet, I just get the first rows data, there is no second list for the second row which includes the keyword sweet.

I want to get the both rows' data in separated lists in JSON. Can anyone help?

Note: I have tried the query in SQL Studio and I have received two records. So I'm sure about that 2 rows!

Kek · Accepted Answer · 2018-09-09 09:22:56Z

1

You're returning from the while loop

while(){
    retun Ok(response);
}

, so it is returned after the first item is added. Move it outside of the while loop

while(){
 //do stuff
}
string response = JsonConvert.SerializeObject(baseResponse);
return Ok(response);

answered Sep 9, 2018 at 9:22

Kek

3912 silver badges17 bronze badges

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

3 Comments

E. Mamaghani Over a year ago

Thanks for answering! that works. Can you please specify how can I name the lists? for example I want to name the list one as 'Item1' and the second as 'Item2'

Kek Over a year ago

I'd create a dictionary Dictionary<string, Item>, where string is either guid or something else ('Item1', 'Item2') P.S. If the answer works, could you please select it as answer (press the tick)?:)

David McEleney Over a year ago

also, I'd advise using parameterised SQL - anyone could drop your database with your current setup!

Collectives™ on Stack Overflow

problem with returning some lists in Json : ASP.NET Core

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related