CODEWITHSUNDEEP
asp.netsqldatasource
Ryan
Ryan

Reputation: 253

ASP.NET - Trying to implement SortParameterName property to SqlDataSource

I have several Gridviews and Repeaters bound to SqlDataSources using stored procedures. I am trying to implement sorting functionality into some of these but am having a hard time finding concrete instructions and/or examples of what is required on the SqlDataSource side to generate the ORDER BY's needed. Particularly, I do not understand the point of having a SortParameterName property in the SqlDataSource if all it does is manually connect to an ORDER BY clause in the stored procedure. Why define it as such if it is just another parameter in the SelectParameters list like any other, but just so happens to be connected to the ORDER BY clause? When I run the code example below, I am told there are too many arguments specified (obviously, the extra SortParams argument). Do I really need to alter my stored procedures and add "ORDER BY @SortParams" clauses to the end of the existing queries to make this work? I feel like I am missing something.

    SqlDataSourceInLine.SelectParameters.Clear()
    SqlDataSourceInLine.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
    SqlDataSourceInLine.SelectCommand = "ApproverGetApproved"
    SqlDataSourceInLine.SelectParameters.Add("CompanyID", ConfigurationManager.AppSettings("Temp_CompanyID"))
    SqlDataSourceInLine.SelectParameters.Add("SortParams", "EmpName DESC")
    SqlDataSourceInLine.DataSourceMode = SqlDataSourceMode.DataSet
    SqlDataSourceInLine.SortParameterName = "SortParams"
    Dim dv As DataView = SqlDataSourceInLine.Select(DataSourceSelectArguments.Empty

Any clarification would be appreciated!

Upvotes: 0

Views: 2956

Answers (3)

Magnus
Magnus

Reputation: 1754

I was just trying to figure out how to use the SortParameterName and found this question. After doing some more search I think I have now found the correct answer.

Microsofts page Sorting Data with Data Source Controls says as follows (my emphasis):

The parameter identified by the SortParameterName property is passed to the ObjectDataSource control's SelectMethod or passed as part of the parameter collection to the SqlDataSource control's SelectCommand. The ObjectDataSource control can use the information passed to it in the sort parameter to return the data in sorted order. For the SqlDataSource control, you must supply the name of a stored procedure that can take the sort parameter and return the sorted data, because you cannot pass a parameter as part of an ORDER BY clause.

This indicates that the answer given by Icarus is not correct.

My conclusion is that when the SortParameterName property is set (in combination with an appropriate stored procedure) the Gridview will not do the sorting itself, but will let the datasource do a so called Custom Sorting, which for example would be the necessary way to sort if Custom Paging is used.

Update:
I have now used it in my own programming and confirmed that my conclusion was correct.

Upvotes: 3

Icarus
Icarus

Reputation: 63956

I've never used the SortParamter in the past, but what I gather from the documentation is that the purpose of this parameter is to allow you to get the results sorted in the way you want them in case the stored procedure does not do it already. Other than that, you don't need to use it for anything. A GridView whose datasource is of type SqlDataSource already implements sorting out of the box. You simply need to set the AllowSorting property to True and the SortExpression on every column.

Example:

 <asp:GridView ID=" productsGridView" Runat="server" 
     DataSourceID="SqlproductDataSource" AutoGenerateColumns="False"
        AllowSorting="True" >
        <Columns>
            <asp:BoundField HeaderText="Product" 
              DataField="ProductName" SortExpression="ProductName">
            </asp:BoundField>
  ...

Upvotes: 0

Steve
Steve

Reputation: 399

Nothing is required on the SqlDataSource. You need to implement the Sorting event for the Gridview and something else for the repeaters since they don't have sorting built in. What you could do (if you have small datasets coming back) is to use ViewState and store your DataTable of results and then utilize the DataView and the sorting capability of that, then bind the Repeaters/GridViews to the sorted DataView. You still have to keep track of the SortDirection and SortParameter within ViewState, regardless.

Upvotes: 0

Related Questions