“递归”之无限分类处理(一)

思路：首先把整个DT拿到(避免多次访问数据库),然后首先加载根目录(classpid为0的),然后处理每个TreeNode,并使递归的方法去将当前TreeNode的子节点添加上去。

一、数据库准备

create table ClassList
(
   classid int identity(1,1) primary key,
   classname nvarchar(100) not null,
   classpid int
)

insert into ClassList(ClassName,ClassPID)
values
('Class1',0),
('Class2',0),
('Class3',0),
('Class4',0),
('Class5',1),
('Class6',1),
('Class7',2),
('Class8',2),
('Class9',3),
('Class1',3)

select * from ClassList

结果：

ClassID	ClassName	ClassPID
1	Class1	0
2	Class2	0
3	Class3	0
4	Class4	0
5	Class5	1
6	Class6	1
7	Class7	2
8	Class8	2
9	Class9	3
10	Class10	3

二、SQLHelper准备

class SQLHelper
{
   //连接字符串
   public static string constr="server=.;database=test;uid=sa;pwd=123456";

   //执行一条查询语句
   public static DataTable ExecuteQuery(string cmdText,params SqlParameter[] ps)
   {
      DataTable dt = new DataTable();
      using(SqlConnection conn=new SqlConnection(constr))
      {
         using (SqlCommand cmd = new SqlCommand(cmdText, conn))
         {
             using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
             {
                if (ps != null)
                {
                    cmd.Parameters.AddRange(ps);
                }
                sda.Fill(dt);
            }
         }
      }
      return dt;
   }
}

三、BLL准备

class BLL
{
    //拿到整个datatable
    public static DataTable GetClassDT()
    {
        string cmdText = "select * from classlist";
        DataTable dt = SQLHelper.ExecuteQuery(cmdText);
        return dt;
    }

    //整个datatable被保存到dt中
    static DataTable dt = GetClassDT();

    //绑定根
    public static void Bind(TreeView tv)
    {
       DataRow[] drs = dt.Select("classpid=0");
       foreach (DataRow dr in drs)
       {
           TreeNode tn = new TreeNode();
           tn.Text = dr["ClassName"].ToString();
           tn.Tag = Convert.ToInt32(dr["classid"]);
           tv.Nodes.Add(tn);
       }
       foreach (TreeNode pnode in tv.Nodes)
       {
           Bind(pnode);
       }
    }
    
    //绑定子
    public static void Bind(TreeNode pnode)
    {
        DataRow[] drs = dt.Select("classpid="+pnode.Tag);
        foreach(DataRow dr in drs)
        {
            TreeNode tn = new TreeNode();
            tn.Text = dr["classname"].ToString();
            tn.Tag = dr["classid"].ToString();
            pnode.Nodes.Add(tn);
            Bind(tn);
        }
     }
}

四、Form准备

BLL.Bind(treeView1);

最终结果：