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 WriteInstanceUpdates(SqlDumpWriter sdw, DataSetInstance inst) { foreach (var r in inst.Class.References) { if (!r.BackReference) continue; if (r.BaseClass.SimplePkCol == null) continue; int colord = inst.Class.ColumnOrdinals[r.BindingColumn]; if (inst.Values[colord] == null || inst.Values[colord] == DBNull.Value) continue; int? rvar = GetReferenceVariable(inst.Values[colord].ToString(), r.BaseClass, r.ReferencedClass, r.BindingColumn); if (rvar == null) continue; sdw.Write("update [{0}] set [{1}]=", r.BaseClass.TableName, r.BindingColumn); sdw.WriteVar(rvar.Value); sdw.Write(" where [{0}]=", r.BaseClass.SimplePkCol); sdw.WriteVar(inst.IdVariable.Value); sdw.EndCommand(); } //foreach (var fr in inst.Class.FulltextReferences) //{ // int colord = inst.Class.ColumnOrdinals[fr.BindingColumn]; // var re = new Regex(fr.Regex); // string val = inst.Values[colord].ToString(); // sdw.SuspendVariables(); // val = val.Replace("'", "''"); // val = re.Replace(val, new FulltextVariableRefMatchReplacer { fr = fr, sdw = sdw, ctx = this }.Replace); // sdw.Write("update [{0}] set [{1}]=N'{2}' where [{3}]=", fr.BaseClass.TableName, fr.BindingColumn, val, fr.BaseClass.IdentityColumn); // sdw.WriteVar(inst.IdVariable.Value); // sdw.ResumeVariables(); // sdw.EndCommand(); //} }