Sorting a dataset

[Nikki]

Is it possible to sort a dataset rather than a dataview? I have a web
service that returns a dataset which I would like to sort before
returning it (this is so the sorting is standardised and so
applications that see the results as xml don't have to fiddle around
and sort it themselves later).

I have tried sorting a dataview and adding that dataview's table to
the dataset but the results don't remain sorted. The only way I can
see to do it is if I return a dataview rather than a dataset (which I
don't want to do for various reasons).

Does anyone have a solution to this problem?

Many thanks.

[Cor Ligthert]

Re: Sorting a dataset

Hi Nikki,

I think the best thing to do is to use a dataview to sort and than use that
to make a new dataset by going through that dataview row by row.

What you need probably are
dataset.clone
datatable.impor trow

(I am not sure if you can use the last one however I think that you can try
it).

I hope this helps?

Cor

[John Muse]

RE: Sorting a dataset

I do this in VB.net by creating a datatable as a copy of the dataset then clear the dataset and merge the datatable back into the (cleared) dataset using the select command which allows filtering and sorting. BUT, once you update any data back to a database, the sort is lost, the database is nothing more than a sacck of nuts (data). If this is heading in the right path I can add some code to the discussion.
Hope it helps, John

"Nikki" wrote:
[color=blue]
> Is it possible to sort a dataset rather than a dataview? I have a web
> service that returns a dataset which I would like to sort before
> returning it (this is so the sorting is standardised and so
> applications that see the results as xml don't have to fiddle around
> and sort it themselves later).
>
> I have tried sorting a dataview and adding that dataview's table to
> the dataset but the results don't remain sorted. The only way I can
> see to do it is if I return a dataview rather than a dataset (which I
> don't want to do for various reasons).
>
> Does anyone have a solution to this problem?
>
> Many thanks.
>[/color]

[Jay B. Harlow [MVP - Outlook]]

Re: Sorting a dataset

Nikki,
I would do the sort on the database before filling the DataTable (an Order
By on your Select statement).

Alternatively you may try setting the primary key of the DataTable to the
sort order you want. I don't remember if that makes a difference or not...

Hope this helps
Jay


"Nikki" <nikkinz@talk21 .com> wrote in message
news:8dac9744.0 407112135.7b698 1fc@posting.goo gle.com...[color=blue]
> Is it possible to sort a dataset rather than a dataview? I have a web
> service that returns a dataset which I would like to sort before
> returning it (this is so the sorting is standardised and so
> applications that see the results as xml don't have to fiddle around
> and sort it themselves later).
>
> I have tried sorting a dataview and adding that dataview's table to
> the dataset but the results don't remain sorted. The only way I can
> see to do it is if I return a dataview rather than a dataset (which I
> don't want to do for various reasons).
>
> Does anyone have a solution to this problem?
>
> Many thanks.[/color]

[Marc Scheuner [MVP ADSI]]

Re: Sorting a dataset

>Is it possible to sort a dataset rather than a dataview?

No - a DataSet is a collection of DataTables - you can't sort the
DataSet - what on??

Also, if ever possible, try to get the data in the right sort order
right from the beginning, e.g. add a "ORDER BY" statement to your SQL
query or stored procedure to safe yourself from having to sort the
data after it's been retrieved.

If you can't do that, then you'll need to use a DataView - the
DataTable inside a DataSet doesn't have any concept of "sort order"
per se.

Marc
=============== =============== =============== =============== ====
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)i nova.ch

[Cor Ligthert]

Re: Sorting a dataset

Hi Marc,

The problem as Niki described is a very normal situation. He needs a
standalone dataset used on the client by instance using a webservice.

A way can be to collect it using an select with an order by.

However when there is needed after that some additions than that is
impossible.

Than the method I described using a dataview (or a sorted table or icomparer
or whatever but I would take the dataview) and use that to create a new
dataset by cloning the table(s) and fill it again is for me the way to go.

This because you wrote.
[color=blue]
>No - a DataSet is a collection of DataTables - you can't sort the
> DataSet - what on??
>[/color]
Cor

[Marc Scheuner [MVP ADSI]]

Re: Sorting a dataset

>A way can be to collect it using an select with an order by.

That's what I'm trying to say - if ever possible, do it right from the
beginning - don't just return an unsorted list of data, and then sort
if on the client, if the server could have returned it in the right
sort order already!
[color=blue]
>However when there is needed after that some additions than that is
>impossible.[/color]

Yes, agreed - but again - you CANNOT sort a DataSet - you can maybe
sort a DataTable. But even then - I don't like the idea of
duplicating, cloning etc., just to get the right sort order, if the
DataView already offers that WITHOUT any need to duplicate. Duplicates
are always redundant, and redundancy in database is a big no-no.

Marc
=============== =============== =============== =============== ====
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)i nova.ch

[Marc Scheuner [MVP ADSI]]

Re: Sorting a dataset

>A way can be to collect it using an select with an order by.

That's what I'm trying to say - if ever possible, do it right from the
beginning - don't just return an unsorted list of data, and then sort
if on the client, if the server could have returned it in the right
sort order already!
[color=blue]
>However when there is needed after that some additions than that is
>impossible.[/color]

Yes, agreed - but again - you CANNOT sort a DataSet - you can maybe
sort a DataTable. But even then - I don't like the idea of
duplicating, cloning etc., just to get the right sort order, if the
DataView already offers that WITHOUT any need to duplicate. Duplicates
are always redundant, and redundancy in database is a big no-no.

Marc
=============== =============== =============== =============== ====
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)i nova.ch

