how to insert json array into mysql using .net core

Lahiru Wijesooriya 0
Viewed 37

I need to insert this json array data to MYSQL database. But I getting this System.Collections.Generic.List`1[TestAPI1.Models.." message in the database column, how do I fix this one

This my model class

{
    public class ResultDt
    {
        public string StID { get; set; }
        public List<SubjectsDt> StudentResults { get; set; }
        public string ExamYear { get; set; }
    }
    public class SubjectsDt
    {
        public string SubjectName { get; set; }
        public string SubjectMark { get; set; }
    }


}

This is the controller


 [HttpPost("results")]
    public string AddResults(ResultDt results)
    {
        string query = "INSERT INTO result_details(StID, StudentResults, ExamYear) VALUES (@StID,@StudentResults,@ExamYear)";

        string sqlDataSource = _configuration.GetConnectionString("DBConnection");

        using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
        {

            using (MySqlCommand mycommand = new MySqlCommand(query, mycon))
            {
                mycon.Open();
                mycommand.Parameters.AddWithValue("@StID", results.StID);
                mycommand.Parameters.AddWithValue("@StudentResults", results.StudentResults);
                mycommand.Parameters.AddWithValue("@ExamYear", results.ExamYear);

                int RowsAffected = mycommand.ExecuteNonQuery();

               if (RowsAffected == 0)
                {
                    return "ERROR - Check The Inputs";
                }
                else
                {
                    return "Results Details Succesfully Added";
                }

            }

        }
    }

This is the Json Array

{
    "StID":"ST01",
    "StudentResults":[
        {
            "SubjectName":"Science",
            "SubjectMark":78
        },
                {
            "SubjectName":"Maths",
            "SubjectMark":65
        },
        {
            "SubjectName":"ICT",
            "SubjectMark":90
        }
    ],
    "ExamYear":"2022"
}

MySQl database table

MySQL Table

asp.net-core mysql json arrays
1 Answers

results.StudentResults is not of string type and your DB framework attempt to convert it to string, usually by using ToString() method available in all objects. You have to tell the program to convert it into JSON, see How to serialize and deserialize (marshal and unmarshal) JSON in .NET:

mycommand.Parameters.AddWithValue(
    "@StudentResults",
    JsonSerializer.Serialize(results.StudentResults));

System.Collections.Generic.List`1 text is the default ToString behavior when you don't implement one yourself.

Luke Vo0
Related