将TreeView存储、写入数据库的扩展类TreeViewEx
在一个项目中用到了读取、生成并保存TreeView内容到数据库的应用，经过研究，并着重参考了planet-source-code上的一篇文章<TreeView Interface (Drag and Drop; Add, Rename, and Delete Nodes; Save Tree to Database)>，将原文的VB.NET代码改造成了C#代码，与大家共同分享。
using System;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
namespace CallingCenterAdmin
{
/// <summary>
/// TreeViewToDB 的摘要说明。
/// </summary>
public class TreeViewToDB
{
// m_alDeletedNodes is used to store node deletions in memory. A call to
// CTreeView.SaveNodeCollection() will commit the deletions, and other changes,
// to the database.
public ArrayList m_alDeletedNodes;
public OleDbConnection m_conn;
public TreeViewToDB(OleDbConnection conn)
{
// Initialize the DeletedNodes collection.
m_alDeletedNodes = new ArrayList();
m_conn = conn;
}
public bool DeleteNode(TreeNode tnStart)
{
// PURPOSE: This function will delete the designated node (tnStart) and all
// of its children. The deletions will be stored in a collection. This will
// keep the deletions in memory, which configuration will allow us to rollback
// deletions.
// Get a reference to the start node parent.
TreeNode tnParent = tnStart.Parent;
// Delete the start node's children. This is performed via
// recursion, which will walk through all children regardless of number or
// arrangement. Walking through each and every child of the start node will
// allow us to synchronize node deletions with the database. Simply calling
// the remove function will remove the node and its children, but
// will leave orphan records in the database.
if (!DeleteNodeRecursive(tnStart))
{
return false;
}
// Record the deletion of the start node.
m_alDeletedNodes.Add(tnStart);
// Remove the start node from the TreeNodeCollection.
tnStart.Nodes.Remove(tnStart);
return true;
}
public bool DeleteNodeRecursive(TreeNode tnParent)
{
// PURPOSE: This function will walk through all the child nodes for a given
// node. It will remove all the nodes from the TreeNodeCollection and will
// record all deletions in memory. Deletions will be committed to the
// database when the user calls the CTreeView.SaveNodeCollection() method.
TreeNode tn = null;
int nCount = 0;
try
{
nCount = tnParent.GetNodeCount(false);
if (nCount > 0)
{
tn = tnParent.Nodes[0];
do
{
nCount = tn.GetNodeCount(false);
if (nCount > 0)
{
DeleteNodeRecursive(tn);
}
m_alDeletedNodes.Add(tn);
tn = tn.NextNode;
}
while (tn != null);
}
return true;
}
catch
{
return false;
}
}
public bool IsDropAllowed(TreeNode tnStart, TreeNode tnDrop)
{
// PURPOSE: This function will determine if a drop will cause a circular
// reference. A circular reference occurs when a node is dropped onto one
// of its children.
TreeNode tnCurrent = tnDrop;
do
{
if (tnCurrent == tnStart)
{
return false;
}
tnCurrent = tnCurrent.Parent;
}
while (tnCurrent == null);
return true;
}
public void PopulateTree(TreeView oTv)
{
// PURPOSE: This function will populate a TreeView control from the
// database.
// Clear the DeletedNodes collection; thereby rolling back any deletes
// that were made since the last call of the CTreeView.SaveTreeNodeCollection()
// method.
m_alDeletedNodes = null;
m_alDeletedNodes = new ArrayList();
// Retrieve a list of node items from the database.
string strSql = "SELECT * FROM [TreeViewItems] ORDER By iSort;";
OleDbDataReader rdr = GetDataReader(strSql);
// collNodeIDs is used to store a relationship between keys
// (in this case uids) and Nodes. The collection is used to
// maintain parent-child relationships when populating the
// TreeView control.
ArrayList collNodeKeys = new ArrayList();
TreeNode tnNew;
TreeNode tnParent;
while (rdr.Read())
{
if (rdr.GetBoolean(0))//"bRoot"
{
tnNew = oTv.Nodes.Add(rdr.GetString(1));//"sName"
tnNew.Tag = rdr.GetString(2);//"uid"
// Record the relationship of uid to node. This will allow
// us to retrieve a given node by providing the uid as a
// key.
collNodeKeys.Add(tnNew);
}
else
{
// Get the parent node based on the relationship stored in the
// database. This relationship is recorded or updated when a
// call is made to CTreeView.SaveTreeNodeCollection().
tnParent = (TreeNode)collNodeKeys[rdr.GetInt32(3)];//"iParentID"
// Add the child to the parent;
tnNew = tnParent.Nodes.Add(rdr.GetString(4));//"sName"
tnNew.Tag = rdr.GetString(2);//"uid"
// Record the relationship of uid to node. This will allow
// us to retrieve a given node by providing the uid as a
// key.
collNodeKeys.Add(tnNew);
}
}
rdr.Close();
}
public void SaveNodeCollection(TreeNode tnRootNode)
{
// PURPOSE; This method will save the TreeNodeCollection to the
// database. It uses recursion to walk through the tree. It must
// be called for each root node, if there is more than one root
// node.
int iCntr = m_alDeletedNodes.Count;
int iRecordID;
TreeNode tn;
// Synch all deleted nodes with the database.
for (int i = 0; i < iCntr; i++)
{
tn = (TreeNode)m_alDeletedNodes[i];
if (tn.Tag.ToString() != "")
{
iRecordID = int.Parse(tn.Tag.ToString());
DeleteRecord(iRecordID);
}
}
if (tnRootNode != null)
{
// Clear the deleted nodes collection because the references
// are no longer required.
m_alDeletedNodes = null;
m_alDeletedNodes = new ArrayList();
// Save all records to the database, starting with the root node. We
// maintain the sort order so that the nodes can be restored in the
// order that they were read. This will prevent adding a node before
// adding its parent.
SaveNodeToDb(tnRootNode, 1);
SaveNodeCollectionRecursive(tnRootNode, 1);
}
}
public void SaveNodeCollectionRecursive(TreeNode tnParent, int iSort)
{
// PURPOSE: This function will save all child nodes in a given order
// starting with the root node and working out towards the child nodes.
// This function uses recursion, and will walk through any tree structure
// regardless of node count or arrangement.
TreeNode tn;
int nCount = tnParent.GetNodeCount(false);
if (nCount > 0)
{
tn = tnParent.Nodes[0];
}
else
{
tn = null;
}
do
{
iSort++;
SaveNodeToDb(tn, iSort);
nCount = tn.GetNodeCount(false);
if (nCount > 0)
{
SaveNodeCollectionRecursive(tn, iSort);
}
tn = tn.NextNode;
}
while (tn == null);
}
public void SaveNodeToDb(TreeNode tn, int iSort)
{
// PURPOSE: The following method will save the designated node to the
// database.
bool bRoot;
int iNewRecordID;
int iParentID;
string sName;
string sFullPath;
string strSql;
OleDbCommand cmd = m_conn.CreateCommand();
if (tn.Parent != null)
{
iParentID = int.Parse(tn.Parent.Tag.ToString());
bRoot = false;
}
else
{
iParentID = -1;
bRoot = false;
}
// Need to escape single and double quotes; otherwise, they will cause
// exceptions when posting to the database.
sName = tn.Text;
sFullPath = tn.FullPath;
// I use the tag value to determine if a record for the node exists
// in the database and to hold the value of the primary key if the
// the record exists in the database. If the tag value is empty, then
// I know the record is newly created and not yet saved in the database.
if (tn.Tag.ToString() == "")
{
// Insert a record into the database for the node.
strSql = "INSERT INTO [TreeViewItems] (bRoot, dLastModified, iImageIndex," +
"iParentID, iSelectedImageIndex, iSort, sName, sFullName) VALUES " +
"(" + bRoot + ",'" + DateTime.Now + "'," + tn.ImageIndex + "," +
iParentID + ", " + tn.SelectedImageIndex + "," + iSort + ",'" +
sName + "', '" + sFullPath + "')";
// Execute the INSERT statement against the database.
ExecuteNonQuery(strSql);
// Get the record ID for the newly created record. This assumes that
// only one person is using the database.
iNewRecordID = GetScalar("SELECT Max(uid) FROM [TreeViewItems]");
// Place the record ID in the node's tag.
tn.Tag = iNewRecordID.ToString();
}
else
{
// Update the corresponding record in the database for the node.
strSql = "UPDATE [TreeViewItems] " +
"SET sName='" + sName + "', " +
"bRoot=" + bRoot + ", " +
"iImageIndex=" + tn.ImageIndex + ", " +
"iParentID=" + iParentID + ", " +
"iSelectedImageIndex=" + tn.SelectedImageIndex + ", " +
"iSort=" + iSort + ", " +
"sFullName='" + sFullPath + "' " +
"WHERE uid=" + int.Parse(tn.Tag.ToString());
// Execute the INSERT statement against the database.
ExecuteNonQuery(strSql);
}
}
public OleDbDataReader GetDataReader(string strSql)
{
OleDbCommand cmd = m_conn.CreateCommand();
cmd.CommandText = strSql;
OleDbDataReader rdr = cmd.ExecuteReader();
return rdr;
}
public void DeleteRecord(int nID)
{
// PURPOSE: The following function will delete the designated record from
// the database.
// NOTE: The brackets '[]' are not required around the table name. They
// are only required if the table name contains spaces. I have included
// them as a matter of style.
string strSql = "DELETE FROM [TreeViewItems] WHERE uid=" + nID + ";";
OleDbCommand cmd = m_conn.CreateCommand();
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
public void ExecuteNonQuery(String strSql)
{
// PURPOSE: This function will execute a non-query SQL statement such
// as an INSERT or UPDATE or DELETE statement.
OleDbCommand cmd = m_conn.CreateCommand();
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
public int GetScalar(string strSql)
{
OleDbCommand cmd = m_conn.CreateCommand();
cmd.CommandText = strSql;
int iNewRecordID;
iNewRecordID = int.Parse(cmd.ExecuteScalar().ToString());
return iNewRecordID;
}
}
}