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

I can't distinguish between the result you are getting and your intended results, they're the same, no? — JohanP, Jan 23 '19 at 03:56
no, they are not. the intended result is hardcoded in the Angular typescript file and as given the the exporting an excel file example. I am returning the same results in form of dataset using ex: 'Binding Json data to a table in mvc 4'. The intended result successfully creates excel file but not the actual result — SilverFish, Jan 23 '19 at 04:11
Looks like you're sending back a string rather than the object. Are you parsing it into JSON yourself? .NET will do that for you, just return the array as is. — Eraph, Jan 23 '19 at 04:18

score 1 · Accepted Answer · answered Jan 23 '19 at 14:00

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

Thanks so much! You made my day – SilverFish Jan 23 '19 at 14:31 — SilverFish, Jan 23 '19 at 14:31

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

1 Answers1

Linked