sorting a dataset

[suzy]

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.

 

[William Ryan eMVP]

Suzy:

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

 

[suzy]

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..

 

[Ravi[MSFT]]

Sorting can be done only on the views. One of the possible solutions for
your problem is to create a table from the view's data so that you can
persist the table to XML. To do this, you need to first clone the table's
schema from the view's underlying DataTable and import the rows from the
DataView into the cloned DataTable.
---
DataTable dtClone = dv1.Table.Clone();
foreach (DataRowView drv in dv1) {
dtClone.ImportRow(drv.Row);
}
DataSet ds = new DataSet();
ds.Tables.Add(dtClone);
string xmlData = ds.GetXml();

 

[suzy]

Ravi,

Thanks, that method works perfectly.

Much appreciated!