对存储过程的结果进行排序

I'd like to sort on a column in the result of a stored procedure without having to add the Order By clause in the stored procedure. I don't want the data to be sorted after I have executed the query, sorting should be part of the query if possible. I have the following code:

我希望对存储过程结果中的列进行排序，而不必在存储过程中添加Order By子句。我不希望在执行查询之后对数据进行排序，如果可能的话，排序应该是查询的一部分。我有以下代码:

public static DataTable RunReport(ReportQuery query)
{
    OffertaDataContext db = new OffertaDataContext();
    Report report = (from r in db.Reports where r.Id == (int)query.ReportId select r).Single(); 
    //???: check security clearance.

    DataSet dataSet = new DataSet();

    /*
    doesn't work, I guess the "Result" table hasn't been created yet;
    if(!string.IsNullOrEmpty(query.SortField))
    {
        dataSet.DefaultViewManager.DataViewSettings["Result"].Sort = query.SortField + " " + (query.SortAscending ? "ASC" : "DESC"); 
    }
    */

    using (SqlConnection conn = new SqlConnection(Config.ConnectionString))
    {
        conn.Open();
        using (SqlCommand exec = conn.CreateCommand())
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                exec.Connection = conn;
                exec.CommandType = CommandType.StoredProcedure;
                exec.CommandText = report.ReportProc;

                adapter.SelectCommand = exec;
                try
                {

                    adapter.Fill(dataSet, query.Skip, query.Take, "Result");
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    conn.Close();
                }
                return dataSet.Tables["Result"];
            }
        }
    }
}

How do I add sorting?

如何添加排序?

2 个解决方案

#1

Get the DataTable you are populating in the dataSet ("Result").

获取正在数据集中填充的数据表(“Result”)。

Now - there's no way to sort the DataTable, except via the Query, View, or Stored Procedure that populates it.

现在——除了通过填充数据的查询、视图或存储过程之外，没有方法对数据表进行排序。

Since you don't wanna do it in the SP, you can sort the DefaultView of the DataTable, or any DataView that is associated with the DataTable.

由于不希望在SP中进行此操作，所以可以对DataTable的DefaultView或与DataTable关联的任何DataView进行排序。

You can achieve it using the Sort property of the DataView. This is a string which specifies the column (or columns) to sort on, and the order (ASC or DESC).

您可以使用DataView的Sort属性实现它。这是一个字符串，指定要排序的列(或列)和顺序(ASC或DESC)。

Example:

例子:

myTable.DefaultView.Sort = "myColumn DESC";

You can now use the DefaultView to do whatever you want (bind it to something or whatever)

你现在可以使用DefaultView来做任何你想做的事情(将它绑定到某个东西或其他东西)

#2

To be honest, since you are using DataTable, you might as well just sort at the client.

老实说，既然您在使用DataTable，您也可以在客户端进行排序。

Dynamic sorting (at the server) via SPs etc is always a pain; to do it in pure TSQL, you either need some horribly inefficient CASE block at the end of the SELECT, or you need to use dynamic SQL (for example via sp_ExecuteSQL), manipulating the ORDER BY in the final query. The only other option (in raw TSQL) would be to EXEC/INTO to get the data into a table variable (or temp table), then SELECT from this with an ORDER BY.

动态排序(在服务器上)一直是一个痛苦的过程;要在纯TSQL中实现这一点，您要么需要在SELECT末尾使用一些非常低效的大小写块，要么需要使用动态SQL(例如通过sp_ExecuteSQL)，在最终查询中操作ORDER。惟一的其他选项(在原始TSQL中)是执行/INTO，将数据放入表变量(或临时表)，然后使用ORDER BY从中选择。

If it is an option, LINQ-to-SQL actually does OK at this; it supports querying (and composing against) UDFs - so rather than an SP, code the query in a UDF (the SP can always just SELECT from the UDF if you need to support legacy callers). Then you can use "order by" etc in a LINQ query:

如果它是一个选项，那么LINQ-to-SQL实际上在这方面做得很好;它支持查询(并针对UDF进行组合)UDF——因此，在UDF中编写查询而不是SP(如果需要支持遗留调用者，SP总是可以从UDF中选择)。然后你可以在LINQ查询中使用“order by”等。

var qry = from row in ctx.SomeMethod(args)
          order by row.Name, row.Key
          select row;

(or there are various methods for adding a dynamic sort to a LINQ query - the above is just a simple example)

(或者有多种方法可以将动态排序添加到LINQ查询中——以上只是一个简单的例子)

the final TSQL will be something like:

最终的TSQL将是这样的:

SELECT blah FROM theudf(args) ORDER BY blah

i.e. it will get it right, and do the "ORDER BY" at the server. This is particularly useful when used with Skip() and Take() to get paged data.

也就是说，它会把它弄对，然后在服务器上按“ORDER BY”进行操作。这在使用Skip()和Take()获取分页数据时特别有用。

#1