Converting DataSourceRequest filters to SqlServer parameterized query in controller Read method

[Richard]

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.

[Ron]

Thank you, Richard,

An "ace in the hole" for a bug holding up a release!

Ron

[Anton Mironov]

Hi Ron,

If any further assistance or information is needed, do not hesitate to open a new thread in our system.

Looking forward to hearing back from you if needed.

Kind Regards,
Anton Mironov

[Viktor Tachev]

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

[Richard]

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("'", "''") + "'";
        }
    }
}

 

[Sylvain]

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! ☺

[Khaled]

Hallo Richard,

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

thanks in Advance

[eren]

Enjoy and happy coding. => Im soooo happy for coding this temp.