About SQL executed by CsvExport

[Pylori]

Hi

I have two questions about the CsvExport method described in the following document.

https://ej2.syncfusion.com/aspnetcore/documentation/grid/excel-export/exporting-grid-in-server?cs-save-lang=1&cs-lang=csharp

1. I want to get the SQL executed by the CsvExport method before the CsvExport method is executed. Is it possible?

2. The application I am currently developing has a large amount of data and the CsvExport method is not very responsive. So I would like to split the data to be retrieved.

Is it possible to add "OFFSET xx ROWS FETCH NEXT xx ROWS ONLY" to the SQL that the CsvExport method executes?

The development environment is as follows

ASP Core .net6.

Syncfusion.EJ2.AspNet.Core 20.4.0.43.

Azure SQL Database

Regards,

Pylori.

[Pavithra Subramaniyam]

Hi Pylori,

For server-side exporting, we need to pass the processed data to the “CsvExport” method. So, you can execute the SQL and get the result as per your requirement and pass it to the “CsvExport” method. Please refer to the below code example for more information.

public IActionResult ExcelExport([FromBody] string gridModel)

        {

            GridExcelExport exp = new GridExcelExport();

            Grid gridProperty = ConvertGridObject(gridModel);

            // here you can add your code and get the Result

            return exp.CsvExport<OrdersDetails>(gridProperty,Result);

        }

If you want more information from the client side along with the gridModel, please let us know what details you need at the server side which will be helpful for us to provide a better solution as early as possible.

[Pylori]

Hi,

I am sorry that my question did not come across well.

A portion of my source code and my question is posted below.

view

server-side

Regards,

Pylori.

[Pavithra Subramaniyam]

Hi Pylori,

Thanks for sharing the details.

In our EJ2 Grid server exporting, we could not pass the SQL query to the data we are exporting inside the CsvExport method. As per our current implementation, We need to load all the data at once only.

[Pylori]

Thank you for your answer.

Now I will try to create SQL based on gridModel(json).

If you have any sample source etc. to serve as an example, please let me know.

[Pavithra Subramaniyam]

Pylori,

In the below code example, we have processed the data source using our default Syncfusion Data Operations before passing it to the “CsvExport” method as an example.
You can also use your own implementation to process the data source using your SQL query.

public ActionResult ExcelExport(string gridModel, string dataModel)

        {

            GridExcelExport exp = new GridExcelExport();

            Grid gridProperty = ConvertGridObject(gridModel);

 

            DataManagerRequest dataManager = (DataManagerRequest)Newtonsoft.Json.JsonConvert.DeserializeObject(dataModel, typeof(DataManagerRequest));

 

            IEnumerable DataSource = OrdersDetails.GetAllRecords();

 

            DataOperations operation = new DataOperations();

 

            if (dataManager.Sorted != null && dataManager.Sorted.Count > 0) //Sorting

            {

              DataSource = operation.PerformSorting(DataSource, dataManager.Sorted);

            }

 

            if (dataManager.Where != null && dataManager.Where.Count > 0) //Filtering

            {

              DataSource = operation.PerformFiltering(DataSource, dataManager.Where, dataManager.Where[0].Operator);

            }

 

            return exp.CsvExport<OrdersDetails>(gridProperty, DataSource);

        }

 

To perform this action, we need the Grid settings in the DataManagerRequest type. So, we have prepared the required settings of the Grid as per the DataManager request format and send it from the client side. If you don’t want to use our Data Operations, then you can skip the below client-side customization.

function toolbarClick(args) {

        var grid = document.getElementById("Grid").ej2_instances[0];

        if (args.item.text == "Excel Export") {

            // preparing the Data model

            var query = grid.getDataModule().generateQuery(true);

            var state = new ej.data.UrlAdaptor().processQuery(new ej.data.DataManager({ url: '' }), query);

            var queries = JSON.parse(state.data);

           

            // preparing the Grid model

            var gridModel = JSON.parse(this.addOnPersist(['allowGrouping', 'allowPaging', 'pageSettings', 'sortSettings', 'allowPdfExport', 'allowExcelExport', 'aggregates',

                'filterSettings', 'groupSettings', 'columns', 'locale', 'searchSettings']));

            var include = ['field', 'headerText', 'type', 'format', 'visible', 'foreignKeyValue', 'foreignKeyField',

                'template', 'index', 'width', 'textAlign', 'headerTextAlign', 'columns'];

            gridModel.filterSettings.columns = queries.where;

            gridModel.searchSettings.fields = queries.search && queries.search[0]['fields'] || [];

            gridModel.sortSettings.columns = queries.sorted;

            gridModel.columns = this.setHeaderText(gridModel.columns, include);

 

            // creating form

            var form = ej.base.createElement('form', { id: 'ExportForm', styles: 'display:none;' });

            var gridInput = ej.base.createElement('input', { id: 'gridInput', attrs: { name: 'gridModel' } });

            gridInput.value = JSON.stringify(gridModel);

            var dataInput = ej.base.createElement('input', { id: 'dataInput', attrs: { name: 'dataModel' } });

            dataInput.value = state.data;

            form.method = 'POST';

            form.action = '/Home/ExcelExport';

            form.appendChild(gridInput);

            form.appendChild(dataInput);

            document.body.appendChild(form);

            form.submit();

            form.remove();

        }

    }