近期工作中发现批量插入的方法需求越来越大。所以在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;
}