How to convert grid filter to sql string ?

[lorryl]

Hi,

I am using grid paging binding DataManger by URL Adaptor, and get data is well.

But I found a queston: I am using sql server stored procedure to get data, when remote filtering on grid, the filter format is like "Numeric1 eq 123", I need to convert this type filter to sql string, like "tblInvoice.Numeric1 = 123". Is there a function to convert the data manger filter to sql filter ? 

[Hariharan J V]

Hi Lorryl, 

Thanks for contacting Syncfusion support. 

Yes we can to covert DataManager request query based on your requirement . Please find the code example and screenshot for your reference. 

Code example: 

[Home.controller] 

public IActionResult GetData([FromBody]DataClass dm) 

        { 

 

              // filter query iterating  

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

            { 

                var optr = dm.Where[0].predicates[0].Operator; 

                // converts based on your needs 

                var optrSymbol = ""; 

                switch (optr) 

                { 

                    case "equal" : 

                    optrSymbol = "="; 

                    break; 

                    case "lessthan": 

                     optrSymbol = "<"; 

                       break; 

                    case "greaterthan": 

                        optrSymbol = ">"; 

                            break; 

                    . . . 

                    default: 

                        break; 

                } 

                var sqlString = dm.Table + '.' + dm.Where[0].predicates[0].Field + optrSymbol + dm.Where[0].predicates[0].value; 

            } 

             

          // execute your data operation  

 

            return Json(new { result = Data, count = Data.Count() }); // while using UrlAdaptor we need to return result and count object into grid 

Screenshot: 

 

Regards, 

Hariharan 

[lorryl]

Thanks for your reply. In my controller, my function is for service API, and the parameters cannot be the dm. Do you have a sollusion to convert to sql filter in typescript ?

[Hariharan J V]

Hi Lorryl, 

Thanks for your update. 

Query :Do you have a sollusion to convert to sql filter in typescript ? 

 

We have analyzed your requirement and you can form the SQL query by using the below way. In the below sample we have get the filter operator and value by using the filterSettings property based on that information we have formed SQL query in button click event.  

Kindly refer to the below code example and sample for more information. 

@Component({ 

    selector: 'app-container', 

    template: 

        ` 

    <button ejs-button id='load'cssClass="e-primary" (click)='sqlquery()'> Query </button> 

        <ejs-grid #grid [dataSource]='data' [filterSettings]='filterOptions' allowFiltering='true' height='272px' > 

               <e-columns> 

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

                    <e-column field='EmployeeID' headerText='Employee ID' width='150'></e-column>               </e-columns> 

                </ejs-grid>`, 

    styleUrls: ['index.css'], 

}) 

export class AppComponent implements OnInit { 

 

    public data: Object[]; 

    public customColumns: Object[]; 

    public toolbarOptions: ToolbarItems[]; 

    @ViewChild('grid') 

    public grid: GridComponent; 

 

    ngOnInit(): void { 

        this.data = data; 

        this.filterOptions = { 

            columns: [{ field: 'EmployeeID', matchCase: false, operator: 'equal', predicate: 'and', value: '1' }, 

            ] 

        }; 

    } 

 

    sqlquery() { // you can your service link here with the generated string 

        var filterColumns = this.grid.filterSettings.columns; 

        for (let i = 0; i < filterColumns.length; i++) { 

            var optr = filterColumns[i].operator; 

            var optrSymbol = ""; 

            var table = 'tblInvoice'; 

            switch (optr) { 

                case "equal": 

                    optrSymbol = "="; 

                    break; 

                    . . . . 

            } 

            var sqlString = table + '.' + filterColumns[i].field + optrSymbol + filterColumns[i].value; 

        } 

    } 

} 

 

Sample : https://next.plnkr.co/edit/sASXQzoS0JhpRneS  

Help documentation :  

https://ej2.syncfusion.com/angular/documentation/grid/filtering.html?lang=typescript  

https://ej2.syncfusion.com/angular/documentation/grid/api-gridComponent.html#filtersettings  

 

If we misunderstood your requirement please get back to us with more information so that we can analyze based on your scenario and provide you better solution quickly?.  

Regards, 

Hariharan 

[lorryl]

That's exactly what I asked for. Thank you !

Maybe it would be a better method by using npm install odata-v4-sql, I will try it, thank you for you response.

[Hariharan J V]

Hi Lorryl, 

 

Thanks for your update. 

 

We are happy to hear that your issue has been resolved. 

 

Regards, 

Hariharan