如何使用接受分页参数的存储过程 [英] How to use Stored Procedure that accepts Paging Parameters

问题描述

任何链接都涉及在使用微风时如何使用存储过程的示例，主要是想了解如何提取Paging参数并设置inlinecount值，因为存储的proc将返回该值并采用分页参数。

Any links samples on how to use stored procedure when using breeze, mainly interested in finding out how to extract Paging parameters and set inlinecount value, since stored proc will return that value and take paging parameters.

IE

    function GetData(int Pageindex,int PageSize, string SP_Input_Para1,string 

SP_Input_Para2等等...。

SP_Input_Para2 and so on....

更新

    function Update(string SP_Input_Param1, string SP_Input_Param2 etc)

然后一些如何配置Breeze告诉它应该在服务器上使用以下功能进行Get& Update，Delete，Insert等。

Then some how configure Breeze to tell that it should use the following function on the server for Get & Update,Delete,Insert etc.

或者更好的方法可能是让
作为自定义结构来获取请求和响应

Or a better way could be that For Get use Request and Response as custom structure

i.e
    public class MyResponse
{
    public IEnumerable<Object> Results { get; set; }
    public string Message { get; set; }


}
public class MyRequest
{
    public PagingInfo pageInfo { get; set; }
    public ParameterInfo Parameters { get; set; }
}


public class PagingInfo
{
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
}

public class ParameterInfo
{
    public string Parameter1 { get; set; }
    public string Parameter2 { get; set; }
    public string Parameter3 { get; set; }
}

then use

public MyResponse GetData(MyResponse request)
        {
            var resp = new MyResponse();
            var lst = new List<object>();
            // do oyur work
            resp.Results= lst;

            return lst;
        }

现在，您可能需要在客户端上提供一个功能来映射集合

Now you might need to provide a function on the client to Map the Collection

推荐答案

要查询微风请求中的信息，请尝试将其作为GET方法。假定GetMyEntityType在您的数据上下文中返回实体。

To query the information from the breeze request, try this as your GET method. This assumes GetMyEntityType returns an entity in your data context.

[HttpGet]
[EnableBreezeQuery(MaxExpansionDepth = 0)]
public QueryResult GetMyEntityType(ODataQueryOptions<MyEntityType> options)
{
... your server method
}

关于微风请求的所有信息都在此对象中。起初，我想知道如何在此对象中包含其他参数，但是没有必要-参数位于对象内部。

Everything you need to know about a breeze request is in this object. At first I was wondering how to include other parameters with this object, but there was no need - the parameters were inside the object.

要跳过并尝试，请尝试这...

To get your skip and take, try this...

foreach (var queryParam in options.Request.Properties.FirstOrDefault(x => x.Key == "MS_QueryNameValuePairs").Value as System.Collections.Generic.KeyValuePair<string, string>[])
        {
            if (queryParam.Key == "$skip")
                int.TryParse(queryParam.Value, out skip);
            if (queryParam.Key == "$top")
                int.TryParse(queryParam.Value, out top);
        }

提示：
如果$ skip为0，则可能不是在请求参数中。默认将其设置为0。
Breeze使用$ top而不是$ take。

Tips: If $skip is 0, it may not be in the request parameters. Default it to 0. Breeze uses $top, not $take.

您可以使用

DB.ObjectContext.ExecuteStoreQuery<MyEntityType>(query).ToList()

只要查询返回类型为MyEntityType，查询可以是任何东西。 EXEC MyStoredProc param1，param2，或者甚至可以构建动态SQL。

As long as query returns an entity set of type MyEntityType, query can be anything. EXEC MyStoredProc param1, param2, or you could even build dynamic SQL.

要返回自己的inlineCount，请使用QueryResult返回类型：

To return your own inlineCount, use the QueryResult return type:

        return new QueryResult()
        {
            InlineCount = myInlineCount,
            Results = DB.ObjectContext.ExecuteStoreQuery<MyEntityType>(query).ToList()
        };

提取过滤器绝非易事。我找到了一个解决方案，并对其进行了修改以添加递归，并对其进行了调整以处理Breeze请求。这些是您需要提取它们的辅助方法和辅助类。

Extracting your filters is no easy task. I found a solution and modified it to add recursion and tweaked it to handle Breeze requests. These are the helper methods and a helper class that you'll need to extract them.

    private void ProcessUnaryOperator(UnaryOperatorNode unaryOperator, List<ODataFilter> filterList)
    {
        if (unaryOperator != null)
        {
            if (unaryOperator.Operand != null && unaryOperator.Operand.GetType().FullName == "Microsoft.Data.OData.Query.SemanticAst.BinaryOperatorNode")
            {
                ProcessBinaryOperator(unaryOperator.Operand as BinaryOperatorNode, filterList);
            }
        }
    }

    private void ProcessBinaryOperator(BinaryOperatorNode binaryOperator, List<ODataFilter> filterList)
    {
        if (binaryOperator != null)
        {
            if (binaryOperator.Left != null && binaryOperator.Left.GetType().FullName == "Microsoft.Data.OData.Query.SemanticAst.BinaryOperatorNode")
                ProcessBinaryOperator(binaryOperator.Left as BinaryOperatorNode, filterList);

            if (binaryOperator.Right != null && binaryOperator.Right.GetType().FullName == "Microsoft.Data.OData.Query.SemanticAst.BinaryOperatorNode")
                ProcessBinaryOperator(binaryOperator.Right as BinaryOperatorNode, filterList);

            if (binaryOperator.Left != null && binaryOperator.Left.GetType().FullName == "Microsoft.Data.OData.Query.SingleValueFunctionCallNode")
            {
                var singleValueFunctionCallNode = binaryOperator.Left as SingleValueFunctionCallNode;

                var property = (singleValueFunctionCallNode.Arguments.FirstOrDefault() as SingleValuePropertyAccessNode ?? singleValueFunctionCallNode.Arguments.LastOrDefault() as SingleValuePropertyAccessNode) ;
                var constant = (singleValueFunctionCallNode.Arguments.FirstOrDefault() as ConstantNode ?? singleValueFunctionCallNode.Arguments.LastOrDefault() as ConstantNode);

                var lt = string.Empty;
                switch (singleValueFunctionCallNode.Name)
                {
                    case "startswith":
                        lt = constant.Value.ToString() + "%";
                        break;
                    case "endswith":
                        lt = "%" + constant.Value.ToString();
                        break;
                    case "substringof":
                        lt = "%" + constant.Value.ToString() + "%";
                        break;
                    case "equal":
                        lt = constant.Value.ToString();
                        break;
                }

                if (property != null && property.Property != null && constant != null && constant.Value != null)
                    filterList.Add(new ODataFilter(property.Property.Name, binaryOperator.OperatorKind.ToString(), lt, property, constant));
            }

            if (binaryOperator.Left != null && binaryOperator.Left.GetType().FullName == "Microsoft.Data.OData.Query.SingleValuePropertyAccessNode")
            {
                var property = binaryOperator.Left as SingleValuePropertyAccessNode ?? binaryOperator.Right as SingleValuePropertyAccessNode;
                var constant = binaryOperator.Left as ConstantNode ?? binaryOperator.Right as ConstantNode;
                var lt = constant.Value.ToString();

                if (property != null && property.Property != null && constant != null && constant.Value != null)
                    filterList.Add(new ODataFilter(property.Property.Name, binaryOperator.OperatorKind.ToString(), lt, property, constant));
            }
        }
    }

    public class ODataFilter
    {
        public ODataFilter(string prop, string op, string lt, SingleValuePropertyAccessNode pn, ConstantNode cn)
        {
            this.Property = prop;
            this.Operator = op;
            this.LiteralText = lt;
            this.PropertyNode = pn;
            this.ConstantNode = cn;
        }

        public string Property { get; set; }
        public string Operator { get; set; }
        public string LiteralText { get; set; }
        public SingleValuePropertyAccessNode PropertyNode { get; set; }
        public ConstantNode ConstantNode { get; set; }
    }

这是提取过滤器的调用代码：

This is the calling code to extract your filters:

        List<ODataFilter> filterList = new List<ODataFilter>();

        // Not Equal (Unary Operators)
        if (options.Filter != null && options.Filter.FilterClause != null && options.Filter.FilterClause.Expression.GetType().Name == "UnaryOperatorNode")
            ProcessUnaryOperator(options.Filter.FilterClause.Expression as UnaryOperatorNode, filterList);

        // Equal (Binary Operators)
        if (options.Filter != null && options.Filter.FilterClause != null && options.Filter.FilterClause.Expression.GetType().Name == "BinaryOperatorNode")
            ProcessBinaryOperator(options.Filter.FilterClause.Expression as BinaryOperatorNode, filterList);

这里是USING的子集

Here's a subset of USING's

using Microsoft.Data.OData.Query; 
using System.Web.Http.OData.Query; 
using Microsoft.Data.OData.Query.SemanticAst;

您是否知道可以执行一个存储过程，该存储过程返回多个结果集并使用MARS使用这些结果？我目前正在运行一个存储过程，该存储过程在一次SQL往返中返回23 IQueryable 。

Did you know you can execute a stored procedure that returns multiple result sets and consume those results using MARS? I currently run a stored procedure that returns 23 IQueryable's in one SQL round trip.

祝您好运！

（编辑）

如果要在存储过程中手动应用$ skip和$ top，请使用Breeze将尝试第二次将它们应用于结果集。在这种情况下，我只是发送了skip和top作为参数，而不是使用$ skip和$ top。顺便说一句，此错误需要7个小时才能找到。

If you are manually applying the $skip and $top in your stored procedure, Breeze will attempt to apply them against the result set a second time. In this case, I simply sent skip and top as parameters instead of using $skip and $top. And btw, this bug took 7 hours to find.

此链接： https://www.stevefenton.co.uk/2015/07/getting-the-sql-query- from-entity-framework-iqueryable / 显示了如何将IQueryable对象转换为T-SQL。使用options.Filter.ApplyTo将微风过滤器应用于MyEntityType的空白IQueryable，然后使用上面链接中的代码将微风请求呈现到TSQL中。

This link: https://www.stevefenton.co.uk/2015/07/getting-the-sql-query-from-an-entity-framework-iqueryable/ shows how to translate an IQueryable object into T-SQL. Apply the breeze filter to a blank IQueryable of MyEntityType, using options.Filter.ApplyTo, then use the code from the link above to render the breeze request into TSQL.

// Determine the where clause 
var whereClause = options.Filter == null ? "" : ToTraceString<MyEntityType>(
    options.Filter.ApplyTo(
        (from x in DB.Context.MyEntityTypes select x),
        new ODataQuerySettings()) as IQueryable<MyEntityType>)
    .Split(new[] { "\r", "\n" }, StringSplitOptions.RemoveEmptyEntries).Where(x => x.Trim().StartsWith("WHERE")).FirstOrDefault().Trim();

/* Steve Fenton, https://www.stevefenton.co.uk/2015/07/getting-the-sql-query-from-an-entity-framework-iqueryable/
 * July 24, 2015
 * */
private static string ToTraceString<T>(IQueryable<T> query)
{
    var internalQueryField = query.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault();
    var internalQuery = internalQueryField.GetValue(query);
    var objectQueryField = internalQuery.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault();
    var objectQuery = objectQueryField.GetValue(internalQuery) as System.Data.Entity.Core.Objects.ObjectQuery<T>;
    return ToTraceStringWithParameters<T>(objectQuery);
}
private static string ToTraceStringWithParameters<T>(System.Data.Entity.Core.Objects.ObjectQuery<T> query)
{
    System.Text.StringBuilder sb = new StringBuilder();
    string traceString = query.ToTraceString() + Environment.NewLine;
    foreach (var parameter in query.Parameters)
        traceString = traceString.Replace("@" + parameter.Name, "'" + parameter.Value.ToString() + "'");
    return traceString;
}
/* */

这篇关于如何使用接受分页参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！