convert dataset from json view .Net Web API-CodePudding

I am trying to create a chart in my angular application. for that I want to pass dataset as follows in my .Net Web API.

But in my code it pass data set as follows

[{"Column1":1,"_Month":"January","SumOfMonth":-14900.40,"NoOfOutlets":11},{"Column1":2,"_Month":"February","SumOfMonth":-17856.00,"NoOfOutlets":2},{"Column1":3,"_Month":"March","SumOfMonth":-5312.00,"NoOfOutlets":4},{"Column1":4,"_Month":"April","SumOfMonth":-22103.13,"NoOfOutlets":15},{"Column1":6,"_Month":"June","SumOfMonth":-16014.72,"NoOfOutlets":5},{"Column1":7,"_Month":"July","SumOfMonth":-63251.93,"NoOfOutlets":46}

now I want to convert my dataset to the above format to create the graph.

my controller

public class MonthSelloutController : ControllerBase
    {
        private readonly VantageContext _context;
        public MonthSelloutController(VantageContext context)
        {
            _context = context;
        }
        //GET: api/AllSellOut
        [HttpGet]
        public ActionResult<string> Getset(string Year)
        {
            DataTable dt = new MonthSelloutMgt().Monthsellout(Year, _context);
            string json = JsonConvert.SerializeObject(dt);
            return json;
        }
    }

My BL

public DataTable Monthsellout(string Year, VantageContext _context)
        {
            DataTable dt = new DataTable();
            try
            {
                string conn = _context.Database.GetDbConnection().ConnectionString;
                using (SqlConnection con = new SqlConnection(conn))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("D_MonthSellOut", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Year", Year));

                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            return dt;
        }

what I can do to convert my dataset as I mentioned in the above image. Any help is much appreciated.

result of my stored procedure

CodePudding user response：

Because you want to get a custom schema for your JSON string, you could try to write a class to carry your data from DataTable as below

public class ViewModel
{
    public List<List<object>> SumOfMonth { get; set; } = new List<List<object>>();
    public List<List<object>> NoOutlets { get; set; } = new List<List<object>>();
}

Then we can use foreach iterator data from DataTable and fill data in the ViewModel

[HttpGet]
public ActionResult<string> Getset(string Year)
{
    DataTable dt = new MonthSelloutMgt().Monthsellout(Year, _context);
    ViewModel result = new ViewModel();
    
    foreach(DataRow item in dt.Rows){
        result.SumOfMonth.Add(new List<object>(){
            (decimal)item["SumOfMonth"],
            item["_Month"].ToString()
        });

        result.NoOutlets.Add(new List<object>(){
            (decimal)item["NoOfOutlets"],
            item["_Month"].ToString()
        });
    }
    string json = JsonConvert.SerializeObject(result);
    return json;
}

I would use Sqldatareader to read data result instead of DataTable becasue Sqldatareader migth provide higher performance then DataTable