How do I insert/delete/update in an ordered tree

Question

I have two tables in which I store a tree with ordered levels like this:

Table: TreeData
---------------
ID (int) (primary key)
Data (string)
Level (int) (not null)


---------------------------------------------
Table: SubTree
---------------
parentID (int) (foreign key #1 to TreeData.ID) 
childID (int) (foreign key #2to TreeData.ID)
orderNumber (int) (not null)

Now lets say on some level 1, I have 2 TreeData entries with ID's 1 2 and, all children of a Treedata entry on 0 with ID 0, in logical order:

TreeData                     SubTree
ID        Level              parentID    childID     orderNumber
---------------              -----------------------------------
0         0                 
1         1                   0           1           1  
2         1                   0           2           2
3         1                   0           3           3

Now I have some data which I want to insert in the database on level 1, as child of parent 0 but "between" children 1 and 2 so the database ends up like this:

TreeData                     SubTree
    ID        Level              parentID    childID     orderNumber
    ---------------              -----------------------------------
    0         0                 
    1         1                   0           1           1  
    2         1                   0           2           3            <-- order increased 1
    3         1                   0           3           4            <-- order increased 1
    4         1                   0           4           2

The data at hand on which I need to base where the new data goes is as follows:

Data (string) // the data itself
Level (int) // the level it needs to be on
parentID (int) // the id of the parent
reference_orderNumber (int) // reference to the ordernumber 'this' data 
                            // needs to come before or after
placement (string) // either "before" or "after"
                   // in case of "after" the order number of 'this' data
                   // should be reference_orderNumber +1
                   // in case of "before" the order number of 'this' data 
                   // should be equal to reference_odrderNumber

Where and how should I implement this?

Is the SQL server (I'm using MS SQL Server 2008 Developer Edition) capable of doing this ordering? (in this case I assume that would be the fastest method)

Or do I really need to fetch all the data with higher ordernumbers through linq2sql, update it inside my C# code, and put it all back into the SQL server?

How about: Moving some data inside the level (from ordernr 2 to 4, so 4 becomes 3 and 3 becomes 2)

Deleting (deleting with ordernumber 2, so 3 becomes 2 and 4 becomes 3)

Moving data outside the level (from ordernr 2 (so 4 becomes 3 and 3 becomes 2) level 1 to higher/lower level, so that level needs to be reordered) ?

Answer 1

This might be a side note, but you probably don't want to do logic in both SQL and C#. Keep it in one place, and it will be easier to maintain. The exception would be if you really, really need the extra speed you may or may not be able to get out of it by taking the shortcut.