Questions on UI for ASP.NET MVC Forum

5 answers

3.5K+ views

Converting DataSourceRequest filters to SqlServer parameterized query in controller Read method

For the use case of a remote data source delivering json to a grid one would typically use a simple combination of an entity framework object using extension method ToDataSourceResult. For example:

using Kendo.Mvc.Extensions;

namespace MyProject.Controllers

{

public partial class MyDataEditorController : Controller

{

public ActionResult MyData_Read([DataSourceRequest]DataSourceRequest request)

{

DataSourceResult result = db.MyData.ToDataSourceResult(request, myData => new {

MyDataID = myData.AnalysisID,

...

});

return Json(result);

}

ToDataSourceResult() takes care of inspecting the request and applying the supplied criteria for filtering, sorting, grouping, paging, etc... to create an EF query in the connected database.

What happens if the database object being queried does not have an EF model ?

The above pattern of coding can not be used.

The lack of an EF model could be by design oversite, or the object is dynamic in nature. A dynamically constructed pivot view can not be compile time modeled for EF. The following method shows how the DataSourceRequest filter data is used to construct a parameterized where clause for a query in SqlServer. Slight modifications may be needed for other target data bases:

private string DescriptorToSqlServerQuery (FilterDescriptor fd, SqlCommand command)

{

string parameterName = "@PARAMETER" + command.Parameters.Count;

string result;

// Some string filter values are modified for use as parameteres in a SQL LIKE clause, thus work with a copy.

// The original value must remain unchanged for when ToDataSourceResult(request) is used later.

Object filterValue = fd.Value;

switch (fd.Operator)

{

case FilterOperator.IsLessThan: result = "[" + fd.Member + "]" + " < " + parameterName; break;

case FilterOperator.IsLessThanOrEqualTo: result = "[" + fd.Member + "]" + " <= " + parameterName; break;

case FilterOperator.IsEqualTo: result = "[" + fd.Member + "]" + " = " + parameterName; break;

case FilterOperator.IsNotEqualTo: result = "[" + fd.Member + "]" + " <> " + parameterName; break;

case FilterOperator.IsGreaterThanOrEqualTo: result = "[" + fd.Member + "]" + " >= " + parameterName; break;

case FilterOperator.IsGreaterThan: result = "[" + fd.Member + "]" + " > " + parameterName; break;

case FilterOperator.StartsWith:

filterValue = fd.Value.ToString().ToSqlSafeLikeData() + "%";

result = "[" + fd.Member + "]" + " like " + parameterName; break;

case FilterOperator.EndsWith:

filterValue = "%" + fd.Value.ToString().ToSqlSafeLikeData();

result = "[" + fd.Member + "]" + " like " + parameterName; break;

case FilterOperator.Contains:

filterValue = "%" + fd.Value.ToString().ToSqlSafeLikeData() + "%";

result= "[" + fd.Member + "]" + " like " + parameterName; break;

case FilterOperator.IsContainedIn:

throw new Exception("There is no translator for [" + fd.Member + "]" + " " + fd.Operator + " " + fd.Value);

case FilterOperator.DoesNotContain:

filterValue = "%" + fd.Value.ToString().ToSqlSafeLikeData();

result = "[" + fd.Member + "]" + " not like " + parameterName; break;

case FilterOperator.IsNull: result = "[" + fd.Member + "]" + " IS NULL"; break;

case FilterOperator.IsNotNull: result = "[" + fd.Member + "]" + " IS NOT NULL"; break;

case FilterOperator.IsEmpty: result = "[" + fd.Member + "]" + " = ''"; break;

case FilterOperator.IsNotEmpty: result = "[" + fd.Member + "]" + " <> ''"; break;

default:

throw new Exception("There is no translator for [" + fd.Member + "]" + " " + fd.Operator + " " + fd.Value);

}

command.Parameters.Add(new SqlParameter(parameterName, filterValue));

return result;

}

is called from this method

private string

FiltersToParameterizedQuery(IList<IFilterDescriptor> filters, FilterCompositionLogicalOperator compositionOperator = FilterCompositionLogicalOperator.And, SqlCommand command =

null)

{

// See https://www.telerik.com/forums/how-to-access-datasourcerequest-filters-in-controller-

if (!filters.Any()) return "";

string result = "(";

string combineWith = "";

foreach (var filter in filters)

{

if (filter is FilterDescriptor fd)

{

result +=

combineWith + "("

+ DescriptorToSqlServerQuery(fd, command)

+ ")"

;

}

else if (filter is CompositeFilterDescriptor cfd)

{

result +=

combineWith + "("

+ FiltersToParameterizedQuery(cfd.FilterDescriptors, cfd.LogicalOperator, command)

+ ")"

;

}

combineWith =

(compositionOperator == FilterCompositionLogicalOperator.And)

? " and "

: " or "

;

}

result += ")";

return result;

}

Which is utilized by this read method that applies the data of the DataSourceRequest in the making of a 'manual' query

public ActionResult MyData_Read(DataSourceRequest request)

{

// KendoUI Grid, Custom binding - https://docs.telerik.com/aspnet-mvc/helpers/grid/binding/custom-binding

// db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

SqlCommand command = new SqlCommand();

string whereClause = FiltersToParameterizedQuery(request.Filters, command: command);

string orderClause = "";

string pageClause =

" OFFSET " + (request.Page-1)*request.PageSize + " ROWS"

+ " FETCH NEXT " + request.PageSize + " ROWS ONLY"

;

string apv1stColName = chemistryColumns[0].ColumnName;

List<string> orderedMembers = new List<string>();

string comma = "";

if (request.Groups != null)

{

foreach (var group in request.Groups)

{

if (orderedMembers.Contains(group.Member)) continue;

orderClause += comma

+ group.Member

+ ((group.SortDirection == System.ComponentModel.ListSortDirection.Ascending) ? " ASC" : " DESC");

comma = ",";

orderedMembers.Add(group.Member);

}

if (request.Sorts != null)

{

foreach (var sort in request.Sorts)

{

if (orderedMembers.Contains(sort.Member)) continue;

orderClause += comma

+ sort.Member

+ ((sort.SortDirection == System.ComponentModel.ListSortDirection.Ascending) ? " ASC" : " DESC");

comma = ",";

orderedMembers.Add(sort.Member);

}

if (!orderedMembers.Contains(apv1stColName))

{

orderClause += comma + apv1stColName + " ASC";

}

if (whereClause.Length > 0) whereClause = " WHERE " + whereClause;

if (orderClause.Length > 0) orderClause = " ORDER BY " + orderClause;

// --- connect and execute page query and count all rows per filter

string pageQuery = "select * from MY_UNMODELED_DATABASE_OBJECT" + " " + whereClause + orderClause + pageClause;

string countQuery = "select count(1) from MY_UNMODELED_DATABASE_OBJECT" + " " + whereClause;

var dataTable = new System.Data.DataTable();

int total = 0;

using (var connection = new SqlConnection(db.Database.Connection.ConnectionString))

{

command.Connection = connection;

command.CommandText = pageQuery;

using (var dataAdapter = new SqlDataAdapter(command))

{

dataAdapter.FillSchema(dataTable, System.Data.SchemaType.Mapped);

dataAdapter.Fill(dataTable);

}

command.CommandText = countQuery;

total = (int) command.ExecuteScalar();

}

// --- a little fakery to request.Page to make things work in ToDataSourceResult()

request.Page = 1;

DataSourceResult result = dataTable.ToDataSourceResult(request);

result.Total = total;

return Json(result);

}

Enjoy and happy coding.

Grid

answered on 28 Mar 2021

1 answer

3.3K+ views

Custom Group header for Grouping column

Hi Team,

I am working on Kendo grid grouping , my requirement is need to change the group header name instead of column name .

Example :

My grid contains following columns

name , city , state , language

we need to group by language column and is set to visible false in grid.

Tried with

1. GroupHeaderTemplate

2. ClientGroupHeaderTemplate(@<text>Language (local language) : </text>)

Thanks & Regards,

Sp

Grid

answered on 26 Mar 2021

1 answer

701 views

Nested Grid in columns bound dataitem

Hi,

i use nested grid. The inner grid too (grid name: YemekTipGrid_#=Id#) I want to create a dynamic button

columns.Bound(b => b.Id).Width(205).Title(Localizer["View_Common_Operation"]).ClientTemplate("#=operationButtonsInner(data)#");

this works fine for the grid above.Seems to work for the inner grid but the data of the above grid is coming

shown in telerikforum_3.jpg. data-id = 2 seen here is the first address of the upper grid. However, the expected value is 7 instead of 2 How can I get the PK IDs of the grid object at the bottom

@(Html.Kendo().Grid<ListYemekTipDto>()
.Name("YemekTipGrid")
.Columns(columns =>
{
//columns.Bound(b => b.Id).Title("").ClientHeaderTemplate("<input type='checkbox' id='ck-check-all' />").ClientTemplate(("<input type='checkbox' selector=#=ID# class='ck-secim' ID='#=ID#' name='Sec' />")).Filterable(false);
columns.Bound(b => b.Tip).Title(Localizer["View_YemekTip_Model_Tip"]);
columns.Bound(b => b.Ucret).Title(Localizer["View_YemekTip_Model_Ucret"]);
columns.Bound(b => b.ParentYemekTipId).Title(Localizer["View_YemekTip_Model_ParentId"]);
columns.Bound(b => b.Not).Title(Localizer["View_YemekTip_Model_Not"]);
columns.Bound(b => b.Aciklama).Title(Localizer["View_YemekTip_Model_Aciklama"]);

columns.Bound(b => b.KayitDurumu).Title(Localizer["View_Common_KayitDurumu"]).Width(50).ClientTemplate("<span id='badge_#=Id#' class='badgeTemplate'></span>");
columns.Bound(b => b.Id).Width(205).Title(Localizer["View_Common_Operation"]).ClientTemplate("#=operationButtons(data)#");
})
.ToolBar(toolbar =>
{
toolbar.Search().Text(Localizer["Grid_Common_Search"]);
//toolbar.Excel().Text(Localizer["Grid_Common_Excel"]);
//toolbar.Pdf().Text(Localizer["Grid_Common_Pdf"]);
//toolbar.Custom().Text(Localizer["Grid_Common_NewRecord"]).IconClass("fa fa-plus").HtmlAttributes(new { @class = "modal-action", data_type = "get", data_url = Url.Action("Ekle", "YemekTip", new { area = "Yonetim" }), data_grid = "#YemekTipGrid", data_title = Localizer["View_Common_CreateOperation"], data_buttons = "true", data_savetext = Localizer["View_Common_Create"] });
})
.Pageable()
.Sortable()
.Filterable()
.Navigatable()
.ColumnMenu()
.ClientDetailTemplateId("templateInner")
.HtmlAttributes(new { style = "height:430px;" })
.Resizable(r => r.Columns(true))
.Reorderable(r => r.Columns(true))
.Groupable(g => g.ShowFooter(false))
//.Events(events => events.DataBound("onDataBound"))
.DataSource(dataSource => dataSource
.Ajax()
.Batch(true)
.PageSize(15)
.AutoSync(true)
.ServerOperation(false)
.Events(events => events.Error("error_handler"))
.Read(read => read.Action("Grid", "YemekTip", new { area = "Yonetim" }).Type(HttpVerbs.Post))
).Events(events => events.DataBound("dataBound"))
)

Grid

answered on 26 Mar 2021

1 answer

480 views

GridView in ASP.NET Migration to use Kendo Grid

We are trying to migrate from using ASP.NET GridView (.ascx file extensions) to use kendo grid features. Currently we are using the licensed product of Kendo UI for our other coldfusion applications. During this research we figured out that migrating ASP.NET requires additional license features like Kendo ASP.NET MVC product. Also our page is very simple dynamic page which displays users table with all the details from the stored procedures. It has only one main functionality to activate/deactivate multiple users using a checkbox. Currently we are using asp:GridView features to handle this. Could you please give more inputs on the following questions.

1. Whether ASP.NET migration to use Kendo Grid feasible?

2. System requirements

3. Kendo Products needed.

4. Steps to setup Kendo in existing ASP.NET Project

5. Configuration level changes needed

6. Any examples on this .ascx pages to use kendo grid with simple functionality.

7. OR Can we use current license product (Kendo UI ONLY) to address this change?

Thanks,

Sujatha

General Discussions

answered on 25 Mar 2021

4 answers

334 views

[Solved] Using bearer auth token on excel export Kendo MVC

Hello

I'm using an external api for my grid datasource, for authorization I'm using this solution:

https://www.telerik.com/forums/asp-net-core-custom-datasource-pass-bearer-token

and is working fine for read event.

$(() => {
var grid = $("#grid").data("kendoGrid");
grid.dataSource.transport.options.read.beforeSend = beforeSendHandler;
});

But unfortunately is not working in Excel Export option, apparently is creating a new web request that doesn't follow the beforeRequest function flow.

Is it possible to add the header to the export request or should I create be a custom action?

Greetings

Grid

answered on 24 Mar 2021

2 answers

202 views

Master-Detail Grid onload all rows collapsible

I designed the Master-detail grid view. But, when I run the code it always show the first row expanded. How to make all rows collapsible on load of page? Rows will be expanded when I click on expand icon.

Example followed : https://demos.telerik.com/aspnet-mvc/grid/detailtemplate

Grid

answered on 24 Mar 2021

1 answer

678 views

how to bind CheckBoxGroupFor to module?

following is my code, but not bind to model
@Html.Kendo().CheckBoxGroupFor(m => m.weekOfDay).Items(i =>
{
i.Add().Label("ma").Value("1");
i.Add().Label("di").Value("2");
i.Add().Label("wo").Value("3");
i.Add().Label("do").Value("4");
i.Add().Label("vr").Value("5");
i.Add().Label("za").Value("6");
i.Add().Label("zo").Value("0");
}).Layout("horizontal")

cannot find any online information.

CheckBoxGroup

answered on 24 Mar 2021

3 answers

1.0K+ views

[Solved] Cache grid pages client-side

I have a read-only grid with paging enabled. It is retrieving the pages via AJAX. Is it possible to configure the grid to only make a AJAX request once for each page and to cache it after that? For example, when the user selects page 2, it makes an AJAX request to retrieve the page 2 records. Then the user selects page 1 again. I don't need it to go back to the server to request these. If it becomes stale, the user can just refresh the whole page. It sounded like the dataSource.transport.cache could do this, but I can't find any way of accessing that via the fluent DataSourceBuilder and it has next to no documentation. I tried setting ServerOperation to false thinking that might control caching, but that just prevented the paging params from being sent with the AJAX request. Can the Grid do this? Here is how my Grid is declared:

@(Html.Kendo().Grid<BatchHistoryVM>()

.Name("grd-batch-history")

.Columns(columns =>

{

columns.Bound(c => c.BatchNumber).Title("Batch #").HeaderHtmlAttributes(textCenterAttr);

columns.Bound(c => c.BatchDate).Title("Batch Date").HeaderHtmlAttributes(textCenterAttr).HtmlAttributes(textCenterAttr).Format("{0:d}");

columns.Bound(c => c.BatchAmount).Title("Batch Amount").HeaderHtmlAttributes(textCenterAttr).HtmlAttributes(textCenterAttr).Format("{0:c}");

columns.Bound(c => c.SettledBy).Title("Settled By").HeaderHtmlAttributes(textCenterAttr).HtmlAttributes(textCenterAttr);

})

.Pageable()

.NoRecords("There is no batch history available")

.Selectable(selectable =>

{

selectable.Mode(GridSelectionMode.Single);

selectable.Type(GridSelectionType.Row);

})

.Pageable(c => c.AlwaysVisible(false).ButtonCount(5))

.DataSource(dataSource => dataSource

.Ajax()

.Model(model => { model.Id(bh => bh.BatchID); })

.PageSize(2)

.ServerOperation(true) //Ask the server to do paging/sorting/filtering/grouping rather than doing it client-side on the already loaded data

.Read(read => read.Action("BatchHistory_Read", "MPC"))

.Events(events => events

.Error("clm.MPC.batchHistoryGrid_onError")

)

.Navigatable()

.Deferred() //Wait for kendo script to load at the bottom of the page

)

Grid

answered on 24 Mar 2021

5 answers

1.2K+ views

Telerik.Web.MVC Extensions

Hi,

We are looking to upgrade your application. Currently it uses:

Telerik.Web.Mvc Version 2011.3.1115.235 (Extensions)

Telerik.Web.UI Version 2013.3.114.35 (Telerik RadControls for ASP.NET Ajax)

The Telerik ASP.Net Ajax I can update using the Wizard tool however the Telerik.Web.Mvc Extensions is no longer supported.

I am wondering what is the best way to replace it with the current Telerik standard?

Thanks,

General Discussions

answered on 24 Mar 2021

4 answers

137 views

PageSizes with GetOptions

Hi. I'm trying to find the current PageSizes for a grid. I know that I can get the current PageSize with GetOptions, but I haven't find a way to get all the PageSizes.

Is there a way to do it using GetOptions? or the only way will be to get the values directly from the dropdown?

Thanks.

Grid

answered on 23 Mar 2021