string createRowStatement(int rowNo, List <string> keys, Vars sv, string tableName, bool firstInBatch, SqlUpdateMode updateMode, DatabaseType dbType)
{
StringBuilder sbInsert = new StringBuilder();
if (updateMode == SqlUpdateMode.Merge)
{
appendSelect(sbInsert, rowNo, keys, sv, firstInBatch, dbType);
return(sbInsert.ToString());
}
if (InsertSyntax == SqlInsertSyntax.Select)
{
if (firstInBatch || UpdateMode != SqlUpdateMode.None)
{
sbInsert.Append("INSERT INTO ");
sbInsert.Append(tableName);
sbInsert.Append(" ( ");
appendColumns(sbInsert, keys, dbType);
sbInsert.Append(" )");
sbInsert.AppendLine();
}
appendSelect(sbInsert, rowNo, keys, sv, firstInBatch || UpdateMode != SqlUpdateMode.None, dbType);
}
else
{
sbInsert.Append("INSERT INTO ");
sbInsert.Append(tableName);
sbInsert.Append(" ( ");
appendColumns(sbInsert, sv.Keys, dbType);
sbInsert.AppendLine(" )");
sbInsert.Append("VALUES (");
bool ff = true;
foreach (var v in sv.Values)
{
if (!ff)
{
sbInsert.Append(",");
}
ff = false;
sbInsert.Append(SqlUtil.GetQuotedValue(dbType, v, NoQuotePrefix, Translate));
}
sbInsert.AppendLine(")");
}
if (updateMode == SqlUpdateMode.None)
{
return(sbInsert.ToString());
}
var updateKeys = (Context.TransformStr(Keys, Transform) ?? string.Empty);
if (string.IsNullOrEmpty(updateKeys))
{
throw new ParsingException("Key columns must be specified in order to enable updates");
}
string[] kfield = updateKeys.Split(';', ',');
StringBuilder sList = new StringBuilder();
StringBuilder sCond = new StringBuilder();
StringBuilder sUpd = new StringBuilder();
Dictionary <string, bool> k = new Dictionary <string, bool>(StringComparer.InvariantCultureIgnoreCase);
for (int i = 0; i < kfield.Length; ++i)
{
string kf = kfield[i].Trim();
kfield[i] = kf;
if (sList.Length != 0)
{
sList.Append(", ");
}
sList.Append(SqlUtil.SqlName(dbType, kf));
if (sCond.Length != 0)
{
sCond.Append(" AND ");
}
sCond.Append("(");
sCond.Append(SqlUtil.SqlName(dbType, kf));
sCond.Append(" = ");
sCond.Append(SqlUtil.GetQuotedValue(dbType, sv[kf], NoQuotePrefix, Translate));
sCond.Append(")");
k[kf] = true;
}
// Prepare update statement
sUpd.AppendFormat("UPDATE " + tableName);
sUpd.AppendLine();
sUpd.Append("SET ");
bool first = true;
foreach (var v in sv)
{
if (!k.ContainsKey(v.Name))
{
if (!first)
{
sUpd.Append(", ");
}
first = false;
sUpd.Append(SqlUtil.SqlName(dbType, v.Name));
sUpd.Append("=");
sUpd.Append(SqlUtil.GetQuotedValue(dbType, v.Value, NoQuotePrefix, Translate));
}
}
sUpd.AppendLine();
var kfieldCond = sCond.ToString();
sUpd.AppendLine("WHERE " + kfieldCond);
StringBuilder sb = new StringBuilder();
if (updateMode == SqlUpdateMode.MsSql)
{
sb.Append(sUpd.ToString());
sb.AppendLine("IF @@ROWCOUNT=0");
}
else
{
sb.AppendFormat("IF EXISTS(SELECT {0} FROM {1} WHERE {2})", sList.ToString(), tableName, kfieldCond);
sb.AppendLine();
sb.Append(sUpd.ToString());
sb.AppendLine("ELSE");
}
sb.AppendLine(sbInsert.ToString());
sb.AppendLine();
return(sb.ToString());
}
private void prepareHeaderAndFooter(StringBuilder batch, StringBuilder footer, string tableName, List <string> keys, SqlUpdateMode updateMode, DatabaseType dbType)
{
if (IdentityInsert)
{
batch.Append("SET IDENTITY_INSERT " + tableName + " ON\n");
footer.AppendLine("SET IDENTITY_INSERT " + tableName + " OFF\n");
}
if (updateMode == SqlUpdateMode.Merge)
{
string[] sqlKeys = new string[keys.Count];
for (int i = 0; i < keys.Count; ++i)
{
sqlKeys[i] = SqlUtil.SqlName(dbType, keys[i]);
}
batch.AppendLine("MERGE " + tableName + " AS DST");
batch.AppendLine("USING (");
footer.AppendLine(") AS SRC(" + string.Join(", ", sqlKeys) + ")");
footer.Append(" ON ");
var updateKeys = (Context.TransformStr(Keys, Transform) ?? string.Empty);
if (string.IsNullOrEmpty(updateKeys))
{
throw new ParsingException("Key columns must be specified in order to enable updates");
}
string[] kfield = updateKeys.Split(';', ',');
for (int i = 0; i < kfield.Length; ++i)
{
kfield[i] = SqlUtil.SqlName(dbType, kfield[i].Trim());
}
Dictionary <string, bool> kk = new Dictionary <string, bool>(StringComparer.InvariantCultureIgnoreCase);
for (int i = 0; i < kfield.Length; ++i)
{
if (i != 0)
{
footer.Append(" AND ");
}
footer.AppendFormat("DST.{0}=SRC.{0}", kfield[i]);
kk[kfield[i]] = true;
}
footer.AppendLine();
footer.AppendLine("WHEN MATCHED THEN");
footer.Append(" UPDATE SET ");
bool ff = true;
for (int i = 0; i < sqlKeys.Length; ++i)
{
if (kk.ContainsKey(sqlKeys[i]))
{
continue;
}
if (!ff)
{
footer.Append(",");
}
ff = false;
footer.AppendFormat("{0} = SRC.{0}", sqlKeys[i]);
}
footer.AppendLine();
footer.AppendLine("WHEN NOT MATCHED THEN");
footer.Append(" INSERT (");
for (int i = 0; i < sqlKeys.Length; ++i)
{
if (i != 0)
{
footer.Append(",");
}
footer.Append(sqlKeys[i]);
}
footer.AppendLine(" )");
footer.Append(" VALUES (");
for (int i = 0; i < sqlKeys.Length; ++i)
{
if (i != 0)
{
footer.Append(",");
}
footer.Append("SRC." + sqlKeys[i]);
}
footer.AppendLine(");");
}
}
private void prepareHeaderAndFooter(StringBuilder batch, StringBuilder footer, string tableName, List<string> keys, SqlUpdateMode updateMode, DatabaseType dbType)
{
if (IdentityInsert)
{
batch.Append("SET IDENTITY_INSERT " + tableName + " ON\n");
footer.AppendLine("SET IDENTITY_INSERT " + tableName + " OFF\n");
}
if (updateMode==SqlUpdateMode.Merge)
{
string[] sqlKeys = new string[keys.Count];
for (int i = 0; i < keys.Count; ++i)
sqlKeys[i] = SqlUtil.SqlName(dbType, keys[i]);
batch.AppendLine("MERGE " + tableName + " AS DST");
batch.AppendLine("USING (");
footer.AppendLine(") AS SRC("+string.Join(", ",sqlKeys)+")");
footer.Append(" ON ");
var updateKeys = (Context.TransformStr(Keys, Transform) ?? string.Empty);
if (string.IsNullOrEmpty(updateKeys))
throw new ParsingException("Key columns must be specified in order to enable updates");
string[] kfield = updateKeys.Split(';', ',');
for (int i = 0; i < kfield.Length; ++i)
kfield[i] = SqlUtil.SqlName(dbType, kfield[i].Trim());
Dictionary<string,bool> kk=new Dictionary<string, bool>(StringComparer.InvariantCultureIgnoreCase);
for (int i = 0; i < kfield.Length; ++i)
{
if (i != 0)
footer.Append(" AND ");
footer.AppendFormat("DST.{0}=SRC.{0}", kfield[i]);
kk[kfield[i]] = true;
}
footer.AppendLine();
footer.AppendLine("WHEN MATCHED THEN");
footer.Append(" UPDATE SET ");
bool ff = true;
for (int i = 0; i < sqlKeys.Length; ++i)
{
if (kk.ContainsKey(sqlKeys[i]))
continue;
if (!ff)
footer.Append(",");
ff = false;
footer.AppendFormat("{0} = SRC.{0}",sqlKeys[i]);
}
footer.AppendLine();
footer.AppendLine("WHEN NOT MATCHED THEN");
footer.Append(" INSERT (");
for (int i = 0; i < sqlKeys.Length; ++i)
{
if (i != 0)
footer.Append(",");
footer.Append(sqlKeys[i]);
}
footer.AppendLine(" )");
footer.Append(" VALUES (");
for (int i = 0; i < sqlKeys.Length; ++i)
{
if (i != 0)
footer.Append(",");
footer.Append("SRC."+sqlKeys[i]);
}
footer.AppendLine(");");
}
}
/// Execute action
public override object Execute()
{
object o = base.Execute();
if (o != null)
{
return(0);
}
if (Database.Current == null)
{
throw new ScriptRuntimeException("Database is not specified");
}
var dbType = Database.Current.SpecificDatabaseType;
string tableName = SqlUtil.SqlName(dbType, Context.TransformStr(Table, Transform));
string firstHeader = string.Empty;
if (Truncate)
{
firstHeader = "TRUNCATE TABLE " + tableName + Environment.NewLine;
}
SqlUpdateMode upMode = UpdateMode;
if (upMode == SqlUpdateMode.Auto)
{
if (dbType == DatabaseType.MsSql2008)
{
upMode = SqlUpdateMode.Merge;
}
else if ((dbType & DatabaseType.MsSql) == DatabaseType.MsSql)
{
upMode = SqlUpdateMode.MsSql;
}
else
{
upMode = SqlUpdateMode.Simple;
}
}
StringBuilder sbFooter = new StringBuilder();
StringBuilder batch = new StringBuilder();
List <string> keys = new List <string>();
int rowsInBatch = 0;
int prevColsCount = -1;
int maxBatch = MaxBatch;
if (maxBatch < 1)
{
maxBatch = 1;
}
if ((dbType & DatabaseType.MsSql) != DatabaseType.MsSql && InsertSyntax == SqlInsertSyntax.Values)
{
maxBatch = 1;
}
int rowNo = 0;
StringBuilder sbO = new StringBuilder();
foreach (Vars sv in GetData())
{
Context.CheckAbort();
flush:
if (sv.Count != prevColsCount && batch.Length != 0)
{
if (string.IsNullOrEmpty(SqlTo))
{
runCommand(firstHeader + batch + sbFooter);
}
else
{
sbO.AppendLine(firstHeader + batch + sbFooter);
}
batch.Length = 0;
firstHeader = string.Empty;
rowsInBatch = 0;
}
if (rowsInBatch == 0)
{
keys.Clear();
foreach (Var c in sv)
{
keys.Add(c.Name);
}
prevColsCount = sv.Count;
sbFooter.Length = 0;
prepareHeaderAndFooter(batch, sbFooter, tableName, keys, upMode, dbType);
}
string cr = createRowStatement(rowNo, keys, sv, tableName, rowsInBatch == 0, upMode, dbType);
if (rowsInBatch >= 1 && (cr.Length + firstHeader.Length + batch.Length + sbFooter.Length > MaxLength || rowsInBatch >= maxBatch))
{
// If we append this command to the batch, it will be too long.
prevColsCount = -1;
goto flush;
}
batch.Append(cr);
rowsInBatch++;
}
if (batch.Length != 0)
{
if (string.IsNullOrEmpty(SqlTo))
{
runCommand(firstHeader + batch + sbFooter);
}
else
{
sbO.AppendLine(firstHeader + batch + sbFooter);
}
}
if (!string.IsNullOrEmpty(SqlTo))
{
Context.OutTo(Context.TransformStr(SqlTo, Transform), sbO.ToString());
}
return(null);
}
string createRowStatement(int rowNo,List<string> keys, Vars sv, string tableName, bool firstInBatch, SqlUpdateMode updateMode, DatabaseType dbType)
{
StringBuilder sbInsert = new StringBuilder();
if (updateMode == SqlUpdateMode.Merge)
{
appendSelect(sbInsert, rowNo, keys, sv, firstInBatch, dbType);
return sbInsert.ToString();
}
if (InsertSyntax == SqlInsertSyntax.Select)
{
if (firstInBatch || UpdateMode != SqlUpdateMode.None)
{
sbInsert.Append("INSERT INTO ");
sbInsert.Append(tableName);
sbInsert.Append(" ( ");
appendColumns(sbInsert, keys, dbType);
sbInsert.Append(" )");
sbInsert.AppendLine();
}
appendSelect(sbInsert, rowNo, keys, sv, firstInBatch || UpdateMode != SqlUpdateMode.None, dbType);
}
else
{
sbInsert.Append("INSERT INTO ");
sbInsert.Append(tableName);
sbInsert.Append(" ( ");
appendColumns(sbInsert, sv.Keys, dbType);
sbInsert.AppendLine(" )");
sbInsert.Append("VALUES (");
bool ff = true;
foreach (var v in sv.Values)
{
if (!ff)
sbInsert.Append(",");
ff = false;
sbInsert.Append(SqlUtil.GetQuotedValue(dbType, v, NoQuotePrefix, Translate));
}
sbInsert.AppendLine(")");
}
if (updateMode == SqlUpdateMode.None)
return sbInsert.ToString();
var updateKeys = (Context.TransformStr(Keys, Transform) ?? string.Empty);
if (string.IsNullOrEmpty(updateKeys))
throw new ParsingException("Key columns must be specified in order to enable updates");
string[] kfield = updateKeys.Split(';', ',');
StringBuilder sList = new StringBuilder();
StringBuilder sCond = new StringBuilder();
StringBuilder sUpd = new StringBuilder();
Dictionary<string,bool> k=new Dictionary<string, bool>(StringComparer.InvariantCultureIgnoreCase);
for (int i=0;i<kfield.Length;++i)
{
string kf = kfield[i].Trim();
kfield[i] = kf;
if (sList.Length!=0)
sList.Append(", ");
sList.Append(SqlUtil.SqlName(dbType, kf));
if (sCond.Length!=0)
sCond.Append(" AND ");
sCond.Append("(");
sCond.Append(SqlUtil.SqlName(dbType, kf));
sCond.Append(" = ");
sCond.Append(SqlUtil.GetQuotedValue(dbType, sv[kf], NoQuotePrefix, Translate));
sCond.Append(")");
k[kf] = true;
}
// Prepare update statement
sUpd.AppendFormat("UPDATE " + tableName);
sUpd.AppendLine();
sUpd.Append("SET ");
bool first = true;
foreach (var v in sv)
if (!k.ContainsKey(v.Name))
{
if (!first)
sUpd.Append(", ");
first = false;
sUpd.Append(SqlUtil.SqlName(dbType, v.Name));
sUpd.Append("=");
sUpd.Append(SqlUtil.GetQuotedValue(dbType, v.Value, NoQuotePrefix, Translate));
}
sUpd.AppendLine();
var kfieldCond = sCond.ToString();
sUpd.AppendLine("WHERE "+kfieldCond);
StringBuilder sb=new StringBuilder();
if (updateMode == SqlUpdateMode.MsSql)
{
sb.Append(sUpd.ToString());
sb.AppendLine("IF @@ROWCOUNT=0");
}
else
{
sb.AppendFormat("IF EXISTS(SELECT {0} FROM {1} WHERE {2})", sList.ToString(), tableName, kfieldCond);
sb.AppendLine();
sb.Append(sUpd.ToString());
sb.AppendLine("ELSE");
}
sb.AppendLine(sbInsert.ToString());
sb.AppendLine();
return sb.ToString();
}
