ASP.net c# Sorting a data table

Question

I'm using DAL, and having trouble figuring out how to sort the returned data table. The case is throwing an error and intellisense isn't offering me much help:

// Get all the specifications available to this user
Artwork.tblSpecificationsDataTable dsCommon = new Artwork.tblSpecificationsDataTable();            
 using (tblSpecificationsTableAdapter specAdapter = new tblSpecificationsTableAdapter())
 {
  specAdapter.FillByClientID(dsCommon, Master.loginData.loggedInUser.company.ID);
  }

 DataView v = dsCommon.DefaultView;
 v.Sort = "category DESC";
 dsCommon = (Artwork.tblSpecificationsDataTable)v.ToTable();

 for (int i = 0; i < dsCommon.Count; i++)
  {
   test.Text += dsCommon[i].category + " " + dsCommon[i].FlatSize + "<br />";
  }

Error without the cast is:

Error 3 Cannot implicitly convert type 'System.Data.DataTable' to 'Artwork.tblSpecificationsDataTable'. An explicit conversion exists (are you missing a cast?)

Edit

Apparently I should be sorting in the query which is fine, but I want the option to ORDER BY any of the dozen fields without having to create a dozen queries in the table adapter, how do I go about doing this?

I have a table adapter that fetches the rows, I want to return the rows in different ways, lots of sort methods but without having to have a seperate query for each one. Is this the wrong way to go about that? — Tom Gullen
– Tom Gullen, Commented Jan 28, 2011 at 13:43
my answer to the question "what's the best sort method?" is always "the ORDER BY clause in T-SQL". If your client-to-server connectivity is slow, then it makes some sense to download rows once and then sort them in various ways in the client code. Otherwise, it's easiest to let the DB do your sorting. — MusiGenesis
– MusiGenesis, Commented Jan 28, 2011 at 13:49
@Musi How do I sort the rows without having to have dozens of similar queries in the table adapter? — Tom Gullen
– Tom Gullen, Commented Jan 28, 2011 at 13:54

Fenton · Accepted Answer · 2011-01-28 14:03:01Z

2

It isn't necessarily correct to to say that the database will perform the sort faster than the client - in most cases this statement is true, but you would have to look at the query plan in SQL Server to see if it genuinely would be quicker.

You can write a query like this, which allows dynamic sorting in the table adapter...

SELECT
    <field list>
FROM
    <TableName>
WHERE
    <Clause>
ORDER BY
CASE @OrderBy
    WHEN 'Field1' THEN Field1
    WHEN 'Field2' THEN Field2
    WHEN 'Field3' THEN Field3
END

answered Jan 28, 2011 at 14:03

Fenton

253k74 gold badges404 silver badges414 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DOK · Accepted Answer · 2011-01-28 13:57:24Z

2

Here's a guess. According to MSDN, DataView.ToTable() returns a new DataTable. But you are passing that to an existing variable dsCommon.

There is a very similar property DataView.Table that gets or sets the source DataTable behind the DataView.

Does your problem go away if you change the line

dsCommon = (Artwork.tblSpecificationsDataTable)v.ToTable();

to

dsCommon = (Artwork.tblSpecificationsDataTable)v.Table();

answered Jan 28, 2011 at 13:57

DOK

32.8k8 gold badges64 silver badges93 bronze badges

2 Comments

Tom Gullen Over a year ago

Doesn't throw an error but data still not coming up in any particular order.

DOK Over a year ago

Can you use the sorted DataView instead of converting it back to a DataTable then? I believe, based on some Googling DataView Sort not working, that sorting the DataView doesn't affect the underlying DataTable.

Collectives™ on Stack Overflow

ASP.net c# Sorting a data table

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related