Basic Operations over Trees in SQL Server
Now that we've got a self-maintaining structure in SQL Server for storing our tree structure, we can start creating our SQL Server/C# implementation. I'm going to be lazy and simply set the connection string as a constant in our class - but you can easily choose to load it from a configuration file or whatever suits you.
public class SqlServerTreeProvider
{
const string connectionString = "server=localhost;uid=someUser;pwd=somePassword;database=dfTreeDemo";
...
}
To start with, we're going to get the boring stuff out of the way - adding, updating and deleting nodes.
Adding nodes
In order to add a node, we simply insert a new row into the table, specifying the values of the name and parentId columns. The insert trigger we specified earlier will automatically calculate values for the depth and lineage fields.
CREATE PROCEDURE dfTreeAddNode
(
@name VARCHAR(50),
@parentId INT
)
AS
IF @parentId=0 SET @parentId=NULL
INSERT INTO dfTree (name,parentId) VALUES (@name,@parentId)
RETURN scope_identity()
Note that our stored procedure uses the scope_identity() function in order to return the identifier of the row we create. (If you're used to using @@identity in these situations - you should probably be using scope_identity instead - take a look here.)
In our SqlServerTreeProvider class, we just need some simple plumbing to expose this operation:
public int AddNode(TreeNode treeNode)
{
// automatically dispose of the SqlConnection object
// when we're done
using (SqlConnection sqlConn = new SqlConnection(connectionString) )
{
// execute the dfTreeAddNode procedure
SqlCommand sqlComm = new SqlCommand("dfTreeAddNode",sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.Add("@name",SqlDbType.VarChar,50).Value = treeNode.Name;
sqlComm.Parameters.Add("@parentId",SqlDbType.Int).Value = treeNode.ParentID;
sqlComm.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
return (int)sqlComm.Parameters["@RETURN_VALUE"].Value;
}
}
Note that we take the returned value from the stored procedure in order to populate the UniqueID field of the TreeNode object we were passed.
Updating nodes
Updating nodes are equally simple, and again our triggers will automatically adjust the lineage and depth fields if the parentId column has changed.
CREATE PROCEDURE dfTreeUpdateNode
(
@id INT,
@name VARCHAR(50),
@parentId INT
)
AS
UPDATE dfTree SET name=@name,parentId=@parentId WHERE id=@id
And again, a very straightforward implementation for our provider class.
public void UpdateNode(TreeNode treeNode)
{
using (SqlConnection sqlConn = new SqlConnection(connectionString) )
{
// execute the dfTreeUpdateNode stored procedure
SqlCommand sqlComm = new SqlCommand("dfTreeUpdateNode",sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.Add("@id",SqlDbType.Int).Value = treeNode.UniqueID;
sqlComm.Parameters.Add("@parentId",SqlDbType.Int).Value = treeNode.ParentID;
sqlComm.Parameters.Add("@name",SqlDbType.VarChar,20).Value = treeNode.Name;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
}
}
Deleting nodes
Our implementation of removing nodes from the tree gives us the first hint of how we might use the lineage field to perform operations over the tree in our table. When we delete a node, we also want to remove all subchildren, otherwise we'll end up creating orphan nodes (which have no valid parents). These subchildren include not only direct children of the node we are deleting, but children of its children, and so on.
As you'll remember, the lineage field contains the id's of the nodes in the path from the root node to whichever node we're looking at, delimited by '/'. As an example, say we want to remove a node with id 5, which has a lineage field set to "/1/2/5/". Any children of this node will have a lineage field that starts with that string. Therefore, in order to delete the node along with all its children, all we need to do is remove all rows from the table that have a lineage field starting with "/1/2/5/". In order to do this, we can use the LIKE operator, which allows us to specify the % wildcard character which matches zero or more characters:
DELETE FROM dfTree WHERE lineage LIKE '/1/2/5/%'
Once we've figured this out, it's a simple matter to implement the DeleteNode procedure:
CREATE PROCEDURE dfTreeDeleteNode ( @id INT ) AS
DELETE FROM dfTree
WHERE lineage LIKE (SELECT lineage
FROM dfTree
WHERE id = @id) + '%'
And again, the standard plumbing in our provider class:
public void RemoveNode(int uniqueID)
{
using (SqlConnection sqlConn = new SqlConnection(connectionString) )
{
// execute the dfTreeDeleteNode stored procedure
SqlCommand sqlComm = new SqlCommand("dfTreeDeleteNode",sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.Add("@id",SqlDbType.Int).Value = uniqueID;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
}
}