[Cor Ligthert]

Re: Sorting a dataset

Marc,

I did not say that I disagree, however how you do what you wrote when a
sorted disconnected dataset is needed at the clientside by instance because
it is readed with loadXML or something like that and the situation is that
before that some not database rows has to be added.

In most other situations we agree.

And do not take the subject to strict, I assume that the OP asked with
sorting a dataset sorting the tables and probably he has only one table in
it. Where I was as well writing about the table(s) contained in the dataset.

Cor
[color=blue][color=green]
> >A way can be to collect it using an select with an order by.[/color]
>
> That's what I'm trying to say - if ever possible, do it right from the
> beginning - don't just return an unsorted list of data, and then sort
> if on the client, if the server could have returned it in the right
> sort order already!
>[color=green]
> >However when there is needed after that some additions than that is
> >impossible.[/color]
>
> Yes, agreed - but again - you CANNOT sort a DataSet - you can maybe
> sort a DataTable. But even then - I don't like the idea of
> duplicating, cloning etc., just to get the right sort order, if the
> DataView already offers that WITHOUT any need to duplicate. Duplicates
> are always redundant, and redundancy in database is a big no-no.
>
> Marc
>[/color]

[Cor Ligthert]

Re: Sorting a dataset

Marc,

I did not say that I disagree, however how you do what you wrote when a
sorted disconnected dataset is needed at the clientside by instance because
it is readed with loadXML or something like that and the situation is that
before that some not database rows has to be added.

In most other situations we agree.

And do not take the subject to strict, I assume that the OP asked with
sorting a dataset sorting the tables and probably he has only one table in
it. Where I was as well writing about the table(s) contained in the dataset.

Cor
[color=blue][color=green]
> >A way can be to collect it using an select with an order by.[/color]
>
> That's what I'm trying to say - if ever possible, do it right from the
> beginning - don't just return an unsorted list of data, and then sort
> if on the client, if the server could have returned it in the right
> sort order already!
>[color=green]
> >However when there is needed after that some additions than that is
> >impossible.[/color]
>
> Yes, agreed - but again - you CANNOT sort a DataSet - you can maybe
> sort a DataTable. But even then - I don't like the idea of
> duplicating, cloning etc., just to get the right sort order, if the
> DataView already offers that WITHOUT any need to duplicate. Duplicates
> are always redundant, and redundancy in database is a big no-no.
>
> Marc
>[/color]

[Marc Scheuner [MVP ADSI]]

Re: Sorting a dataset

>And do not take the subject to strict, I assume that the OP asked with[color=blue]
>sorting a dataset sorting the tables and probably he has only one table in
>it. Where I was as well writing about the table(s) contained in the dataset.[/color]

Oh, I imagined as much, but I think it's important to be very clear
about the terminology, in order to really understand what's happening.
The fact Microsoft choose to call a collection of tables and relations
a "DataSet" is a bit unfortunate, since that's really a very generic
term, but that's the way it is in the .NETframework, and you need to
be careful to use the appropriate terms, in order to minimize
confusion and chaos ;-)

Marc
=============== =============== =============== =============== ====
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)i nova.ch

[Marc Scheuner [MVP ADSI]]

Re: Sorting a dataset

>And do not take the subject to strict, I assume that the OP asked with[color=blue]
>sorting a dataset sorting the tables and probably he has only one table in
>it. Where I was as well writing about the table(s) contained in the dataset.[/color]

Oh, I imagined as much, but I think it's important to be very clear
about the terminology, in order to really understand what's happening.
The fact Microsoft choose to call a collection of tables and relations
a "DataSet" is a bit unfortunate, since that's really a very generic
term, but that's the way it is in the .NETframework, and you need to
be careful to use the appropriate terms, in order to minimize
confusion and chaos ;-)

Marc
=============== =============== =============== =============== ====
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)i nova.ch