grid URLadabter datasource with json

[ADMIRAL PH]

Hello, how can I get the correct JSON syntax data type as per the following example?

--- controller

    public class LocalDataController : Controller

    {

        public IActionResult Index()

        {

            CRUDModeloc model = new CRUDModeloc();

            DataTable dt = model.GetAllStudents();

            ///return View("Index", dt);

            return json ( ????????????????????????????????) new dt     count = ???

        }

    }

--- Model

        public DataTable GetAllStudents()

        {

            DataTable dt = new DataTable();

            string strConString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\MVC\MVC .NET GRID 2025\ADO.NET\ADO Normal Grid - Grid based Model\ADO\LocalData\NORTHWND.MDF;Integrated Security=True";

            using (SqlConnection con = new SqlConnection(strConString))

            {

                con.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.Orders ORDER BY OrderID", con);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(dt);

            }

            return dt;

        }

--- View 

<ejs-grid id="Grid" rowHeight="20" height="200" allowPaging="true" allowFiltering="true" allowSorting="true"

<e-data-manager url="Home/Index" insertUrl="/Home/Insert" updateUrl="/Home/Update" removeUrl="/Home/Remove" adaptor="UrlAdaptor"></e-data-manager>

[Sivaranjani Rajasekaran]

Hi ADMIRAL PH, 

Greetings from Syncfusion support!

When using the UrlAdaptor in the Grid, the server is expected to return different JSON response formats depending on whether the requiresCounts parameter is present in the query request:

• For general Grid actions (such as initial load, paging, sorting, or refreshing the data source):

• The requiresCounts parameter will be true.

• In this case, the server must return a response in the format { result: [...], count: totalCount }.

• For search or filtering within the filter popup (Excel or menu filter search):

• The requiresCounts parameter will be undefined.

• Here, the server should return a plain array of objects — only the filtered data without wrapping it inside a { result: [...], count: ... } object.

You can modify your controller to return the correct JSON format based on the request. Here's how you can do it using your DataTable-based implementation:

Please refer to the code below for more information:

Code Example : 

 

[ Model ]

[OrderDetails.cs]

 public OrdersDetails(

        int OrderID, string CustomerId, int EmployeeId, string ShipCountry)

        {

            this.OrderID = OrderID;

            this.CustomerID = CustomerId;

            this.EmployeeID = EmployeeId;

            this.ShipCountry = ShipCountry;

        }

        public static List<OrdersDetails> GetAllRecords()

        {

            if (order.Count() == 0)

            {

                int code = 10000;

                for (int i = 1; i < 10; i++)

                {

                    order.Add(new OrdersDetails(code + 1, "ALFKI", i + 0, "Denmark"));

                    order.Add(new OrdersDetails(code + 2, "ANATR", i + 2, "Brazil"));

                      .        .           .

                    code += 5;

                }

            }

            return order;

        }

Controller: 

[OrderController.cs]

   public object Post([FromBody] DataManagerRequest DataManagerRequest)

        {

            // Retrieve data from the data source (e.g., database)

            IQueryable<OrdersDetails> DataSource = GetOrderData().AsQueryable();

 

            QueryableOperation queryableOperation = new QueryableOperation(); // Initialize DataOperations instance

 

            // Handling filtering operation

            if (DataManagerRequest.Where != null && DataManagerRequest.Where.Count > 0)

            {

                DataSource = 

                                    queryableOperation.PerformFiltering(DataSource.Cast<OrdersDetails>(), 

                                    DataManagerRequest.Where, 

                                    DataManagerRequest.Where[0].Operator);

            }

 

            // Get the total count of records

            int totalRecordsCount = DataSource.Count();

 

            // Handling paging operation.

            if (DataManagerRequest.Skip != 0)

            {

                DataSource = queryableOperation.PerformSkip(DataSource, DataManagerRequest.Skip);

            }

            if (DataManagerRequest.Take != 0)

            {

                DataSource = queryableOperation.PerformTake(DataSource, DataManagerRequest.Take);

            }

 

            // Return data based on the request

            return DataManagerRequest.RequiresCounts ? 

                             new { result = DataSource, count = totalRecordsCount }: 

                             new { result = DataSource };

        }

 

Screenshot:

 

Sample : Please find the sample attached.

 

Please get back to us if you need further assistance.

 

Regards,

Sivaranjani R.

Attachment: GridCore_UrlAdaptor_ddeee7ef.zip

[ADMIRAL PH]

Dear Sivaranjani R. , thanks so much for the sample, can we apply it on the following Model (converting datatable to json result)

        public DataTable GetAllStudents()

        {

            DataTable dt = new DataTable();

            string strConString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\MVC\MVC .NET GRID 2025\ADO.NET\ADO Normal Grid - Grid based Model\ADO\LocalData\NORTHWND.MDF;Integrated Security=True";

            using (SqlConnection con = new SqlConnection(strConString))

            {

                con.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.Orders ORDER BY OrderID", con);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(dt);

            }

            return dt;

        }

[Sivaranjani Rajasekaran]

Hi ADMIRAL PH,

Thank you for your inquiry!

To achieve your requirement, you need to convert the DataTable into a list of objects. After that, you can apply filtering, sorting, and paging operations either manually or by using Syncfusion’s helper classes like DataOperations or QueryableOperation. Finally, return the processed data and the total record count as a JSON object.

Please refer to the code below for more information:

Code Example : 

public class LocalDataController : Controller

{

    public IActionResult Index([FromBody] DataManagerRequest dm)

    {

        // Get data from DataTable

        DataTable dt = GetAllStudents();

        // Convert DataTable to List<Dictionary<string, object>>

        var records = new List<Dictionary<string, object>>();

        foreach (DataRow row in dt.Rows)

        {

            var dict = new Dictionary<string, object>();

            foreach (DataColumn col in dt.Columns)

            {

                dict[col.ColumnName] = row[col];

            }

            records.Add(dict);

        }

        // Convert to IQueryable for processing

        IQueryable<Dictionary<string, object>> data = records.AsQueryable();

        // Apply filtering, sorting, paging using DataOperations

        DataOperations operation = new DataOperations();

      

        if (dm.Skip != 0)

            data = operation.PerformSkip(data, dm.Skip);

        if (dm.Take != 0)

            data = operation.PerformTake(data, dm.Take);

        return Json(dm.RequiresCounts ? new { result = data, count = count } : new { result = data });

    }

    public DataTable GetAllStudents()

    {

        DataTable dt = new DataTable();

        string strConString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\MVC\MVC .NET GRID 2025\ADO.NET\ADO Normal Grid - Grid based Model\ADO\LocalData\NORTHWND.MDF;Integrated Security=True";

        using (SqlConnection con = new SqlConnection(strConString))

        {

            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.Orders ORDER BY OrderID", con);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(dt);

        }

        return dt;

    }

}

For more information, please refer to the following public forum and documentation links:

Related Forum : https://support.syncfusion.com/kb/article/11728/how-to-bind-sql-server-data-in-asp-net-core-datagrid-using-sqlclient-data-provider

Documentation Link : https://ej2.syncfusion.com/aspnetcore/documentation/common/data-binding/bind-entity-framework#creating-dbcontext-and-model-class

Please feel free to reach out if you need any further assistance.

Regards,

Sivaranjani R.

[ADMIRAL PH]

thanks so much Mr. Sivaranjani R. , I really appreciate your kind reply and cooperation.

[Sivaranjani Rajasekaran]

ADMIRAL PH,

 

You're most welcome! We're glad we could assist you, and we truly appreciate your kind words.

 

If you have any further assistance please get back to us!