[MSSql] hierarchyid 데이터형을 이용한 트리구조 생성

/// <summary>
/// 기본코드를 트리구조로 표시하는 메인 함수
/// </summary>
/// <param name="tv">트리뷰 컨트롤</param>
/// <returns>데이터가 있으면 데이터의 개수, 그렇지않으면 0</returns>
static public int F_SNPCODE_TREE(TreeView tv, string strGroup="", string strCode="", string strCodeName="")
        {
            try
            {
                if (OpenDB() == false)
                    return 0;

                Cursor.Current = Cursors.WaitCursor;

                tv.Nodes.Clear();

    List<BaseCodeItem> lstCode = new List<BaseCodeItem>();

    SqlDataReader ds = null;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dbConn;
                cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "SP_GET_CODE_TREE";
    cmd.Parameters.AddWithValue("@V_GROUP", strGroup);
    cmd.Parameters.AddWithValue("@V_CODE", strCode);
    cmd.Parameters.AddWithValue("@V_NAME", strCodeName);
    //cmd.Parameters.AddWithValue("@V_ORDER", "[BASE_ID]");

    ds = cmd.ExecuteReader();

    if (ds.HasRows)
    {   //HID, HidLevel,[PATH],[BASE_ID],[GROUP],GRPNAME,[NAME],C.[ENAME],C.[VALUE],C.[DESC],C.[ORDER],[YN],[YN_DATE],[REG_DATE],[CDATE]
     while (ds.Read())
     {
      BaseCodeItem code = new BaseCodeItem();

      int i = -1;

      if (ds.IsDBNull(++i) == false)
       code.hid = SqlHierarchyId.Parse(ds["HID"].ToString());
      if (ds.IsDBNull(++i) == false)
       code.level = Convert.ToInt32(ds[i].ToString());
      if (ds.IsDBNull(++i) == false)
       code.path = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.id = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.group = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.gname = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.name = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.ename = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.val = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.desc = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.order = Convert.ToInt32(ds[i].ToString());
      if (ds.IsDBNull(++i) == false)
       code.yn = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.yn_date = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.regdate = ds[i].ToString();
      if (ds.IsDBNull(++i) == false)
       code.cdate = ds[i].ToString();

      lstCode.Add(code);
     }
    }

    CloseDB();

    LoadTreeBaseCodes(tv, lstCode);
    
                tv.ExpandAll();
            }
            catch (Exception er)
            {
                Console.WriteLine(string.Format("[SNPSqlLib=>LoadData] {0}", er.Message));
            }
   finally
   {
    CloseDB();
   }

            Cursor.Current = Cursors.Default;
           
            return 0;
        }

  /// <summary>
  /// hierarchyid 데이터형을 가지고 트리를 구성한다.
  /// </summary>
  /// <param name="tv">트리뷰 컨트롤</param>
  /// <param name="lstCode">List<BaseCodeItem></param>
  static private void LoadTreeBaseCodes(TreeView tv, List<BaseCodeItem> lstCode)
  {
   try
   {
    tv.Nodes.Clear();

    TreeNode node;

    //Top Node
    //SqlHierarchyId hid = new SqlHierarchyId();

    IEnumerable<BaseCodeItem> rows = from order in lstCode.AsEnumerable()
             where order.level == 1
             select order;

    foreach (BaseCodeItem it in rows)
    {
     node = new TreeNode(it.name);

     node.Tag = it;

     LoadNodeBaseCodes(node, lstCode);

     tv.Nodes.Add(node);
    }
   }
   catch (Exception er)
   {
    Console.WriteLine(string.Format("[SNPSqlLib=>LoadNodeBaseCodes] {0}", er.Message));
   }
  }

  /// <summary>
  /// hierarchyid 데이터형을 가지고 하위트리를 구성한다.
  /// </summary>
  /// <param name="parentNode">상위 트리노드</param>
  /// <param name="lstCode">List<BaseCodeItem></param>
  static private void LoadNodeBaseCodes(TreeNode parentNode, List<BaseCodeItem> lstCode)
  {
   try
   {
    parentNode.Nodes.Clear();

    SqlHierarchyId hid = new SqlHierarchyId();
    BaseCodeItem itParent = (BaseCodeItem)parentNode.Tag;
    hid = (SqlHierarchyId)itParent.hid;

    IEnumerable<BaseCodeItem> rows = from order in lstCode.AsEnumerable()
               where order.hid.GetAncestor(1).Equals(hid)
               select order;

    foreach (BaseCodeItem it in rows)
    {
     TreeNode node = new TreeNode(it.name);
     node.Tag = it;

     LoadNodeBaseCodes(node, lstCode);
     parentNode.Nodes.Add(node);
    }
   }
   catch (Exception er)
   {
    Console.WriteLine(string.Format("[SNPSqlLib=>LoadNodeBaseCodes] {0}", er.Message));
   }
  }