We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

About SQL executed by CsvExport

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.



5 Replies

PS Pavithra Subramaniyam Syncfusion Team January 23, 2023 12:04 PM UTC

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.



PY Pylori replied to Pavithra Subramaniyam January 23, 2023 02:01 PM UTC

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.



PS Pavithra Subramaniyam Syncfusion Team January 24, 2023 10:55 AM UTC

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.



PY Pylori January 25, 2023 03:24 PM UTC

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.



PS Pavithra Subramaniyam Syncfusion Team January 27, 2023 04:54 PM UTC

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();

        }

    }

 




Loader.
Live Chat Icon For mobile
Up arrow icon