How to load datareader data then return json for ajax datatable

Question

This code for load data member

public ActionResult LoadData()
{
    string query = "SELECT NAME, DATELASTUSE, CREATE_ID FROM MEMBER";

    using (OdbcConnection c = new OdbcConnection(Connection.Conn()))
    {
        OdbcCommand cmd = new OdbcCommand(query, c);
        c.Open();

        OdbcDataReader dr = cmd.ExecuteReader();

        var dataTable = new DataTable();
        dataTable.Load(dr);

        return **json for ajax** ;
    }
}


$(document).ready(function () {
    var oTable = $("#PassTable").DataTable({            
    "ajax": {
            "url": "/Home/LoadData",
            "type": "GET",
            "datatype": "json"
            },
            "columns": [
                { "data": "NAME", "autowidth": true },
                { "data": "DATELASTUSE", "autowidth": true },
                { "data": "CREATED_ID", "autowidth": true },
            ],
            "language": {
                "emptytable": "No data found, Please click on <b>Add New</b> button"
            }
        });
    });

<table id="PassTable" class="table table-border table-hover" style="background-color:white;" width="100%">
    <thead>
        <tr>               
            <th>Name</th>
            <th>Date</th>
            <th>Created_By</th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

LoadData is From HomeController

I don't know how to return datareader to json.

I don't understand the flow just try in error use -> return Json(new { data = dataTable }, JsonRequestBehavior.AllowGet);

but Gv me error.

Please Help.

Thx.

score 0 · Answer 1 · answered Feb 28 '20 at 07:41

Your action method should end up like

        public ActionResult LoadData()
        {
            string query = "SELECT LastName,FirstName,PhoneNumber,Skype,Email,City FROM Contacts;;

            using (OdbcConnection c = new OdbcConnection(Connection.Conn()))
            {
                OdbcCommand cmd = new OdbcCommand(query, c);
                c.Open();

                OdbcDataReader dr = cmd.ExecuteReader();

                var dataTable = new DataTable();
                dataTable.Load(dr);

                var lst = dataTable.AsEnumerable()
                        .Select(r => r.Table.Columns.Cast<DataColumn>()
                                .Select(c => new KeyValuePair<string, object>(c.ColumnName, r[c.Ordinal])
                               ).ToDictionary(z => z.Key, z => z.Value)
                        ).ToList();
                return Json(lst, JsonRequestBehavior.AllowGet);
            }
        }

I took this solution from https://stackoverflow.com/a/30899867/3816470.

I don't know more about Ajax Datable so I'll not for any help on this part.

How to load datareader data then return json for ajax datatable

1 Answers1