If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Dataset or Stored Proc ?

We're using MS Sql Server 2005 in development but very likely will be using MSSQL 2000 in production. The platform is Windows Server 2003, .NET Framework v1.1, C#.

I'm in a situation that involves T-Sql queries. I have a table called "tblSections" that contains the fileds: id, parent_id, caption. The ID is the auto-number identity of the record, and the PARENT_ID is the ID that this record considers to be its parent (in most cases it is set to NULL since the record has no parent -- it is the top of the tree-heirarchy). Basically the tblSections table is used to keep track of a user created tree view that can have nodes with children nodes (sections with children sections).

I would like to make a copy of a node that may or may not have children nodes and so I need to make a copy of the tblSection records it is related to. The problem Im having is the unique ID and Parent_ID each record has -- I need to make new sections that replicate the parent-child relationship established by a desired node but how do i generate the correct relationship IDs for these new sections when it is all auto-generated and properly linked?

I was hoping for an efficient approach to making these new sections but if the only choice i have is to just use a cursor to step thru each original section and store the new IDENTITY and then INSERT a new section and add the parent_id, then so be it.

I've heard of .NET Datasets within C# but have never explored them. Maybe this would be useful? Or perhaps Temporary Tables / Variables ?

Although both solutions are more or less of the same complexity, I would advice to use stored procedures, specially if a single logical operation (like copying) translates into multiple row or multiple table operations. Why? Because you're "hiding" the underlying data structure, enforcing integrity, and making it simpler for the upper tier to use that structure.

On a side note, the operation you're trying to make looks to me like a recursive task, so you could simplify it by creating a function that copies a node and then calls itself for each of the child nodes of the node being copied. Here's some rough pseudo-code to make the concept clearer:

Code:

function copy_node (node_id, parent_id)
insert new node into node table with parent parent_id and get id for new node into variable new_node_id;
for each child node of node_id do
-- recursively call itself, passing the child node to be copied and new_node_id as the parent.
copy_node(child_node_id, new_node_id);
end for;
end;