dynamic datasource asp.net mvc
dynamic datasource asp.net mvc
montoyam
Posts: 568Questions: 136Answers: 5
I am trying to build a datatable based off a user defined function. I see a post where code is given on how to grab the column names dynamically: https://datatables.net/forums/discussion/comment/142403#Comment_142403
However, I am having trouble getting the data into the correct format. If you look at my commented out lines, there are several techniques I have tried (and you can probably tell by what I tried that I am very new to this 
)
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Net.Http.Formatting;
using System.Web;
using System.Web.Http;
using DataTables;
using Billing.Models;
using Newtonsoft.Json;
namespace Billing.Controllers
{
public class ToGenerateController : ApiController
{
[Route("api/ToGenerate")]
[HttpGet]
[HttpPost]
public IHttpActionResult toGenerate()
{
var request = HttpContext.Current.Request;
var settings = Properties.Settings.Default;
var AsOfCookie = request.Cookies["AsOfDate"].Value;
string query = "select * from udf_FundOrgFTE_AdjustedFTEPercentages ('" + AsOfCookie + "')";
string connectionString = settings.DbConnection;
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = command.ExecuteReader();
var dt = new System.Data.DataTable();
dt.Load(reader);
object[] result = new object[dt.Rows.Count + 1];
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
result[i] = dt.Rows[i].ItemArray;
}
reader.Close();
return Json( result);
//string strArray = "{ 'draw':null,'data':[" + result.ToString() + "]}" ;
//return Json( strArray );
//var json = JsonConvert.SerializeObject(result);
//return Json(json);
//return Json("{ 'draw':null,'data':[" + result + "]}");
}
}
}
}
This discussion has been closed.
Answers
I was almost there but I wasn't getting any property, like '{data: [' so I just tried appending it manually...yeah, im guesssing that is a rookie thing to do, mixing up objects and strings.
I am lost.
Hello, I'm doing an asp project with linq
I have this in my controller
HTML:
And this in JS
this works for me
in the columns option, you must place the keys name from the JSON object
does dataTables have a helper or function that will add the property and field names to a dataset (not a specific table) or array to make it DataTables friendly? I was hoping to not have to hard code the field names as this project evolves every week and the data they want to return changes so I want to use the code referenced in the link of my original post.
There are a few threads that discuss dynamically creating the table, such as this one here, that should get you going.
Colin
yes, that is the same thread I had a link to in my original post
My issue was that I was not able to get the json in the correct format. But, I have come upon this code that finally seems to be presenting the data correctly.
I am not quite there, getting closer, but I will close this question and start a new one if needed.
and here is the js
Aha, my mistake, apologies!