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;
}
}
