i need an sql or .net expert !

[suzy]

it seems like a simple enough problem, but i need some help......

i have a table of messages (like newsgroup messages) in sql server. my
application will allow people to read threads/messages and reply to messages
over the net.

the table has the following columns:
messageId - unique primary key
parent message id - if someone replies to an existing message, the parent
message's id goes here. (if message has no parent then this will be 0).
root message id - the id of the first message in the thread.
subject - the subject of the message
text - the main text of the message.

basically, what i want is to write some code that takes in a root message id
as a parameter, then returns all message in that thread.

however, i need to data to be returned hierarchically, so anything along the
lines of xml is needed. if i could do this using the DataSet object in .NET
it would be ideal.



ideally, the data will be returned in the following format:

<messages>

<message id="1" parentmessageid="0" rootmessageid="1" subject="hello"
text="hi everyone, how are you">

<message id="2" parentmessageid="1" rootmessageid="1" subject="Re:
hello" text="im fine, you?">

<message id="4" parentmessageid="2" rootmessageid="1"
subject="Re: Re: hello" text="im good too!">
</message>

</message>

<message id="3" parentmessageid="1" rootmessageid="1" subject="Re:
hello" text="im terrible.">
</message>

</message>

</messages>

 

[Chris Capel]

So do something like this. You can use some of the XML writer classes to
write the thread to XML instead of putting it into the MessageboardMessage
class like this if you want. It shouldn't be a big change to the code.

class MessageboardMessage {
public MessageboardMessage(int ID, string Text, etc) { etc }
public int ID;
public string Text, Title, Author, Email, Etc, Etc;
public MessageboardMessageCollection children; /*This could be a simple
array too. You would inherit a class from CollectionBase to do this.*/
}

class OtherClass {
MessageboardMessage rootMessage;
void SomeFunc() {
DataTable dtMain; /*this could be a typed datatable if you wish*/

/*Code here to fill table with this query "SELECT * FROM messages
WHERE Root_ID = " + some ID + "OR ID = " + same ID + "ORDER BY Date ASC", so
that the root message is first, though it's probably redundant; the natural
order should be by date */

rootMessage = new MessageboardMessage(dtMain.Rows[0]["ID"], /*fill
in constructor here*/);
MakeMessage (dtMain, rootMessage);
}
void MakeMessage(DataTable t, MessageboardMessage m) {
foreach (DataRow r in t)
if (r["Parent_ID"] == m.ID) {
MessageboardMessage child = new MessageboardMessage(r["ID"],
/*etc*/);
m.children.Add(child);
MakeMessage(t, child);
}
}
}

Disclaimer: Fix any typos you see in this code. Then re-write it. It's
really just to give you an idea, you know.

Chris

 

[Mark Pearce]

Hi Peter,

Long time, no hear! My book's finished, baby has arrived, albeit three
months' premature, now six months old and thriving. You in this country, and
up for a drink?

Regards,

Mark
--
Author of "Comprehensive VB .NET Debugging"
http://www.apress.com/book/bookDisplay.html?bID=128


Getting the data into a hierarchical format might require a bit of extra
effort at the database design level, or at the code level. The immediate
thing that springs to mind would be to use the Composite pattern to build a
tree in memory of objects that might contain other objects, that might
contain other objects and so on. It would be trivial to have such a
composite render itself as hierarchical XML.

A select using ordering on the message and parent ids would appear to be the
easiest way to go in getting data out in almost the right format.

Do a Google on "Composite pattern" or take a look at the Gang Of Four
Software Patterns book.

Hope that helps,

--
Peter Wright
Author of ADO.NET Novice To Pro, from Apress Inc.


_____________________________