增加批量插入方法

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

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

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

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

下面是具体代码的实现。

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

/// <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;
        }