internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters) { StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning); bool first = true; commandText.Append("INSERT INTO "); tree.Target.Expression.Accept(translator); // (c1, c2, c3, ...) commandText.Append("("); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); } commandText.AppendLine(")"); // values c1, c2, ... first = true; commandText.Append("VALUES ("); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Value.Accept(translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } commandText.AppendLine(")"); parameters = translator.Parameters; return commandText.ToString(); }
internal static string[] GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters, bool isLocalProvider) { var commandTexts = new List<String>(); var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity); var translator = new ExpressionTranslator( commandText, tree, null != tree.Returning, isLocalProvider); // insert [schemaName].[tableName] commandText.Append("insert "); tree.Target.Expression.Accept(translator); if (0 < tree.SetClauses.Count) { // (c1, c2, c3, ...) commandText.Append("("); var first = true; foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); } commandText.AppendLine(")"); // values c1, c2, ... first = true; commandText.Append("values ("); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Value.Accept(translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } commandText.AppendLine(")"); } else { // default values throw ADP1.NotSupported("Default values not supported"); } commandTexts.Add(commandText.ToString()); commandText.Length = 0; // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); if (!String.IsNullOrEmpty(commandText.ToString())) { commandTexts.Add(commandText.ToString()); } parameters = translator.Parameters; return commandTexts.ToArray(); }
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters) { StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, "InsertFunction"); // insert [schemaName].[tableName] commandText.Append("INSERT INTO "); tree.Target.Expression.Accept(translator); if (tree.SetClauses.Count > 0) { // (c1, c2, c3, ...) commandText.Append("("); bool first = true; foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); } commandText.AppendLine(")"); // values c1, c2, ... first = true; commandText.Append(" VALUES ("); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Value.Accept(translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } commandText.AppendLine(");"); } else // No columns specified. Insert an empty row containing default values by inserting null into the rowid { commandText.AppendLine(" DEFAULT VALUES;"); } // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return commandText.ToString(); }
protected virtual void VisitInsertCommandTree(DbInsertCommandTree insertTree) { EntityUtil.CheckArgumentNull(insertTree, "insertTree"); this.VisitExpressionBindingPre(insertTree.Target); this.VisitModificationClauses(insertTree.SetClauses); if (insertTree.Returning != null) { this.VisitExpression(insertTree.Returning); } this.VisitExpressionBindingPost(insertTree.Target); }
private DbModificationCommandTree RebuildCommandTree(DbModificationCommandTree originalTree, Dictionary<DbSetClause, DbSetClause> clauseMappings) { if (clauseMappings.Count == 0) { return originalTree; } DbModificationCommandTree result; Debug.Assert(originalTree.CommandTreeKind == DbCommandTreeKind.Insert || originalTree.CommandTreeKind == DbCommandTreeKind.Update, "Set clauses specified for a modification tree that is not an update or insert tree?"); if (originalTree.CommandTreeKind == DbCommandTreeKind.Insert) { DbInsertCommandTree insertTree = (DbInsertCommandTree)originalTree; result = new DbInsertCommandTree(insertTree.MetadataWorkspace, insertTree.DataSpace, insertTree.Target, ReplaceClauses(insertTree.SetClauses, clauseMappings).AsReadOnly(), insertTree.Returning); } else { DbUpdateCommandTree updateTree = (DbUpdateCommandTree)originalTree; result = new DbUpdateCommandTree(updateTree.MetadataWorkspace, updateTree.DataSpace, updateTree.Target, updateTree.Predicate, ReplaceClauses(updateTree.SetClauses, clauseMappings).AsReadOnly(), updateTree.Returning); } return result; }
/// <summary> /// Builds insert command. /// </summary> /// <param name="newRow">Row to insert.</param> /// <param name="processor">Context for the table we're inserting into.</param> /// <returns>Insert command.</returns> internal UpdateCommand BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor) { // Bind the insert target DbExpressionBinding target = GetTarget(processor); // Create set clauses and returning parameter Dictionary<int, string> outputIdentifiers; DbExpression returning; bool rowMustBeTouched = true; // for inserts, the row must always be touched List<DbModificationClause> setClauses = new List<DbModificationClause>(); foreach (DbModificationClause clause in BuildSetClauses(target, newRow, null, processor, /* insertMode */ true, out outputIdentifiers, out returning, ref rowMustBeTouched)) { setClauses.Add(clause); } // Initialize DML command tree DbInsertCommandTree commandTree = new DbInsertCommandTree(m_translator.MetadataWorkspace, DataSpace.SSpace, target, setClauses.AsReadOnly(), returning); // Create command UpdateCommand command = new DynamicUpdateCommand(processor, m_translator, ModificationOperator.Insert, null, newRow, commandTree, outputIdentifiers); return command; }
protected virtual void VisitInsertCommandTree(DbInsertCommandTree insertTree) { //Contract.Requires(insertTree != null); VisitExpressionBindingPre(insertTree.Target); VisitModificationClauses(insertTree.SetClauses); if (insertTree.Returning != null) { VisitExpression(insertTree.Returning); } VisitExpressionBindingPost(insertTree.Target); }
/// <summary> /// Determine whether we should use a generated values variable to return server generated values. /// This is true when we're attempting to insert a row where the primary key is server generated /// but is not an integer type (and therefore can't be used with scope_identity()). It is also true /// where there is a compound server generated key. /// </summary> private static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion, ExpressionTranslator translator) { bool useGeneratedValuesVariable = false; if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null) { // Figure out which columns have values HashSet<EdmMember> columnsWithValues = new HashSet<EdmMember>(tree.SetClauses.Cast<DbSetClause>().Select(s => ((DbPropertyExpression)s.Property).Property)); // Only SQL Server 2005+ support an output clause for inserts bool firstKeyFound = false; foreach (EdmMember keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers) { if (!columnsWithValues.Contains(keyMember)) { if (firstKeyFound) { // compound server gen key useGeneratedValuesVariable = true; break; } else { firstKeyFound = true; if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage)) { // unsupported type useGeneratedValuesVariable = true; break; } } } } } return useGeneratedValuesVariable; }
internal static string GenerateInsertSql(DbInsertCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters) { StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, sqlVersion); bool useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlVersion, translator); EntityType tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType; if (useGeneratedValuesVariable) { // manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)" commandText .Append("declare ") .Append(s_generatedValuesVariableName) .Append(" table("); bool first = true; foreach (EdmMember column in tableType.KeyMembers) { if (first) { first = false; } else { commandText.Append(", "); } string columnType = SqlGenerator.GenerateSqlForStoreType(sqlVersion, column.TypeUsage); if (columnType == "rowversion" || columnType == "timestamp") { // rowversion and timestamp are intrinsically read-only. use binary to gather server generated // values for these types. columnType = "binary(8)"; } commandText .Append(GenerateMemberTSql(column)) .Append(" ") .Append(columnType); Facet collationFacet; if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet)) { string collation = collationFacet.Value as string; if (!string.IsNullOrEmpty(collation)) { commandText.Append(" collate ").Append(collation); } } } Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values"); commandText.AppendLine(")"); } // insert [schemaName].[tableName] commandText.Append("insert "); tree.Target.Expression.Accept(translator); if (0 < tree.SetClauses.Count) { // (c1, c2, c3, ...) commandText.Append("("); bool first = true; foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); } commandText.AppendLine(")"); } else { commandText.AppendLine(); } if (useGeneratedValuesVariable) { // output inserted.id into @generated_values commandText.Append("output "); bool first = true; foreach (EdmMember column in tableType.KeyMembers) { if (first) { first = false; } else { commandText.Append(", "); } commandText.Append("inserted."); commandText.Append(GenerateMemberTSql(column)); } commandText .Append(" into ") .AppendLine(s_generatedValuesVariableName); } if (0 < tree.SetClauses.Count) { // values c1, c2, ... bool first = true; commandText.Append("values ("); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Value.Accept(translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } commandText.AppendLine(")"); } else { // default values commandText.AppendLine("default values"); } // generate returning sql GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable); parameters = translator.Parameters; return commandText.ToString(); }
public SqlInsertGenerator(DbInsertCommandTree commandTree) { _commandTree = commandTree; }