dynamic datasource asp.net mvc

dynamic datasource asp.net mvc

montoyammontoyam 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 + "]}");
            }
        }

    }
}

Answers

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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.

  • danflalvdanflalv Posts: 3Questions: 0Answers: 0
    edited February 2020

    Hello, I'm doing an asp project with linq

    I have this in my controller

    HTML:

    And this in JS

         function getData() {
                    var xhm = new XMLHttpRequest();
                    xhm.open('GET', '/Equipo/GetData', true)
                    xhm.addEventListener('load', () => {
                        var table = $('#tblEQuipo').DataTable({
                            dom: 'Bfrtip',
                            data: JSON.parse(xhm.responseText),
                            columns: [
                                { data: "ID" },
                                { data: "Marca" },
                                { data: "Modelo" },
                                { data: "Serie" },
                                { data: "SO" },
                                { data: "Descripcion" },
                                { data: "Clase" },
                                { data: "Tipo" },
                                { data: "IP" },
                                { data: "IPWiFi" },
                                { data: "MAC" },
                                { data: "MACWiFi" },
                                { data: "Estado" },
                                {
                                    data: "FechaCreacion",
                                    render: (data, type, row) => {
                                        let dateInt = parseInt(data.replace(/(\/)*(\Date)*(\/)*(\))*(\()*/g, ''));
                                        let dateCV = new Date(dateInt);
                                        return dateCV.toLocaleDateString();
                                    }
    
                                },
                                { data: "Usuario" },
                                {
                                    "className": 'details-control',
                                    "orderable": false,
                                    "data": "urlEdit",
                                    "defaultContent": '',
                                    "render": (data, type, row) => { return `<a class="btn btn-outline-primary" href="${data}"><span><i class="fas fa-edit"></i></span></a>`; }
                                },
                                {
                                    "className": 'details-control',
                                    "orderable": false,
                                    "data": "urlDetails",
                                    "defaultContent": '',
                                    "render": (data, type, row) => { return `<a class="btn btn-outline-info" href="${data}"><span><i class="fas fa-info-circle"></i></span></a>`; }
                                },
                                {
                                    "className": 'details-control',
                                    "orderable": false,
                                    "data": "urlCreate",
                                    "defaultContent": '',
                                    "render": (data, type, row) => { return `<a class="btn btn-outline-warning" href="${data}"><span><i class="fas fa-clone"></i></span></a>`; }
                                }
                            ],
                            deferRender: true,
                            pageLength: 50,
                            paging: true,
                            orderMulti: true,
                        });
                    });
                    xhm.send();
                }
    
                $(document).ready(function () {
                    getData();
                });
    

    this works for me

    in the columns option, you must place the keys name from the JSON object

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited February 2020

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    There are a few threads that discuss dynamically creating the table, such as this one here, that should get you going.

    Colin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    yes, that is the same thread I had a link to in my original post :smile:

    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.

    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;
                System.Data.DataSet ds = new System.Data.DataSet();
    
    
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
    
    
                    using (SqlCommand command = new SqlCommand(query, conn))
                    {
                        command.Connection = conn;
                        using (SqlDataAdapter sda = new SqlDataAdapter(command))
                        {
                            sda.Fill(ds);
                        }
                    }
                }
                return Json(ds);
    
            }
    
        }
    }
    

    I am not quite there, getting closer, but I will close this question and start a new one if needed.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    and here is the js

                $.ajax({
                    url: "api/AdjustedRatios",
                    success: function (data) {
                        var columns = []; 
                        //build the DataTable dynamically.
                        // json return:  {"Table":[{"ImportID":121,"DeptName":"Ag Commissioner","FTE":48.15,"EmployeeCount":50}...
                        columnNames = Object.keys(data.Table[0]); //.Table[0]] refers to the propery name of the returned json
                        for (var i in columnNames) {
                            columns.push({
                                data: columnNames[i],
                                title: columnNames[i]
                            });
                        }
                        
                        $('#AdjustedRatios').DataTable({
                            dom: 'frtip',
                            data: data.Table,
                            rowId: 'ImportID',
                            scrollX: true,
                            columns: columns
                        })
                    }
    
                });
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Aha, my mistake, apologies!

This discussion has been closed.