C# WinForm TreeView 动态生成 无限级树 Log4net SQLite

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SQLite;using System.Data;namespace QuartzTaskScheduler{    class SQLiteHelper    {        #region SQlite数据操作        /// <summary>        /// ConnectionString样例：Datasource=Test.db3;Pooling=true;FailIfMissing=false        /// </summary>        public static string ConnectionString { get; set; }        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)        {            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Parameters.Clear();            cmd.Connection = conn;            cmd.CommandText = cmdText;            cmd.CommandType = CommandType.Text;            cmd.CommandTimeout = 30;            if (p != null)            {                foreach (object parm in p)                    cmd.Parameters.AddWithValue(string.Empty, parm);            }        }        public static DataSet ExecuteQuery(string cmdText, params object[] p)        {            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))            {                using (SQLiteCommand command = new SQLiteCommand())                {                    DataSet ds = new DataSet();                    PrepareCommand(command, conn, cmdText, p);                    SQLiteDataAdapter da = new SQLiteDataAdapter(command);                    da.Fill(ds);                    return ds;                }            }        }        public static int ExecuteNonQuery(string cmdText, params object[] p)        {            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))            {                using (SQLiteCommand command = new SQLiteCommand())                {                    PrepareCommand(command, conn, cmdText, p);                    return command.ExecuteNonQuery();                }            }        }        public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)        {            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))            {                using (SQLiteCommand command = new SQLiteCommand())                {                    PrepareCommand(command, conn, cmdText, p);                    return command.ExecuteReader(CommandBehavior.CloseConnection);                }            }        }        public static object ExecuteScalar(string cmdText, params object[] p)        {            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))            {                using (SQLiteCommand command = new SQLiteCommand())                {                    PrepareCommand(command, conn, cmdText, p);                    return command.ExecuteScalar();                }            }        }        /// <summary>        /// 执行SQl语句 返回执行的行数        /// </summary>        /// <param name="str">链接数据库的字符串</param>        /// <param name="SqlString">sql执行语句</param>        /// <param name="Param">参数</param>        public static int SQLiteCommand(string str, string SqlString, params SQLiteParameter[] Param)        {            try            {                SQLiteConnection slcn = new SQLiteConnection(str);                SQLiteCommand slcmd = slcn.CreateCommand();                PrepareCommand(slcmd, slcn, null, SqlString, Param);                return slcmd.ExecuteNonQuery();            }            catch (System.Data.SQLite.SQLiteException ex)            {                throw new Exception(ex.Message);            }        }        /// <summary>        /// 执行SQl语句 返回DataSet        /// </summary>        /// <param name="str">链接数据库的字符串</param>        /// <param name="SqlString">sql执行语句</param>        /// <param name="Param">参数</param>        /// <returns>DataSet</returns>        public static DataSet SQLiteCommandDataSet(string str, string SqlString, params SQLiteParameter[] Param)        {            try            {                DataSet ds = new DataSet();                SQLiteConnection slcn = new SQLiteConnection(str);                SQLiteCommand slcmd = slcn.CreateCommand();                PrepareCommand(slcmd, slcn, null, SqlString, Param);                SQLiteDataAdapter sda = new SQLiteDataAdapter(slcmd);                slcmd.Parameters.Clear();                sda.Fill(ds);                sda.Dispose();                return ds;            }            catch (System.Data.SQLite.SQLiteException ex)            {                throw new Exception(ex.Message);            }        }        /// <summary>        /// 执行SQl语句 返回数据集SQLiteDataReader        /// </summary>        /// <param name="str">链接数据库的字符串</param>        /// <param name="SqlString">sql执行语句</param>        /// <param name="Param">参数</param>        /// <returns>SQLiteDataReader</returns>        public static SQLiteDataReader SQLiteCommandDataReader(string str, string SqlString, params SQLiteParameter[] Param)        {            try            {                SQLiteConnection slcn = new SQLiteConnection(str);                SQLiteCommand slcmd = slcn.CreateCommand();                PrepareCommand(slcmd, slcn, null, SqlString, Param);                SQLiteDataReader myReader = slcmd.ExecuteReader();                slcmd.Parameters.Clear();                return myReader;            }            catch (System.Data.SQLite.SQLiteException ex)            {                throw new Exception(ex.Message);            }        }        public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)        {            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            if (trans != null)                cmd.Transaction = trans;            cmd.CommandType = CommandType.Text;//cmdType;            if (cmdParms != null)            {                foreach (SQLiteParameter parameter in cmdParms)                {                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&                        (parameter.Value == null))                    {                        parameter.Value = DBNull.Value;                    }                    cmd.Parameters.Add(parameter);                }            }        }        #endregion        #region Commit To Database        public static void CommitToDataBase(string connectionString, DataSet ds, string sql)        {            try            {                if (!ds.HasChanges()) { return; }                using (SQLiteConnection conn = new SQLiteConnection())                {                    conn.ConnectionString = connectionString;                    conn.Open();                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))                    {                        SQLiteCommand b = new SQLiteCommand();                        da.Update(ds.Tables[0]);                        ds.AcceptChanges();                    }                }            }            catch (Exception) { throw; }        }        #endregion        #region Get Data Set        public static DataSet GetDataSet(string connectionString, string sql)        {            DataSet ds = new DataSet();            try            {                using (SQLiteConnection conn = new SQLiteConnection())                {                    conn.ConnectionString = connectionString;                    conn.Open();                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))                    {                        da.Fill(ds);                    }                }            }            catch (Exception) { throw; }            return ds;        }        #endregion    }}

通用类：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data;namespace QuartzTaskScheduler{    class TreeViewUtils    {        //创建根节点        public static void BindTreeVew(DataSet ds, TreeView treeView, bool isExpanded, string sSelectedData)        {            //将获取的数据转换为 一个DataTable            DataTable dt = ds.Tables[0];            treeView.Nodes.Clear();//清空 树的所有节点            //获取与排序顺序中的筛选器以及指定的状态相匹配的所有System.Data.DataRow 数组            DataRow[] rowlist = dt.Select("ParentID='0'");            if (rowlist.Length <= 0) return;            //创建跟节点            TreeNode rootNode = new TreeNode();            //设置根节点的属性            rootNode.Text = rowlist[0]["Name"].ToString();            //设置根节点的值            rootNode.Name = rowlist[0]["ID"].ToString();            //rootNode.Expanded = isExpanded;            //rootNode.Selected = true;            //添加跟节点            treeView.Nodes.Add(rootNode);            CreateChildNode(rootNode, dt, isExpanded, sSelectedData);        }        public static void CreateChildNode(TreeNode parentNode, DataTable dt, bool isExpanded, string sSelecedData)        {            //选择数据，添加排序表达市            DataRow[] rowList = dt.Select("ParentID=" + Convert.ToInt32(parentNode.Name));            foreach (DataRow row in rowList)            {                //创建一个新的节点了                TreeNode node = new TreeNode();                node.Text = row["Name"].ToString();                node.Name = row["ID"].ToString();                //node.Expand = isExpanded;                //if (node.Name == sSelecedData)                //{                //    node.Selected = true;                //}                //parentNode.ChildNodes.Add(node);                parentNode.Nodes.Add(node);                //递归调用，创建其他节点                CreateChildNode(node, dt, isExpanded, sSelecedData);            }        }    }}

调用：

        //获取SQlite数据可定义的连接字符串        string DBConStr = string.Format("Data Source={0}", Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf("\\")) + "\\SQLiteDB.db");        private void initTree()        {            DataSet ds = new DataSet();            string sqlStr = string.Format("select * from TaskScheduler");            ds = SQLiteHelper.SQLiteCommandDataSet(DBConStr, sqlStr, null);            TreeViewUtils.BindTreeVew(ds, treeView1,true,"0");        }

SQLiteHelper类：