How can I sort counted DataTable rows according to specific parameter-CodePudding

I am trying to use dataTable.Rows.Count, but sort the result based on a specific parameter. That parameter being "Column1" in my DataTable. So that the output gives me the rows pertaining to that distinct value only. My DataTable is in a View, but I need to use the sorted int values in a ViewModel.

I am able to count the rows with public static int o { get; set; } and a dt.Rows.Count in my DataTable. I then grab the value by instantiating my View in my ViewModel, with int numberOfRows = ViewName.o;.

But that gives me the total number of rows, whereas I need the number of rows per distinct value in "Column1".

My question is, where and how can I do the required sorting? Because when I've gone as far as to count them and add them to an int (in my ViewModel), there's no way to know what row they used to represent, right? And If I try to sort in the DataTable (in the View) somehow, I don't know how to reference the distinct values. They might vary from time to time as the program is used, so I can't hard-code it.

Comment suggested using query instead, adding my stored procedure for help to implement solution:

SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myProcedure]
    @param myUserDefinedTableType readonly

AS
BEGIN TRANSACTION
    INSERT INTO [dbo].[myTable] (/* list of columns */)
        SELECT [Column1], /* this is the column I need to sort by */
               -- more columns
               -- the rest of the columns
        /* I do aggregations to my columns here, I am adding several thousands row to 20 summarized rows, so after this line, I can no longer get ALL the rows per "Column1", but only the summarized rows. How can I count the rows BEFORE I do the aggregations? */
        FROM @param

        GROUP BY [Column2], [Column1]
        ORDER BY [Column2], [Column1]

// Some UPDATE clauses

COMMIT TRANSACTION

CodePudding user response：

I believe the wisest choice is to act in the db side. Assuming you're using SQL Server, the query should be

SELECT *, COUNT(*) OVER (PARTITION BY Column1) AS c
FROM Table1
ORDER BY c

This query returns the data on your table "Table1", plus the column "c" that represents the count of the value of "Column1", per each row.
Finally, it sorts rows by the column "c", as you request.

CodePudding user response：

DataView dv = dt.DefaultView;
            dv.Sort = "SName ASC"; -- your column name
            DataTable dtsorted = dv.ToTable();