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. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Paging,Filtering,Searching and Sorting problem when using DataGrid with Dapper ORM

Thread ID:

Created:

Updated:

Platform:

Replies:

148174 Oct 9,2019 05:40 AM UTC Oct 10,2019 08:56 AM UTC ASP.NET Core - EJ 2 1
loading
Tags: DataGrid
pradeep
Asked On October 9, 2019 05:40 AM UTC

Dear Team,

            I am working with syncfusion Datagrid and server side dapper ORM. 
  • i have a problem in Paging,Filtering,Searching and Sorting. kindly suggest solution for our problem
  • is there any way to create dynamic query that can be run on serverside which syncfusion internally creates using grid column filters.

View
--------
<ejs-grid id="Grid" dataBound="dataBound" beforePdfExport="beforePdfExport" beforeExcelExport="beforeExcelExport" actionFailure="actionFailure" actionBegin="actionBegin" allowPaging="true" allowSorting="true" allowFiltering="true" allowPdfExport="true" allowExcelExport="true" pdfExportComplete="pdfExportComplete" excelExportComplete="excelExportComplete" contextMenuItems="@(new List<object>() { "AutoFit", "AutoFitAll", "SortAscending", "SortDescending", "Copy", "Edit", "Delete","PdfExport", "ExcelExport", "CsvExport", "FirstPage", "PrevPage","LastPage", "NextPage"})" selectedRowIndex="0" width="auto">

            <e-grid-editSettings allowDeleting="true" allowEditing="true" showDeleteConfirmDialog="true"></e-grid-editSettings>
            <e-data-manager url="@Url.Action("GirdView", "VehicleTariff" )" removeUrl="/VehicleTariff/Delete" adaptor="UrlAdaptor"></e-data-manager>
            <e-grid-filtersettings type="Excel"></e-grid-filtersettings>
            <e-grid-pagesettings pageSize="11" pageSizes="@(new string[] { "11", "15" , "20" , "50" , "100" })"></e-grid-pagesettings>
            <e-grid-sortsettings columns="sortcols"></e-grid-sortsettings>

            <e-grid-columns>
                <e-grid-column field="intVehicleTariffID" isPrimaryKey="true" isIdentity="true" headerText="ID" visible="false" textAlign="Right"></e-grid-column>
                <e-grid-column field="dtVehicleTariffAppliedfrom" headerText="Applied From" customFormat="@(new { type =" date", format="dd-MMM-yyyy" })" type="date"></e-grid-column>

                <e-grid-column field="strVehicleModelName" headerText="Model" type="string"></e-grid-column>
                <e-grid-column field="strCityName" headerText="City" type="string"></e-grid-column>
                <e-grid-column headerText="Action" width="92" commands="commands" textAlign="Center"></e-grid-column>
            </e-grid-columns>
        </ejs-grid>

-----------------
Controller
-----------------
public async Task<IActionResult> GirdView([FromBody]DataManagerRequest dm)
        {
            try
            {
                var _entity = await _IVehicleTariffRepository.GetGridViewAsync();

                DataOperations operation = new DataOperations();
                if (dm.Search != null && dm.Search.Count > 0)
                {
                    _entity = operation.PerformSearching(_entity, dm.Search);
                }
                if (dm.Sorted != null && dm.Sorted.Count > 0)
                {
                    _entity = operation.PerformSorting(_entity, dm.Sorted);
                }
                if (dm.Where != null && dm.Where.Count > 0)
                {
                    _entity = operation.PerformFiltering(_entity, dm.Where, dm.Where[0].Operator);
                }
                int count = _entity.Cast<VehicleTariffView>().Count();
                if (dm.Skip != 0)
                {
                    _entity = operation.PerformSkip(_entity, dm.Skip);
                }
                if (dm.Take != 0)
                {
                    _entity = operation.PerformTake(_entity, dm.Take);
                }
                return dm.RequiresCounts ? Json(new { result = _entity, count = count }) : Json(_entity);

            }
            catch (Exception ex)
            {
                var message = string.Join(" | ", (ex.InnerException == null ? ex.Message : ex.InnerException.Message));
                return new BadRequestObjectResult(message);
            }
        }
------------------------
////////Repository
---------------------
public async Task<IEnumerable<VehicleTariffView>> GetGridViewAsync()
        {
            try
            {
                using (var _sqlConnection = GetOpenConnection())
                {
                    var _strquery = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"
                                  + " SELECT VehicleTariff.intVehicleTariffID, VehicleTariff.dtVehicleTariffAppliedfrom, VehicleModel.strVehicleModelName, City.strCityName FROM VehicleTariff LEFT OUTER JOIN VehicleModel ON VehicleModel.intVehicleModelID = VehicleTariff.intVehicleModelID LEFT OUTER JOIN City ON City.intCityID = VehicleTariff.intCityID";

                    return await _sqlConnection.QueryAsync<VehicleTariffView>(_strquery);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


Thavasianand Sankaranarayanan [Syncfusion]
Replied On October 10, 2019 08:56 AM UTC

Hi Pradeep, 
 
Greetings from Syncfusion support. 
 
Query: I have a problem in Paging,Filtering,Searching and Sorting. kindly suggest solution for our problem 
 
We have validated your query and we suspect that the cause of the issue is the provided dataSource type. Could you please provide dataSource as IEnumerable and check this from your side. Refer the below code snippet for your reference. 
 
[code snippets] 
public IActionResult UrlDatasource([FromBody]DataManagerRequest dm) 
        { 
            IEnumerable DataSource = db.GetAllOrders().ToList(); 
            DataOperations operation = new DataOperations(); 
            if (dm.Search != null && dm.Search.Count > 0) 
            { 
                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search 
            } 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                DataSource = operation.PerformSorting(DataSource, dm.Sorted); 
            } 
            if (dm.Where != null && dm.Where.Count > 0) //Filtering 
            { 
                DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); 
            } 
            int count = DataSource.Cast<OrdersDetails>().Count(); 
            if (dm.Skip != 0) 
            { 
                DataSource = operation.PerformSkip(DataSource, dm.Skip);   //Paging 
            } 
            if (dm.Take != 0) 
            { 
                DataSource = operation.PerformTake(DataSource, dm.Take); 
            } 
            return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); 
        } 
 
Please get back to us if you need further assistance. 
 
Regards, 
Thavasianand S. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon