MySql 批量提交方法

        #region MySql批量提交

        /// 

        /// MySql批量提交
        /// 
        /// 
        /// 表名
        /// 如果主键不是自增长ID，则可以输入空字符串
        /// 需要插入表格内容
        /// 
        public int SqlBulkToMySQl(string TbName, string PrintKey, List lstData)
        {

            int RtnExe = 0;

            string Sql = string.Empty;
            try
            {

                List lstDtSel = new List();

                foreach (var item in lstData)
                {
                    lstDtSel.Add(item);
                    if (lstDtSel.Count > 1500)
                    {
                        Sql = MySqlEceSql(TbName, PrintKey, lstData);
                        if (Sql.Length > 0)
                        {
                            int Add= _db.Execute(Sql);
                            RtnExe = RtnExe + Add;

                            _log.Error($"表名：{TbName}新增条数：{Add}");
                            lstDtSel = new List();
                            Sql = string.Empty;
                        }
                    }

                }


                if (lstDtSel.Count > 0)
                {
                    Sql = DataConvert.MySqlEceSql(TbName, PrintKey, lstData);
                    int Add = _db.Execute(Sql);
                    RtnExe = RtnExe + Add; 
                    _log.Error($"表名：{TbName}新增条数：{Add}");
                    lstDtSel = new List();
                    Sql = string.Empty;
                }
            }
            catch (Exception)
            {

                throw;
            }

            return RtnExe;
        }




        /// 

        /// 批量生成可以执行的MySql语句
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        public static string MySqlEceSql(string TbName, string PrintKey, List lstData)
        {

            string Sql = string.Empty;
            try
            {

                if (lstData.Count < 1) return Sql;
                T s = lstData[0];
                PropertyInfo[] pps = GetPropertyInfos(s.GetType());
                Sql = Sql + string.Format($" INSERT INTO {TbName}  (  ");

                //剔除主键
                List lst = new List();
                foreach (var item in pps)
                {
                    if (item.Name.ToUpper().Equals(PrintKey.ToUpper()))
                    {
                        continue;
                    }
                    else
                    {
                        lst.Add(item);
                    }
                }

                foreach (var item in lst)
                {
                    Sql = Sql + string.Format($" {item.Name},");
                }
                Sql = Sql.Substring(0, Sql.Length - 1);
                Sql = Sql + string.Format($"  ) values ");




                foreach (var item in lstData)
                {
                    Sql = Sql + string.Format($" (  ");
                    foreach (var itemP in lst)
                    {


                        var value = item.GetType().GetProperty(itemP.Name).GetValue(item, null);

                        if (itemP.PropertyType.FullName.Contains("System.DateTime"))
                        {
                            DateTime.TryParse(value.ToString(), out  DateTime ODateTime);
                            if (ODateTime==null|| ODateTime.Year<2000)
                            {
                                value = null;
                            }
                            else
                            {
                                value = ODateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
                            }

                           

                        }

                        Sql = Sql + ($" '{value}',");
                    }
                    Sql = Sql.Substring(0, Sql.Length - 1);
                    Sql = Sql + string.Format($"  ) ,");

                }


                Sql = Sql.Substring(0, Sql.Length - 1);


            }
            catch (Exception)
            {

            }

            return Sql;
        }
        public static PropertyInfo[] GetPropertyInfos(Type type)
        {
            return type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
        }


        #endregion