Dataset or Stored Proc ?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Dataset or Stored Proc ?

  1. #1
    Join Date
    Mar 2004
    Posts
    9

    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 ?

  2. #2
    Join Date
    Dec 2006
    Posts
    13
    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;
    Cheers.

Similar Threads

  1. Replies: 2
    Last Post: 09-13-2008, 11:38 AM
  2. ADO and stored proc parameters data type problem
    By markus in forum VB Classic
    Replies: 13
    Last Post: 01-29-2007, 11:23 AM
  3. Loading Dataset from Stored Proc.
    By jcb1269 in forum .NET
    Replies: 1
    Last Post: 10-17-2006, 02:42 PM
  4. ADO - T-SQL - Stored Proc Question
    By Neil Whitlow in forum VB Classic
    Replies: 2
    Last Post: 08-23-2000, 01:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center