首页 > 代码库 > 增加批量插入方法

增加批量插入方法

  近期工作中发现批量插入的方法需求越来越大。所以在ORM中增加了MYSQL的批量插入方法。由三个方法组成,可以使用在不同情况下。

  1、根据传入的实体集合生成批量插入的SQL语名 GetInsertSqlBatch()

  2、在方法1的基础上增加一个执行并返回是否成功的功能 ExecuteInsertModelBatch()。

  3、上面两个方法都没有控制每次批量插入的最大数量。只适用于小量批量插入情况。如果实体集合一次性传入1万,10万也做一次提交的话好像不太合适吧。所以就有了第三个方法。第三个方法是第二个方法的重载,增加了<param name="batchNum">每个批量插入的数量</param>参数。用于控制每个批次最大插入数量。转入0时使用默认值为100。当插入实体集合大于100或指定的值时,将根据设定的batchNum值的来分批提交。

下面是具体代码的实现。

 

/// <summary>        /// 获取批量插入SQL        /// </summary>        /// <param name="entitys">插入实体集合</param>        /// <param name="sql">输出SQL</param>        /// <param name="tableName">表名</param>        /// <param name="excludeProperties">过滤属性名称列表</param>        /// <returns>SQL参数集合</returns>        public static List<MySqlParameter> GetInsertSqlBatch<T>(List<T> entitys, out string sql, string tableName, params string[] excludeProperties)        {            List<MySqlParameter> sqlParameters = new List<MySqlParameter>();            sql = string.Empty;            if (entitys != null && entitys.Count > 0)            {                var type = entitys.First().GetType();                tableName = GetTableName(type, tableName);                //INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)                 //    VALUES (‘0‘, ‘userid_0‘, ‘content_0‘, 0),                 //           (‘1‘, ‘userid_1‘, ‘content_1‘, 1);                if (!string.IsNullOrEmpty(tableName))                {                    StringBuilder InsertSql = new StringBuilder(string.Format("Insert into {0}(", tableName.ToLower()));                    StringBuilder ValuesSql = new StringBuilder(" Values(");                    var propertieList = GetProperties(type);                    var entityItemIndex = 1;                    try                    {                        //循环要插入的实体集合                        foreach (var entityItem in entitys)                        {                            //循环每个实体的属性集合                            var propertieIndex = 0;                            foreach (var propertieItem in propertieList.Values)                            {                                //是否添加属性                                if (ValidateIsAddPropertie(type, propertieItem, OperationType.Insert, paramsArrayToDictionary(excludeProperties)))                                {                                    if (entityItemIndex == 1)                                    {                                        InsertSql.AppendFormat("{0},", propertieItem.Name);                                    }                                    var parameterName = string.Format("@{0}{1}", propertieItem.Name, entityItemIndex);                                    if (entityItemIndex > 1 && propertieIndex == 0)                                    {                                        ValuesSql.Append("(");                                    }                                    ValuesSql.AppendFormat("{0},", parameterName);                                    var value = http://www.mamicode.com/propertieItem.GetValue(entityItem);")");                                    }                                    ValuesSql.Remove(ValuesSql.Length - 1, 1);                                    ValuesSql.Append("),");                                }                                propertieIndex++;                            }                            entityItemIndex++;                        }                    }                    catch (Exception ex)                    {                        if (sqlParameters != null && sqlParameters.Count > 0)                        {                            sqlParameters.Clear();                            sqlParameters = null;                        }                        throw ex;                    }                    sql = string.Format("{0};", InsertSql.Append(ValuesSql).ToString().Trim(‘,‘));                    InsertSql.Clear();                    ValuesSql.Clear();                }            }            return sqlParameters;        }        /// <summary>        /// 执行批量插入实体方法        /// </summary>        /// <param name="entitys">插入实体集合</param>        /// <param name="dataBaseName">数据名称</param>        /// <param name="tableName">表名</param>        /// <param name="excludeProperties">过滤属性名称列表</param>        /// <returns>SQL参数集合</returns>        public static bool ExecuteInsertModelBatch<T>(List<T> entitys, string dataBaseName, string tableName = "", params string[] excludeProperties)        {            bool isSucceed = false;            List<MySqlParameter> sqlParameters = new List<MySqlParameter>();            string sql = string.Empty;            sqlParameters = GetInsertSqlBatch(entitys, out sql, tableName, excludeProperties);            if (sql.Length > 0 && sqlParameters != null && sqlParameters.Count > 0)            {                try                {                    isSucceed = CBDMySqlHelper.ExecuteNonQuery(dataBaseName, sql, sqlParameters.ToArray()) == entitys.Count;                }                catch (Exception ex)                {                    throw ex;                }                finally                {                    if (sqlParameters != null && sqlParameters.Count > 0)                    {                        sqlParameters.Clear();                        sqlParameters = null;                    }                    if (entitys != null && entitys.Count > 0)                    {                        entitys.Clear();                        entitys = null;                    }                }            }            return isSucceed;        }        /// <summary>        /// 执行批量插入实体方法        /// </summary>        /// <param name="entitys">插入实体集合</param>        /// <param name="batchNum">分批处理数量[<=0时默认值为100]</param>        /// <param name="batchNum">每个批量插入的数量</param>        /// <param name="dataBaseName">数据名称</param>        /// <param name="tableName">表名</param>        /// <param name="excludeProperties">过滤属性名称列表</param>        /// <returns>SQL参数集合</returns>        public static bool ExecuteInsertModelBatch<T>(List<T> entitys, int batchNum, string dataBaseName, string tableName = "", params string[] excludeProperties)        {            bool isSucceed = false;            var insertCount = 0;            List<MySqlParameter> sqlParameters = new List<MySqlParameter>();            if (entitys != null && entitys.Count > 0)            {                //如果分批处理数量为0时,则使用默认值100                if (batchNum <= 0)                {                    batchNum = 100;                }                var type = entitys.First().GetType();                tableName = GetTableName(type, tableName);                //INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)                 //    VALUES (‘0‘, ‘userid_0‘, ‘content_0‘, 0),                 //           (‘1‘, ‘userid_1‘, ‘content_1‘, 1);                if (!string.IsNullOrEmpty(tableName))                {                    StringBuilder InsertSql = new StringBuilder(string.Format("Insert into {0}(", tableName.ToLower()));                    StringBuilder ValuesSql = new StringBuilder(" Values(");                    var propertieList = GetProperties(type);                    var entityItemIndex = 1;                    try                    {                        //循环要插入的实体集合                        foreach (var entityItem in entitys)                        {                            //循环每个实体的属性集合                            var propertieIndex = 0;                            foreach (var propertieItem in propertieList.Values)                            {                                //是否添加属性                                if (ValidateIsAddPropertie(type, propertieItem, OperationType.Insert, paramsArrayToDictionary(excludeProperties)))                                {                                    if (entityItemIndex == 1)                                    {                                        InsertSql.AppendFormat("{0},", propertieItem.Name);                                    }                                    var parameterName = string.Format("@{0}{1}", propertieItem.Name, entityItemIndex);                                    if (entityItemIndex > 1 && propertieIndex == 0)                                    {                                        ValuesSql.Append("(");                                    }                                    ValuesSql.AppendFormat("{0},", parameterName);                                    var value = http://www.mamicode.com/propertieItem.GetValue(entityItem);")");                                    }                                    ValuesSql.Remove(ValuesSql.Length - 1, 1);                                    ValuesSql.Append("),");                                }                                propertieIndex++;                            }                            if ((entityItemIndex % batchNum) == 0 || entityItemIndex == entitys.Count)                            {                                var sql = string.Format("{0}{1}", InsertSql, ValuesSql).Trim(‘,‘);                                insertCount += CBDMySqlHelper.ExecuteNonQuery(dataBaseName, sql, sqlParameters.ToArray());                                ValuesSql = new StringBuilder(" Values");                                sqlParameters.Clear();                            }                            entityItemIndex++;                        }                        if (insertCount == entitys.Count)                        {                            isSucceed = true;                        }                    }                    catch (Exception ex)                    {                        InsertSql.Clear();                        ValuesSql.Clear();                        if (sqlParameters != null && sqlParameters.Count > 0)                        {                            sqlParameters.Clear();                            sqlParameters = null;                        }                        throw ex;                    }                    finally                    {                        if (entitys != null && entitys.Count > 0)                        {                            entitys.Clear();                            entitys = null;                        }                    }                }            }            return isSucceed;        }

  

增加批量插入方法