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()); } }