int MultipleRowsBulkCopy <T>( DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source, BasicSqlBuilder sqlBuilder, EntityDescriptor descriptor, string tableName) { var iszOS = Version == DB2Version.zOS; { var sb = new StringBuilder(); var buildValue = BasicSqlBuilder.GetBuildValue(sqlBuilder, sb); var columns = descriptor.Columns.Where(c => !c.SkipOnInsert).ToArray(); var pname = sqlBuilder.Convert("p", ConvertType.NameToQueryParameter).ToString(); sb .AppendFormat("INSERT INTO {0}", tableName).AppendLine() .Append("("); foreach (var column in columns) { sb .AppendLine() .Append("\t") .Append(sqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField)) .Append(","); } sb.Length--; sb .AppendLine() .Append(")"); if (!iszOS) { sb .AppendLine() .Append("VALUES"); } var headerLen = sb.Length; var totalCount = 0; var currentCount = 0; var batchSize = options.MaxBatchSize ?? 1000; if (batchSize <= 0) { batchSize = 1000; } var parms = new List <DataParameter>(); var pidx = 0; foreach (var item in source) { sb .AppendLine() .Append(iszOS ? "SELECT " : "("); foreach (var column in columns) { var value = column.GetValue(item); if (value == null) { sb.Append("NULL"); } else { switch (Type.GetTypeCode(value.GetType())) { case TypeCode.DBNull: sb.Append("NULL"); break; case TypeCode.String: var isString = false; switch (column.DataType) { case DataType.NVarChar: case DataType.Char: case DataType.VarChar: case DataType.NChar: case DataType.Undefined: isString = true; break; } if (isString) { goto case TypeCode.Int32; } goto default; case TypeCode.Boolean: case TypeCode.Char: case TypeCode.SByte: case TypeCode.Byte: case TypeCode.Int16: case TypeCode.UInt16: case TypeCode.Int32: case TypeCode.UInt32: case TypeCode.Int64: case TypeCode.UInt64: case TypeCode.Single: case TypeCode.Double: case TypeCode.Decimal: case TypeCode.DateTime: //SetParameter(dataParam, "", column.DataType, value); buildValue(value); break; default: var name = pname + ++pidx; sb.Append(name); parms.Add(new DataParameter("p" + pidx, value, column.DataType)); break; } } sb.Append(","); } sb.Length--; sb.Append(iszOS ? " FROM SYSIBM.SYSDUMMY1 UNION ALL" : "),"); totalCount++; currentCount++; if (currentCount >= batchSize || parms.Count > 100000 || sb.Length > 100000) { if (iszOS) { sb.Length -= " UNION ALL".Length; } else { sb.Length--; } dataConnection.Execute(sb.AppendLine().ToString(), parms.ToArray()); parms.Clear(); pidx = 0; currentCount = 0; sb.Length = headerLen; } } if (currentCount > 0) { if (iszOS) { sb.Length -= " UNION ALL".Length; } else { sb.Length--; } dataConnection.Execute(sb.ToString(), parms.ToArray()); sb.Length = headerLen; } return(totalCount); } }
protected override BulkCopyRowsCopied MultipleRowsCopy <T>( DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source) { var sqlBuilder = _dataProvider.CreateSqlBuilder(); var descriptor = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T)); var tableName = GetTableName(sqlBuilder, descriptor); var rowsCopied = new BulkCopyRowsCopied(); var sb = new StringBuilder(); var buildValue = BasicSqlBuilder.GetBuildValue(sqlBuilder, sb); var columns = descriptor.Columns.Where(c => !c.SkipOnInsert).ToArray(); var pname = sqlBuilder.Convert("p", ConvertType.NameToQueryParameter).ToString(); sb.AppendLine("INSERT ALL"); var headerLen = sb.Length; var currentCount = 0; var batchSize = options.MaxBatchSize ?? 1000; if (batchSize <= 0) { batchSize = 1000; } var parms = new List <DataParameter>(); var pidx = 0; foreach (var item in source) { sb.AppendFormat("\tINTO {0} (", tableName); foreach (var column in columns) { sb .Append(sqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField)) .Append(", "); } sb.Length -= 2; sb.Append(") VALUES ("); foreach (var column in columns) { var value = column.GetValue(item); if (value == null) { sb.Append("NULL"); } else { switch (Type.GetTypeCode(value.GetType())) { case TypeCode.DBNull: sb.Append("NULL"); break; case TypeCode.String: var isString = false; switch (column.DataType) { case DataType.NVarChar: case DataType.Char: case DataType.VarChar: case DataType.NChar: case DataType.Undefined: isString = true; break; } if (isString) { goto case TypeCode.Int32; } goto default; case TypeCode.Boolean: case TypeCode.Char: case TypeCode.SByte: case TypeCode.Byte: case TypeCode.Int16: case TypeCode.UInt16: case TypeCode.Int32: case TypeCode.UInt32: case TypeCode.Int64: case TypeCode.UInt64: case TypeCode.Single: case TypeCode.Double: case TypeCode.Decimal: case TypeCode.DateTime: //SetParameter(dataParam, "", column.DataType, value); buildValue(value); break; default: var name = pname + ++pidx; sb.Append(name); parms.Add(new DataParameter("p" + pidx, value, column.DataType)); break; } } sb.Append(","); } sb.Length--; sb.AppendLine(")"); rowsCopied.RowsCopied++; currentCount++; if (currentCount >= batchSize || parms.Count > 100000 || sb.Length > 100000) { sb.AppendLine("SELECT * FROM dual"); dataConnection.Execute(sb.AppendLine().ToString(), parms.ToArray()); if (options.RowsCopiedCallback != null) { options.RowsCopiedCallback(rowsCopied); if (rowsCopied.Abort) { return(rowsCopied); } } parms.Clear(); pidx = 0; currentCount = 0; sb.Length = headerLen; } } if (currentCount > 0) { sb.AppendLine("SELECT * FROM dual"); dataConnection.Execute(sb.ToString(), parms.ToArray()); sb.Length = headerLen; if (options.RowsCopiedCallback != null) { options.RowsCopiedCallback(rowsCopied); } } return(rowsCopied); }