MySql 批量提交方法

自己写的方法,没有经过很多校验

 

 

        #region MySql批量提交

        /// <summary>
        /// MySql批量提交
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strTableName"></param>
        /// <param name="lstData"></param>
        /// <returns></returns>
        public int SqlBulkToMySQl<T>(string TbName, string PrintKey, List<T> lstData)
        {

            int RtnExe = 0;

            string Sql = string.Empty;
            try
            {

                List<T> lstDtSel = new List<T>();

                foreach (var item in lstData)
                {
                    lstDtSel.Add(item);
                    if (lstDtSel.Count > 2000)
                    {
                        Sql = MySqlEceSql<T>(TbName, PrintKey, lstData);
                        if (Sql.Length > 0)
                        {
                            RtnExe = RtnExe + _db.Execute(Sql);
                            lstDtSel = new List<T>();
                            Sql = string.Empty;
                        }
                    }

                }


                if (lstDtSel.Count > 0)
                {
                    Sql = DataConvert.MySqlEceSql<T>(TbName, PrintKey, lstData);
                    RtnExe = RtnExe + _db.Execute(Sql);
                    lstDtSel = new List<T>();
                    Sql = string.Empty;
                }
            }
            catch (Exception)
            {

                throw;
            }

            return RtnExe;
        }




        /// <summary>
        /// 批量生成可以执行的MySql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="TbName"></param>
        /// <param name="PrintKey"></param>
        /// <param name="lstData"></param>
        /// <returns></returns>
        public static string MySqlEceSql<T>(string TbName, string PrintKey, List<T> 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<PropertyInfo> lst = new List<PropertyInfo>();
                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

  

  

MySql 批量提交方法

上一篇:SQL(2)—— coalesce函数


下一篇:精美的时尚标志作品