Syncfusion OData DataManager class To SQL

[Cenk]

Hi

 

We want to use DataManager class to get requests on ServerSide.

We are not using EntityFramework and we want to generate SQL code from Datamanager filters.

Is there a tool or sample code that converts DataManager object to sql?

 

Thanks and best regards.

Cenk

[Mohammed Farook J]

Hi Cenk, 

 

Thanks for contacting Syncfusion support. 

 

The DataManager class is not available in Essential JS 2. So before start working in your requirement, could you please confirm the project you are using (Essential JS 1 or Essential JS 2) in your application? 

 

Please find the Essential JS1 and Essential JS 2 demo link for your reference. 

 

Essential JS 1 :  https://aspnetcore.syncfusion.com/grid/default   

 

Essential JS 2: https://aspdotnetcore.syncfusion.com/Grid/DefaultFunctionalities#/material  

 

 

Regards, 

J Mohammed Farook 

[Cenk]

Hi Mohammed

We plan to use:

Essential JS 2: https://aspdotnetcore.syncfusion.com/Grid/DefaultFunctionalities#/material 

Thanks and best regards.

Cenk

[Mohammed Farook J]

Hi Cenk,  

 

Thanks for your confirmation. 

 

We can construct SQL query from the DataManager server parameters. Please find the code example. 

 

In Currently, Essential JS2 DataManager class is not available directly. So we need to declare class to hold the server parameters. Please find the code example  

 

 

public class Data 

        { 

 

            public bool requiresCounts { get; set; } // 

            public int skip { get; set; } // handle page skip  

            public int take { get; set; }  // handler page take value 

 

            public List<Wheres> where { get; set; } // handle filter query 

        } 

 

 

 

To declare Where class of DataManager 

 

 

public class Wheres 

{ 

    public List<Predicates> predicates { get; set; } 

    public string field { get; set; } 

    public bool ignoreCase { get; set; } 

 

    public bool isComplex { get; set; } 

 

    public string value { get; set; } 

    public string Operator { get; set; } 

 

} 

public class Predicates 

{ 

 

    public string value { get; set; } 

    public string field { get; set; } 

 

    public bool isComplex { get; set; } 

 

    public bool ignoreCase { get; set; } 

    public string Operator { get; set; } 

 

} 

 

 

 

 

To render Grid component and handle DataManager query to SQL query 

 

[index.cshtml] 

 

<ejs-grid id="Grid" allowPaging="true" allowFiltering="true" allowSorting="true"> 

    <e-data-manager url="/Home/UrlDatasource" adaptor="UrlAdaptor"></e-data-manager> 

    <e-grid-columns> 

        <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="120"></e-grid-column> 

        <e-grid-column field="CustomerID" headerText="Customer ID" width="150"></e-grid-column> 

        <e-grid-column field="EmployeeID" headerText="Employee ID" textAlign="Right" width="120"></e-grid-column> 

        <e-grid-column field="ShipCity" headerText="Ship City" width="150"></e-grid-column> 

    </e-grid-columns> 

</ejs-grid> 

 

[Home controller] 

 

public IActionResult UrlDatasource([FromBody]Data dm) 

        { 

            var Data=  select OrderID, EmployeeID, CustomerID, ShipCity * from Orders  

             var count = Data.ToList().Count(); 

            if (dm.where != null && dm.where.Count > 0 && dm.where[0].predicates != null) 

            { 

 

                for (var i = 0; i < dm.where[0].predicates.Count; i++) 

                { 

 

                    switch (dm.where[0].predicates[i].field) 

                    { 

                         

 

                        //Codes to Perform Filtering 

                        case "OrderID": 

                            // Data=  SELECT * FROM Orders  Where OrderID=dm.@where[0].predicates[i].value 

                            break; 

                        case "CustomerID": 

                             

                            // Data = SELECT * FROM Orders  Where CustomerID like '+dm.@where[0].predicates[i].value+ %' 

                            break; 

                        case "EmployeeID": 

                             

                            break; 

                        //  Data = SELECT * FROM Orders  Where EmployeeID=dm.@where[0].predicates[i].value 

                        case "ShipCity": 

                            

                            break; 

 

                            // Data SELECT * FROM Orders  Where Shipcity like '+dm.@where[0].predicates[i].value+ %' 

                    } 

 

 

                } 

            } 

            return dm.requiresCounts ? Json(new { result = Data.ToList().Skip(dm.skip).Take(dm.take), count = count }) : Json(Data); 

        } 

 

 

Please get back to us if you need further assistance.  

 

Regards, 

J Mohammed Farook