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:
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?
Thanks! /Niels