Re: sorting a dataset

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: suzy (suzy_at_spam.com)
Date: 03/04/04 

Date: Thu, 4 Mar 2004 19:28:50 -0000

Hi William

Thanks for your reply. You are obviously far more of an expert on .NET than
me :) I am just a beginner so it's all still very new to me. I had never
really heard of the stuff you mentioned in your post (serialization, etc).

Let me tell you my scenario, then maybe you can give some specific advise?
:) Thanks.

Basically, I want to write a tool that allows me to read/write data to a SQL
db. And I want to reference this tool from asp.net.

So for example, I should be able to select a table to retrieve data from,
and also specify which table(s) to join to from the primary table, etc.

Ideally, I would like to return the data in structured hierarchical xml, eg:
if i retrieved a list of authors joined to books, id want something like
this:

<authors>
    <author id="1" name="tom clancey" dob="03/04/1950">
        <book id="23" title="midnight dream" year="1999" isbn="abc1234567"/>
        <book id="45" title="long road" year="2003" isbn="bff1356657"/>
    </author>
    <author id="2" name="mary smith" dob="05/04/1967">
        <book id="53" title="never say never again" year="1989"
isbn="cgr465767"/>
    </author>
</authors>

However, the only way I can think of getting my xml in this format is to use
FOR XML EXPLICIT in sql server (which is quite hard for me to generate for
custom queries), so i am returning the data in a flat level xml where every
field in my dataset is returned as a node (1 level xml hierachy). Is there
another way to get my xml in my ideal format without using for xml explicit?
Maybe via xsl transformation or something?

Anyhow, as my data is currently being returned as a single table in a
dataset, I wanted to perform sorting/filtering/paging on it so I could build
it into my tool. I know I can copy my dataset into a dataview and sort the
data that way, but then I am stuck as to how to get that data in xml format
back up my call stack. At the moment I am thinking the only way is to doing
a complete re-read of the db building the sort/filter into the query, what
do you think?

While I am here, can I just ask about my updates/inserts? I've read a bit
about data adapters for updates, but am I right in thinking this is only
best used for bulk updates/inserts? I would probably only be
adding/updating 1 record at a time so I was thinking I would just build up a
query and execute the command directly on the db (without using data
adapters). Do you think this is a good idea?

Many many thanks for your help :) It's good to hear other peoples advice
when you are learning..

"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:uJ5UqghAEHA.448@TK2MSFTNGP11.phx.gbl...
> Suzy:
> "suzy" <suzy@spam.com> wrote in message
> news:MsK1c.137$ra.61@news-binary.blueyonder.co.uk...
> > hello.
> >
> > how can i sort data in a dataset? all the examples i have seen on msdn,
> etc
> > are sorting a dataview. this works fine, but i want to return the
results
> > in xml and the dataview doesn't have a .getxml method (unlike the
> dataset).
> >
> > any ideas?
> >
> > thanks.
> >
>
> You can't sort a dataset which if you think about it, kind of makes sense.
> A dataset can have 0 or more tables, but lets say you had 10 tables, three
> of which had relations defined. How would you logically sort this set
since
> not everything relates to each other? Anyway, you could implement a sort
> order of your own, perhaps using IComparable or similar logic, but then
> you'll have to define what sorting is (for instance in my example you
could
> sort by Table Name ) and since a DataSet can involve many tables which may
> or may not be related, it's probably not the way to go if you are looking
to
> sort an individual object.
>
> There's tremendous support for XML in .NET and you can easily create a
class
> that inherits DataView or DataSet and you can them use IFormatter for
> instance or XMLSerializer to serialize your dataview to XML. You can use
> the SoapFormatter or any of the other XML Support but you have many
options.
>
> Also, instead of creating your own class or subclassing a DataView or
using
> the SoapFormatter on it, you could simply serialize the dataset to xml.
> Whenever you need to sort it, deserialize it, create a DataView, do your
> sorting and then use it however you need it. Once you are done,
reserialize
> it.
>
> Suffice to say you have a bunch of good options and if you need any
specific
> help implementing any of these strategies, let me know a little more about
> your DataSet and I'll do what I can to help you.
>
> Cheers,
>
> Bill
> >
>
>

Relevant Pages

  • Re: sorting a dataset
    ... Sorting can be done only on the views. ... persist the table to XML. ... DataView into the cloned DataTable. ... foreach (DataRowView drv in dv1) { ...
    (microsoft.public.dotnet.xml)
  • Re: sorting a dataset
    ... Sorting can be done only on the views. ... persist the table to XML. ... DataView into the cloned DataTable. ... foreach (DataRowView drv in dv1) { ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: dataset tables
    ... There are a bunch of ways but the first one is to sort the first one it's PK ... field (you can create a Dataview on it) by using DataView dv = ... > I have a sql DB table which I can load into a dataset and a XML file I ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Another Flex/VMS example - Browsing the ACCOUNTNG.DAT file
    ... XML) certainly works there.) ... if you specified the sortOn method using Array.NUMERIC the array will be sorted using a numeric sort instead of a string sort. ... My example use the AMF protocol which is native in Flex ... Send me email with a valid email and I will give you access to the repository. ...
    (comp.os.vms)
  • Re: sorting a dataset
    ... Ideally, I would like to return the data in structured hierarchical xml, eg: ... I know I can copy my dataset into a dataview and sort the ... >> are sorting a dataview. ...
    (microsoft.public.dotnet.xml)