This is a migrated thread and some comments may be shown as answers.
Converting DataSourceRequest filters to SqlServer parameterized query in controller Read method
5 Answers 386 Views
This is a migrated thread and some comments may be shown as answers.
Richard
Top achievements
Rank 1
Richard asked on 10 Jan 2018, 07:10 PM

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)
{
 
    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.

5 Answers, 1 is accepted

Sort by
0
Viktor Tachev
Telerik team
answered on 12 Jan 2018, 12:07 PM
Hello Richard,

Thank you for sharing the functionality with the community. This would help someone facing similar scenario.

As a token of gratitude for sharing your solution you will find your points updated. 

Regards,
Viktor Tachev
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Richard
Top achievements
Rank 1
answered on 13 Jan 2018, 05:06 PM

I neglected to include the string extensions for ToSqlSafe...() methods. Here they are:

using System;
using System.Linq;
using System.Text.RegularExpressions;
 
namespace Elgua.Lab.Chemistry.ExtensionMethods
{
    public static class StringExtensions
    {
        public static string ToSqlSafeLikeData(this string val)
        {
            return Regex.Replace(val, @"([%_\[])", @"[$1]").Replace("'", "''");
        }
 
        public static string ToSqlSafeString(this string val)
        {
            return "'" + val.Replace("'", "''") + "'";
        }
    }
}

 

0
Sylvain
Top achievements
Rank 1
answered on 31 Oct 2019, 06:06 AM

Hello, 

I am trying to add cases for IsContainedIn, IsNullOrEmpty and IsNotNullOrEmpty.

case FilterOperator.IsContainedIn: result = $"CHARINDEX([{fd.Member}],'{fd.Value.ToString().ToSqlSafeLikeData()}') > 0";
break;
case FilterOperator.IsNullOrEmpty: result = $"([{fd.Member}] IS NULL OR [{fd.Member}] = '')";
break;
case FilterOperator.IsNotNullOrEmpty: result = $"(COALESCE([{fd.Member}], '') <> '')";
break;

 

Let me know if you see a problem with these.

Thanks in advance! ☺

0
Khaled
Top achievements
Rank 1
answered on 07 Dec 2020, 12:14 PM

Hallo Richard,

Could you please tell, where the filter method and the .extensions be added ?

thanks in Advance

0
eren
Top achievements
Rank 1
answered on 28 Mar 2021, 11:15 AM
Enjoy and happy coding. => Im soooo happy for coding this temp.
Tags
Grid
Asked by
Richard
Top achievements
Rank 1
Answers by
Viktor Tachev
Telerik team
Richard
Top achievements
Rank 1
Sylvain
Top achievements
Rank 1
Khaled
Top achievements
Rank 1
eren
Top achievements
Rank 1
Share this question
or