增加批量插入方法

　　近期工作中发现批量插入的方法需求越来越大。所以在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 = propertieItem.GetValue(entityItem);
                                    sqlParameters.Add(new MySqlParameter(parameterName, value ?? string.Empty));
                                }
                                if (propertieIndex == propertieList.Count - 1)
                                {
                                    if (entityItemIndex == 1)
                                    {
                                        InsertSql.Remove(InsertSql.Length - 1, 1);
                                        InsertSql.Append(")");
                                    }
                                    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 = propertieItem.GetValue(entityItem);
                                    sqlParameters.Add(new MySqlParameter(parameterName, value ?? string.Empty));
                                }
                                if (propertieIndex == propertieList.Count - 1)
                                {
                                    if (entityItemIndex == 1)
                                    {
                                        InsertSql.Remove(InsertSql.Length - 1, 1);
                                        InsertSql.Append(")");
                                    }
                                    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;
        }