3S技术与.NET

即使爬到最高的山上,一次也只能脚踏实地地迈一步。

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
在一个项目中用到了读取、生成并保存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;

  }

 }
}

posted on 2005-01-04 16:32  zhouligis  阅读(4474)  评论(4编辑  收藏  举报