其他 分部分项 生成 FullPath

生成后的样子

代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    public class Class12
    {


        /*
         
         后面还需要执行的SQL语句
         
        测试SQL语句
        select REPLACE('泸州长江六桥', '长江', '') 
        select REPLACE(FullPath, '泸州长江六桥', '') from Projects
        select REPLACE(FullPath, '/泸州长江六桥/', '') from Projects where Id>510321

        主要是这两句
        update Projects set  FullPath=REPLACE(FullPath, '/泸州长江六桥/', '') where Id>510321
        update Projects set FullPath=REPLACE(FullPath, '/', '') where Id=510321
         
         */



        public void createFullPath()
        {
            var dt = ExecuteDataTable($"select * from Projects order by Id asc", null);
            var list = DataTableToEntities<Projects>(dt);
            var dic = list.ToDictionary(c => c.Id, c => c);

            for (int i = 0; i < list.Count; i++)
            {
                var item = list[i];
                if (item.ParentId == 0)
                {
                    item.FullPath = "/" + item.ProjectName;
                }
                else
                {
                    var item2 = dic[item.ParentId.Value];
                    item.FullPath = item2.FullPath + "/" + item.ProjectName;
                }
                Console.WriteLine(item.FullPath);
            }


            int num = 0;
            StringBuilder sb = new StringBuilder();
            list.ForEach(c =>
            {
                num++;
                Console.WriteLine("num：" + num);
                sb.Append($"update Projects set FullPath='{c.FullPath}' where Id='{c.Id}';");
                if (num % 2000 == 0)
                {
                    ExecuteScalar(sb.ToString());
                    sb.Clear();
                }
            });

            ExecuteScalar(sb.ToString());
            sb.Clear();
        }





        public static string conStr = "data source=192.168.2.51; initial catalog=LZCJLQBimDb;user id=sa;password=Sql123456; ";


        private int ExecuteScalar(string sql)
        {
            if (!sql.EndsWith(";"))
            {
                sql += ";";
            }
            int n = -1;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    n = Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
            return n;
        }




        // 返回DataTable
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, con))
                {
                    //添加参数
                    if (param != null)
                    {
                        adapter.SelectCommand.Parameters.AddRange(param);
                    }
                    adapter.Fill(dt);
                }
            }
            return dt;
        }



        // DataTable转换为Entitys
        public static List<T> DataTableToEntities<T>(DataTable dt) where T : class, new()
        {
            if (null == dt || dt.Rows.Count == 0) { return null; }
            List<T> entities = new List<T>();
            List<string> columnNames = new List<string>();

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                columnNames.Add(dt.Columns[i].ColumnName);
            }

            foreach (DataRow row in dt.Rows)
            {
                PropertyInfo[] pArray = typeof(T).GetProperties();
                T entity = new T();

                Array.ForEach<PropertyInfo>(pArray, p =>
                {
                    if (!columnNames.Contains(p.Name))
                    {
                        return;
                    }

                    object cellvalue = row[p.Name];

                    //空值不处理
                    if (cellvalue == DBNull.Value)
                    {
                        return;
                    }
                    if ((cellvalue == null) || string.IsNullOrWhiteSpace(cellvalue.ToString().Trim()))
                    {
                        return;
                    }

                    if (cellvalue != DBNull.Value)
                    {
                        //经过了几个版本的迭代，最后一个为最新的，摘自网上，已附原文地址

                        //4、原地址：https://blog.csdn.net/Simon1003/article/details/80839744
                        if (!p.PropertyType.IsGenericType)
                        {
                            p.SetValue(entity, Convert.ChangeType(cellvalue, p.PropertyType), null);
                        }
                        else
                        {
                            Type genericTypeDefinition = p.PropertyType.GetGenericTypeDefinition();
                            if (genericTypeDefinition == typeof(Nullable<>))
                            {
                                p.SetValue(entity, Convert.ChangeType(cellvalue, Nullable.GetUnderlyingType(p.PropertyType)), null);
                            }
                            else
                            {
                                throw new Exception("genericTypeDefinition != typeof(Nullable<>)");
                            }
                        }
                    }
                });
                entities.Add(entity);
            }
            return entities;
        }


        public class Projects
        {
            public int Id { get; set; }
            public int? ParentId { get; set; }
            public string ProjectName { get; set; }
            public string ProjectCode { get; set; }


            public string FullPath { get; set; }
        }

    }
}