Angular 6/C#/MVC Dynamically return JsonResult from controller in form of Json Array of Array

Question

I am trying to export a number of datasets to Excel in an Angular 6 application. For that I am using XLSX and File-save as given in this ex: https://medium.com/@madhavmahesh/exporting-an-excel-file-in-angular-927756ac9857

The problem is that the fields might keep changing time to time, so I need somthing dynamic (do not want to specify column names). For that I found a very good example: Binding Json data to a table in mvc 4

However, I am not getting the data in the right format. I need something like array of array. I am assuming the return type should be async Task<JsonResult> in that case but not sure how to make it work. I am attaching two images - of the result I am getting

and the intended result

Here is code from the sample cited. The stored proc call to GetMyData, uses SqlAdapter and there is no need to specify field names

 public JsonResult GetValue()
    {
        JsonResult json = new JsonResult();
        DataSet ds = GetMyData(); 
       /*LoadDoctordetailsNew is method where i get data from database and convert
          to dataset.It returns a dataset*/
        string returnData = GetJson(ds.Tables[0]);
        json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
        json.Data = returnData;            
        return json;
    }

    public static string GetJson(DataTable dt)
    {
        System.Web.Script.Serialization.JavaScriptSerializer serializer =
           new System.Web.Script.Serialization.JavaScriptSerializer();
        List<Dictionary<string, object>> rows =
           new List<Dictionary<string, object>>();
        Dictionary<string, object> row = null;

        foreach (DataRow dr in dt.Rows)
        {
            row = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                row.Add(col.ColumnName, dr[col]);
            }
            rows.Add(row);
        }
        return serializer.Serialize(rows);
    }

public static DataSet GetMyData()
        {
            try
            {
                using (SqlConnection connection = Connection.GetConnection())
                {
                  SqlDataAdapter da = new SqlDataAdapter("dbo.MySQLStoredproc", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

Please guide!

P.S.

This technique is using Dapper. It returns data as desired (Array of Array) format, but in the Object we have to specify column names which will be hard coding and therefore, I can't use

public static IEnumerable<Object> GetMyData()
        {

            var strQuery = @"[dbo].[Myproc]";        
            IEnumerable< Object > items = new List<Object>(0);
            var p = new DynamicParameters();

            using (SqlConnection con = Connection.GetConnection())
            {
                items = con.Query<Object>(strQuery, param: p, commandTimeout: 120, commandType: System.Data.CommandType.StoredProcedure);
            }
            return items;
        }

Call from MVC Controller

[HttpPost]
public async Task<JsonResult> SelectMyData()
{
    var task = Task.Run(() => Request.GetMyData());

    var retData = await task;
    return new JsonResult
    {
        ContentType = "application/json",
        Data = retData,
        JsonRequestBehavior = JsonRequestBehavior.AllowGet,
        MaxJsonLength = int.MaxValue
    };
}

Answer 1

You should return the List<Dictionary<string, object>> instead of string. You don't need to Serialize the data it will be take care by JsonResult

public JsonResult GetValue()
{
    JsonResult json = new JsonResult();
    DataSet ds = GetMyData(); 
   /*LoadDoctordetailsNew is method where i get data from database and convert
      to dataset.It returns a dataset*/
    json.Data = GetJson(ds.Tables[0]);
    json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
    return json;
}

public static List<Dictionary<string, object>> GetJson(DataTable dt)
{
    List<Dictionary<string, object>> rows =
       new List<Dictionary<string, object>>();
    Dictionary<string, object> row = null;

    foreach (DataRow dr in dt.Rows)
    {
        row = new Dictionary<string, object>();
        foreach (DataColumn col in dt.Columns)
        {
            row.Add(col.ColumnName, dr[col]);
        }
        rows.Add(row);
    }
    return rows;
}