private void WriteInstance(SqlDumpWriter sdw, DataSetInstance inst)
{
int idVariable = ReserveVariable();
if (inst.Class.ConditionalInsertFields != null)
{
var cls = inst.Class;
var sb = new StringBuilder();
sb.Append("(");
sb.AppendFormat("select top(1) tmain.[{0}] from [{1}] tmain ", cls.SimplePkCol, cls.TableName);
var exprs = new List<string>();
var rewr = new SqlRewriter(cls.TableName, this);
for (int i = 0; i < cls.ConditionalInsertFields.Length; i++) exprs.Add(rewr.Rewrite(cls.ConditionalInsertFields[i]));
rewr.WriteJoins(sb);
sb.Append(" where ");
bool wasCond = false;
for (int i = 0; i < exprs.Count; i++)
{
if (wasCond) sb.Append(" and ");
sb.AppendFormat("{0}=N'{1}'", exprs[i], inst.Values[cls.ConditionalInsertFieldIndexes[i]]);
wasCond = true;
}
sb.Append(")");
inst.IdVariable = AssignVariable(sdw, sb.ToString(), idVariable);
sdw.EndCommand();
sdw.Write("if ({0} is null) begin\n", sdw.GetVarExpr(inst.IdVariable.Value));
}
sdw.Write("insert into [{0}] (", inst.Class.TableName);
WriteColumnList(inst.Class, sdw.CurrentCommandBuilder);
sdw.Write(") values (");
bool was = false;
for (int i = 0; i < inst.Class.Columns.Length; i++)
{
if (inst.Class.Columns[i] == inst.Class.IdentityColumn) continue;
if (inst.Class.TargetTable.Columns[i].ComputedExpression != null) continue;
if (was) sdw.Write(",");
FormulaDefinition formula = null;
if (inst.Class.ChangedColumns.ContainsKey(inst.Class.Columns[i])) formula = inst.Class.ChangedColumns[inst.Class.Columns[i]];
if (formula != null) formula.WritePrefix(sdw.CurrentCommandBuilder);
var r = inst.Class.GetReference(i);
//var fr = inst.Class.GetFulltextReference(i);
//if (fr != null)
//{
// sdw.Write(fr.EmptyValueExpression);
//}
//else
if (r != null && !r.ReferencedClass.KeepKey)
{
if (r.ReferencedClass.LookupDefined)
{
if (inst.Values[i] == null || inst.Values[i] == DBNull.Value)
{
sdw.Write("NULL");
}
else
{
string refid = inst.Values[i].ToString();
if (r.ReferencedClass.LookupVariables.ContainsKey(refid))
{
sdw.WriteVar(r.ReferencedClass.LookupVariables[refid]);
}
else
{
sdw.Write("NULL");
}
}
}
else
{
if (!KeepUndefinedReferences)
{
if (r.ReferencedClass.IdentityColumn == null)
{
int ord = r.BaseClass.ColumnOrdinals[r.BindingColumn];
if (r.BaseClass.AllInstances.Select(x => x.Values[ord]).Any(x => x != null))
{
throw new Exception(String.Format("DBSH-00125 Lookup or target identity must be defined on table {0} because of reference from {1}", r.ReferencedClass.TableName,
r.BaseClass.TableName));
}
}
}
if (r.BackReference)
{
sdw.Write("NULL");
}
else
{
if (inst.Values[i] == null || inst.Values[i] == DBNull.Value)
{
sdw.Write("NULL");
}
else
{
int? rvar = GetReferenceVariable(inst.Values[i].ToString(), r.BaseClass, r.ReferencedClass, r.BindingColumn);
if (rvar == null)
{
if (KeepUndefinedReferences)
{
WriteValue(sdw.CurrentCommandBuilder, inst.Values[i]);
}
else
{
sdw.Write("NULL");
}
}
else
{
sdw.WriteVar(rvar.Value);
}
}
}
}
}
else
{
//object overr;
//if (inst.Class.Overrides.TryGetValue(inst.Class.Columns[i], out overr))
//{
// if (overr is RawOverrides)
// sdw.CurrentCommandBuilder.Append(((RawOverrides)overr).RawValue);
// else
// WriteValue(sdw.CurrentCommandBuilder, overr);
//}
//else
//{
// WriteValue(sdw.CurrentCommandBuilder, inst.Values[i]);
//}
WriteValue(sdw.CurrentCommandBuilder, inst.Values[i]);
}
if (formula != null) formula.WritePostfix(sdw.CurrentCommandBuilder);
was = true;
}
sdw.Write(")");
if (inst.RequiredIdentity || inst.Class.ConditionalInsertFields != null)
{
if (inst.Class.IdentityColumn != null)
{
inst.IdVariable = AssignVariable(sdw, "SCOPE_IDENTITY()", idVariable, true);
}
else
{
if (inst.Class.SimplePkCol != null)
{
inst.IdVariable = AssignVariable(sdw, inst.Values[inst.Class.SimplePkColIndex].ToString(), idVariable, true);
}
}
}
if (inst.Class.ConditionalInsertFields != null)
{
sdw.Write("end\n");
}
sdw.EndCommand();
}
private void DoAddRows(DbConnection conn, NameWithSchema table, string condition)
{
using (var cmd = conn.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("SELECT ");
var cls = GetClass(table);
sb.Append(cls.Columns.Select(x => "tmain.[" + x + "]").CreateDelimitedText(", "));
sb.Append($" FROM [{table.Schema}].[{table.Name}] tmain");
if (!String.IsNullOrEmpty(condition))
{
var rewr = new SqlRewriter(table, this);
string outcond = rewr.Rewrite(condition);
rewr.WriteJoins(sb);
sb.Append(" WHERE " + outcond);
}
cmd.CommandText = sb.ToString();
try
{
using (ICdlReader reader = _dda.AdaptReader(cmd.ExecuteReader()))
{
DoLoadRows(reader, table);
}
}
catch (Exception err)
{
throw new Exception(String.Format("Error loading, table={0}, condition={1}, error={2}", table, condition, err.Message));
}
}
}
private void WriteLookup(SqlDumpWriter sdw, DataSetClass cls)
{
foreach (var tuple in cls.LookupValues)
{
var sb = new StringBuilder();
sb.Append("(");
sb.AppendFormat("select top(1) tmain.[{0}] from [{1}] tmain ", cls.SimplePkCol, cls.TableName);
var exprs = new List<string>();
var rewr = new SqlRewriter(cls.TableName, this);
for (int i = 0; i < cls.LookupFields.Length; i++) exprs.Add(rewr.Rewrite(cls.LookupFields[i]));
rewr.WriteJoins(sb);
sb.Append(" where ");
bool was = false;
for (int i = 0; i < exprs.Count; i++)
{
if (was) sb.Append(" and ");
sb.AppendFormat("{0}=N'{1}'", exprs[i], tuple.Value[i]);
was = true;
}
sb.Append(")");
cls.LookupVariables[tuple.Key] = AssignVariable(sdw, sb.ToString());
}
}