private List <string> GetInsertBatchSqlListUnionAll <T>(
string insertText,
IEnumerable <T> collection,
MemberMapper[] members,
int maxBatchSize,
bool withIdentity)
{
var sp = new OracleSqlProvider();
var n = 0;
var sqlList = new List <string>();
var indexValuesWord = insertText.IndexOf(" VALUES (", StringComparison.Ordinal);
var initQuery = insertText.Substring(0, indexValuesWord) + Environment.NewLine;
var valuesQuery = insertText.Substring(indexValuesWord + 9);
var indexEndValuesQuery = valuesQuery.IndexOf(")");
valuesQuery = valuesQuery.Substring(0, indexEndValuesQuery)
.Replace("\r", "")
.Replace("\n", "")
.Replace("\t", "");
// 1 = Number of primary keys generated by sequence
var valuesWihtoutSequence = withIdentity ? valuesQuery.Substring(valuesQuery.IndexOf(",") + 1) : valuesQuery;
var sb = new StringBuilder(initQuery);
sb.Append(" SELECT ");
sb.AppendFormat(valuesQuery, members.Select(m => m.Name).ToArray());
sb.AppendLine(" FROM (");
initQuery = sb.ToString();
sb = new StringBuilder(initQuery);
bool isFirstValues = true;
foreach (var item in collection)
{
if (!isFirstValues)
{
sb.AppendLine(" UNION ALL ");
}
sb.Append("SELECT ");
var values = new List <object>();
foreach (var member in members)
{
var sbItem = new StringBuilder();
var value = member.GetValue(item);
if (value is DateTime?)
{
value = ((DateTime?)value).Value;
}
sp.BuildValue(sbItem, value);
values.Add(sbItem + " " + member.Name);
}
sb.AppendFormat(valuesWihtoutSequence, values.ToArray());
sb.Append(" FROM DUAL");
isFirstValues = false;
n++;
if (n > maxBatchSize)
{
sb.Append(")");
sqlList.Add(sb.ToString());
sb = new StringBuilder(initQuery);
isFirstValues = true;
n = 0;
}
}
if (n > 0)
{
sb.Append(")");
sqlList.Add(sb.ToString());
}
return(sqlList);
}
public override int InsertBatch <T>(
DbManager db,
string insertText,
IEnumerable <T> collection,
MemberMapper[] members,
int maxBatchSize,
DbManager.ParameterProvider <T> getParameters)
{
var sb = new StringBuilder();
var sp = new OracleSqlProvider();
var n = 0;
var cnt = 0;
var str = "\t" + insertText
.Substring(0, insertText.IndexOf(") VALUES ("))
.Substring(7)
.Replace("\r", "")
.Replace("\n", "")
.Replace("\t", " ")
.Replace("( ", "(")
//.Replace(" ", " ")
+ ") VALUES (";
foreach (var item in collection)
{
if (sb.Length == 0)
{
sb.AppendLine("INSERT ALL");
}
sb.Append(str);
foreach (var member in members)
{
var value = member.GetValue(item);
if (value is Nullable <DateTime> )
{
value = ((DateTime?)value).Value;
}
if (value is DateTime)
{
var dt = (DateTime)value;
sb.Append(string.Format("to_timestamp('{0:dd.MM.yyyy HH:mm:ss.ffffff}', 'DD.MM.YYYY HH24:MI:SS.FF6')", dt));
}
else
{
sp.BuildValue(sb, value);
}
sb.Append(", ");
}
sb.Length -= 2;
sb.AppendLine(")");
n++;
if (n >= maxBatchSize)
{
sb.AppendLine("SELECT * FROM dual");
var sql = sb.ToString();
if (DbManager.TraceSwitch.TraceInfo)
{
DbManager.WriteTraceLine("\n" + sql.Replace("\r", ""), DbManager.TraceSwitch.DisplayName);
}
cnt += db.SetCommand(sql).ExecuteNonQuery();
n = 0;
sb.Length = 0;
}
}
if (n > 0)
{
sb.AppendLine("SELECT * FROM dual");
var sql = sb.ToString();
if (DbManager.TraceSwitch.TraceInfo)
{
DbManager.WriteTraceLine("\n" + sql.Replace("\r", ""), DbManager.TraceSwitch.DisplayName);
}
cnt += db.SetCommand(sql).ExecuteNonQuery();
}
return(cnt);
}
private List <string> GetInsertBatchSqlListWithInsertAll <T>(
string insertText,
IEnumerable <T> collection,
MemberMapper[] members,
int maxBatchSize)
{
var sb = new StringBuilder();
var sp = new OracleSqlProvider();
var n = 0;
var sqlList = new List <string>();
foreach (var item in collection)
{
if (sb.Length == 0)
{
sb.AppendLine("INSERT ALL");
}
string strItem = "\t" + insertText
.Replace("INSERT INTO", "INTO")
.Replace("\r", "")
.Replace("\n", "")
.Replace("\t", " ")
.Replace("( ", "(");
var values = new List <object>();
foreach (var member in members)
{
var sbItem = new StringBuilder();
var keyGenerator = member.MapMemberInfo.KeyGenerator as SequenceKeyGenerator;
if (keyGenerator != null)
{
values.Add(NextSequenceQuery(keyGenerator.Sequence));
}
else
{
var value = member.GetValue(item);
if (value is DateTime?)
{
value = ((DateTime?)value).Value;
}
sp.BuildValue(sbItem, value);
values.Add(sbItem.ToString());
}
}
sb.AppendFormat(strItem, values.ToArray());
sb.AppendLine();
n++;
if (n >= maxBatchSize)
{
sb.AppendLine("SELECT * FROM dual");
var sql = sb.ToString();
sqlList.Add(sql);
n = 0;
sb.Length = 0;
}
}
if (n > 0)
{
sb.AppendLine("SELECT * FROM dual");
var sql = sb.ToString();
sqlList.Add(sql);
}
return(sqlList);
}
