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); // insert [schemaName].[tableName] commandText.Append("insert "); tree.Target.Expression.Accept(translator); // (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(")"); // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return(commandText.ToString()); }
/// <summary> /// General purpose static function that can be called from System.Data assembly /// </summary> /// <param name="tree">command tree</param> /// <param name="version">version</param> /// <param name="parameters">Parameters to add to the command tree corresponding /// to constants in the command tree. Used only in ModificationCommandTrees.</param> /// <returns>The string representing the SQL to be executed.</returns> internal static string GenerateSql(DbCommandTree tree, EFIngresStoreVersion version, out List <DbParameter> parameters, out CommandType commandType) { commandType = CommandType.Text; //Handle Query DbQueryCommandTree queryCommandTree = tree as DbQueryCommandTree; if (queryCommandTree != null) { SqlGenerator sqlGen = new SqlGenerator(version); parameters = null; return(sqlGen.GenerateSql((DbQueryCommandTree)tree)); } //Handle Function DbFunctionCommandTree DbFunctionCommandTree = tree as DbFunctionCommandTree; if (DbFunctionCommandTree != null) { SqlGenerator sqlGen = new SqlGenerator(version); parameters = null; string sql = sqlGen.GenerateFunctionSql(DbFunctionCommandTree, out commandType); return(sql); } //Handle Insert DbInsertCommandTree insertCommandTree = tree as DbInsertCommandTree; if (insertCommandTree != null) { return(DmlSqlGenerator.GenerateInsertSql(insertCommandTree, out parameters)); } //Handle Delete DbDeleteCommandTree deleteCommandTree = tree as DbDeleteCommandTree; if (deleteCommandTree != null) { return(DmlSqlGenerator.GenerateDeleteSql(deleteCommandTree, out parameters)); } //Handle Update DbUpdateCommandTree updateCommandTree = tree as DbUpdateCommandTree; if (updateCommandTree != null) { return(DmlSqlGenerator.GenerateUpdateSql(updateCommandTree, out parameters)); } throw new NotSupportedException("Unrecognized command tree type"); }
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(")"); // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return commandText.ToString(); }
/// <summary> /// In case of an insert command we always assign the correct value to the tenantId /// </summary> private static bool InterceptInsertCommand(DbCommandTreeInterceptionContext interceptionContext, int userId) { var shoolcontextList = interceptionContext.DbContexts.OfType <SchoolContext>(); if (shoolcontextList.Any(x => x.IsSeeding)) { return(true); } var insertCommand = interceptionContext.Result as DbInsertCommandTree; if (insertCommand != null) { var column = TenantAwareAttribute.GetTenantColumnName(insertCommand.Target.VariableType.EdmType); if (!string.IsNullOrEmpty(column)) { // Create the variable reference in order to create the property var variableReference = DbExpressionBuilder.Variable(insertCommand.Target.VariableType, insertCommand.Target.VariableName); // Create the property to which will assign the correct value var tenantProperty = DbExpressionBuilder.Property(variableReference, column); // Create the set clause, object representation of sql insert command var tenantSetClause = DbExpressionBuilder.SetClause(tenantProperty, DbExpression.FromInt32(userId)); // Remove potential assignment of tenantId for extra safety var filteredSetClauses = insertCommand.SetClauses.Cast <DbSetClause>() .Where(sc => ((DbPropertyExpression)sc.Property).Property.Name != column); // Construct the final clauses, object representation of sql insert command values var finalSetClauses = new ReadOnlyCollection <DbModificationClause>(new List <DbModificationClause>(filteredSetClauses) { tenantSetClause }); // Construct the new command var newInsertCommand = new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, finalSetClauses, insertCommand.Returning); interceptionContext.Result = newInsertCommand; // True means an interception successfully happened so there is no need to continue return(true); } } return(false); }
private static DbInsertCommandTree HandleInsertCommand(DbInsertCommandTree insertCommand) { var newSetClauses = GenerateSetClauses(insertCommand.SetClauses); var newCommand = new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, newSetClauses, insertCommand.Returning); return(newCommand); }
private string IntroduceRequiredLocalVariables(EntityType entityType, DbInsertCommandTree commandTree) { DebugCheck.NotNull(entityType); DebugCheck.NotNull(commandTree); var storeGeneratedKeys = entityType .KeyProperties .Where(p => p.IsStoreGeneratedIdentity) .ToList(); var sql = new SqlStringBuilder { UpperCaseKeywords = true }; if (storeGeneratedKeys.Any()) { foreach (var keyProperty in storeGeneratedKeys) { sql.Append(sql.Length == 0 ? "DECLARE " : ", "); sql.Append("@"); sql.Append(keyProperty.Name); sql.Append(" "); sql.Append(DmlSqlGenerator.GetVariableType(_sqlGenerator, keyProperty)); } sql.AppendLine(); var translator = new DmlSqlGenerator.ExpressionTranslator( sql, commandTree, true, _sqlGenerator, entityType.KeyProperties); DmlSqlGenerator.GenerateReturningSql( sql, commandTree, entityType, translator, commandTree.Returning, DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, _sqlGenerator.SqlVersion)); sql.AppendLine(); sql.AppendLine(); } return(sql.ToString()); }
private static DbCommandTree HandleInsertCommand(DbInsertCommandTree insertCommand, DateTime dateTime) { var setClauses = insertCommand.SetClauses .Select(clause => clause.UpdateIfMatch(CreatedColumnName, DbExpression.FromDateTime(dateTime))) .Select(clause => clause.UpdateIfMatch(ModifiedColumnName, DbExpression.FromDateTime(dateTime))) .ToList(); return(new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, setClauses.AsReadOnly(), insertCommand.Returning)); }
internal static string GenerateInsertSql(DbInsertCommandTree tree, EFOracleProviderManifest providerManifest, EFOracleVersion sqlVersion, out List <OracleParameter> parameters) { StringBuilder commandText = new StringBuilder(256); DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, null != tree.Returning, sqlVersion); commandText.Append("insert into "); tree.Target.Expression.Accept((DbExpressionVisitor)translator); if (0 < tree.SetClauses.Count) { commandText.Append("("); bool flag1 = true; foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses) { if (flag1) { flag1 = false; } else { commandText.Append(", "); } setClause.Property.Accept((DbExpressionVisitor)translator); } commandText.AppendLine(")"); bool flag2 = true; commandText.Append("values ("); foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses) { if (flag2) { flag2 = false; } else { commandText.Append(", "); } setClause.Value.Accept((DbExpressionVisitor)translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } commandText.AppendLine(")"); } else { commandText.AppendLine().AppendLine(" values (default)"); } DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)tree, translator, tree.Returning, providerManifest, sqlVersion, false); parameters = translator.Parameters; return(commandText.ToString()); }
private static DbCommandTree HandleInsertCommand(DbInsertCommandTree insertCommand, string createDateColumnName, string updateDateColumnName) { var now = DateTime.UtcNow; var setClauses = insertCommand.SetClauses .Select(clause => clause.UpdateIfMatch(createDateColumnName, DbExpression.FromDateTime(now))) .Select(clause => clause.UpdateIfMatch(updateDateColumnName, DbExpression.FromDateTime(now))) .ToList(); return(new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, setClauses.AsReadOnly(), insertCommand.Returning)); }
internal UpdateCommand BuildInsertCommand( PropagatorResult newRow, TableChangeProcessor processor) { DbExpressionBinding target = UpdateCompiler.GetTarget(processor); bool rowMustBeTouched = true; List <DbModificationClause> modificationClauseList = new List <DbModificationClause>(); Dictionary <int, string> outputIdentifiers; DbExpression returning; foreach (DbModificationClause buildSetClause in this.BuildSetClauses(target, newRow, (PropagatorResult)null, processor, true, out outputIdentifiers, out returning, ref rowMustBeTouched)) { modificationClauseList.Add(buildSetClause); } DbInsertCommandTree insertCommandTree = new DbInsertCommandTree(this.m_translator.MetadataWorkspace, DataSpace.SSpace, target, new ReadOnlyCollection <DbModificationClause>((IList <DbModificationClause>)modificationClauseList), returning); return((UpdateCommand) new DynamicUpdateCommand(processor, this.m_translator, ModificationOperator.Insert, (PropagatorResult)null, newRow, (DbModificationCommandTree)insertCommandTree, outputIdentifiers)); }
internal string Print(DbInsertCommandTree tree) { DebugCheck.NotNull(tree); TreeNode targetNode = null; if (tree.Target != null) { targetNode = _visitor.VisitBinding("Target", tree.Target); } else { targetNode = new TreeNode("Target"); } var clausesNode = new TreeNode("SetClauses"); foreach (var clause in tree.SetClauses) { if (clause != null) { clausesNode.Children.Add(clause.Print(_visitor)); } } TreeNode returningNode = null; if (null != tree.Returning) { returningNode = new TreeNode("Returning", _visitor.VisitExpression(tree.Returning)); } else { returningNode = new TreeNode("Returning"); } return(Print( new TreeNode( "DbInsertCommandTree", CreateParametersNode(tree), targetNode, clausesNode, returningNode))); }
public override string GenerateSQL(DbCommandTree tree) { DbInsertCommandTree commandTree = tree as DbInsertCommandTree; InsertStatement statement = new InsertStatement(); DbExpressionBinding e = commandTree.Target; statement.Target = (InputFragment)e.Expression.Accept(this); scope.Add("target", statement.Target); foreach (DbSetClause setClause in commandTree.SetClauses) { statement.Sets.Add(setClause.Property.Accept(this)); } if (values == null) { values = new Dictionary <EdmMember, SqlFragment>(); } foreach (DbSetClause setClause in commandTree.SetClauses) { DbExpression value = setClause.Value; SqlFragment valueFragment = value.Accept(this); statement.Values.Add(valueFragment); if (value.ExpressionKind != DbExpressionKind.Null) { EdmMember property = ((DbPropertyExpression)setClause.Property).Property; values.Add(property, valueFragment); } } if (commandTree.Returning != null) { statement.ReturningSelect = GenerateReturningSql(commandTree, commandTree.Returning); } return(statement.ToString()); }
public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext) { if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { var insertCommand = interceptionContext.Result as DbInsertCommandTree; if (insertCommand != null) { var namesToIgnore = new List<string> { "ValidFrom", "ValidTo" }; var props = new List<DbModificationClause>(insertCommand.SetClauses); props = props.Where(_ => !namesToIgnore.Contains((((_ as DbSetClause)?.Property as DbPropertyExpression)?.Property as EdmProperty)?.Name)).ToList(); var newSetClauses = new ReadOnlyCollection<DbModificationClause>(props); var newCommand = new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, newSetClauses, insertCommand.Returning); interceptionContext.Result = newCommand; }; } }
private static DbModificationCommandTree RebuildCommandTree( DbModificationCommandTree originalTree, Dictionary <DbSetClause, DbSetClause> clauseMappings) { if (clauseMappings.Count == 0) { return(originalTree); } DbModificationCommandTree modificationCommandTree; if (originalTree.CommandTreeKind == DbCommandTreeKind.Insert) { DbInsertCommandTree insertCommandTree = (DbInsertCommandTree)originalTree; modificationCommandTree = (DbModificationCommandTree) new DbInsertCommandTree(insertCommandTree.MetadataWorkspace, insertCommandTree.DataSpace, insertCommandTree.Target, new ReadOnlyCollection <DbModificationClause>((IList <DbModificationClause>)DynamicUpdateCommand.ReplaceClauses(insertCommandTree.SetClauses, clauseMappings)), insertCommandTree.Returning); } else { DbUpdateCommandTree updateCommandTree = (DbUpdateCommandTree)originalTree; modificationCommandTree = (DbModificationCommandTree) new DbUpdateCommandTree(updateCommandTree.MetadataWorkspace, updateCommandTree.DataSpace, updateCommandTree.Target, updateCommandTree.Predicate, new ReadOnlyCollection <DbModificationClause>((IList <DbModificationClause>)DynamicUpdateCommand.ReplaceClauses(updateCommandTree.SetClauses, clauseMappings)), updateCommandTree.Returning); } return(modificationCommandTree); }
/// <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) { var useGeneratedValuesVariable = false; if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null) { // Figure out which columns have values var columnsWithValues = new HashSet <EdmMember>(tree.SetClauses.Cast <DbSetClause>().Select(s => ((DbPropertyExpression)s.Property).Property)); // Only SQL Server 2005+ support an output clause for inserts var firstKeyFound = false; foreach (var 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); }
public string GenerateInsert(ICollection <DbInsertCommandTree> commandTrees) { StringBuilder stringBuilder = new StringBuilder(); DbInsertCommandTree insertCommandTree1 = commandTrees.First <DbInsertCommandTree>(); List <SqlParameter> parameters; stringBuilder.Append(DmlSqlGenerator.GenerateInsertSql(insertCommandTree1, this._sqlGenerator, out parameters, false, true, false)); stringBuilder.AppendLine(); EntityType elementType = (EntityType)((DbScanExpression)insertCommandTree1.Target.Expression).Target.ElementType; stringBuilder.Append(this.IntroduceRequiredLocalVariables(elementType, insertCommandTree1)); foreach (DbInsertCommandTree tree in commandTrees.Skip <DbInsertCommandTree>(1)) { stringBuilder.Append(DmlSqlGenerator.GenerateInsertSql(tree, this._sqlGenerator, out parameters, false, true, false)); stringBuilder.AppendLine(); } List <DbInsertCommandTree> list = commandTrees.Where <DbInsertCommandTree>((Func <DbInsertCommandTree, bool>)(ct => ct.Returning != null)).ToList <DbInsertCommandTree>(); if (list.Any <DbInsertCommandTree>()) { DmlFunctionSqlGenerator.ReturningSelectSqlGenerator selectSqlGenerator = new DmlFunctionSqlGenerator.ReturningSelectSqlGenerator(); foreach (DbInsertCommandTree insertCommandTree2 in list) { insertCommandTree2.Target.Expression.Accept((DbExpressionVisitor)selectSqlGenerator); insertCommandTree2.Returning.Accept((DbExpressionVisitor)selectSqlGenerator); } foreach (EdmProperty keyProperty1 in elementType.KeyProperties) { EdmProperty keyProperty = keyProperty1; DbExpression right = insertCommandTree1.SetClauses.Cast <DbSetClause>().Where <DbSetClause>((Func <DbSetClause, bool>)(sc => ((DbPropertyExpression)sc.Property).Property == keyProperty)).Select <DbSetClause, DbExpression>((Func <DbSetClause, DbExpression>)(sc => sc.Value)).SingleOrDefault <DbExpression>() ?? (DbExpression)keyProperty.TypeUsage.Parameter(keyProperty.Name); insertCommandTree1.Target.Variable.Property(keyProperty).Equal(right).Accept((DbExpressionVisitor)selectSqlGenerator); } stringBuilder.Append(selectSqlGenerator.Sql); } return(stringBuilder.ToString().TrimEnd()); }
public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext) { if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { var insertCommand = interceptionContext.Result as DbInsertCommandTree; if (insertCommand != null) { var newSetClauses = GenerateSetClauses(insertCommand.SetClauses); var newCommand = new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, newSetClauses, insertCommand.Returning); interceptionContext.Result = newCommand; } var updateCommand = interceptionContext.Result as DbUpdateCommandTree; if (updateCommand != null) { var newSetClauses = GenerateSetClauses(updateCommand.SetClauses); var newCommand = new DbUpdateCommandTree( updateCommand.MetadataWorkspace, updateCommand.DataSpace, updateCommand.Target, updateCommand.Predicate, newSetClauses, updateCommand.Returning); interceptionContext.Result = newCommand; } } }
private void InterceptInsertStatement(DbCommandTreeInterceptionContext interceptionContext) { var insertCommand = interceptionContext.Result as DbInsertCommandTree; List <DbModificationClause> finalSetClauses = new List <DbModificationClause>((IEnumerable <DbModificationClause>)insertCommand.SetClauses); //TENANT AWARE string column = "TenantId"; DbExpression newValue = "JustMe"; // Here we should insert the right value // TODO: Need to check if this entity is a Multitenant entity in the right way // You can use the attribute like in the original sample if ( interceptionContext.ObjectContexts.Count() == 1 && interceptionContext.ObjectContexts.First().DefaultContainerName == "HistoryContext") { return; } finalSetClauses.Add( GetInsertSetClause(column, newValue, insertCommand)); // Construct the final clauses, object representation of sql insert command values // In insert probably you can avoid to change the newInstanceAfterInsert because you are using a Guid for the entity ID that is always unique (it does not matter the tenant). var newInsertCommand = new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, new ReadOnlyCollection <DbModificationClause>(finalSetClauses), insertCommand.Returning); interceptionContext.Result = newInsertCommand; }
public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext) { if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { var insertCommand = interceptionContext.Result as DbInsertCommandTree; if (insertCommand != null) { List <DbModificationClause> finalSetClauses = new List <DbModificationClause>( (IEnumerable <DbModificationClause>)insertCommand.SetClauses.Select( a => { var dbSetClause = a as DbSetClause; if (dbSetClause != null) { var dbPropertyExpression = dbSetClause.Property as DbPropertyExpression; if (dbPropertyExpression != null) { var edmProperty = dbPropertyExpression.Property as EdmProperty; if (edmProperty != null && edmProperty.MaxLength != null && _typesToTrim.Contains(edmProperty.TypeName)) { var dbConstantExpression = dbSetClause.Value as DbConstantExpression; if (dbConstantExpression != null && dbConstantExpression.Value != null) { var value = dbConstantExpression.Value.ToString(); if (!string.IsNullOrEmpty(value) && value.Length > (int)edmProperty.MaxLength) { return(DbExpressionBuilder.SetClause( DbExpressionBuilder.Property( DbExpressionBuilder.Variable( insertCommand.Target.VariableType, insertCommand.Target.VariableName), dbPropertyExpression.Property.Name), EdmFunctions.Trim( dbConstantExpression.Value.ToString() .Substring(0, (int)edmProperty.MaxLength)))); } } } } } return(a); })); var newInsertCommand = new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, new ReadOnlyCollection <DbModificationClause>(finalSetClauses), insertCommand.Returning); interceptionContext.Result = newInsertCommand; } } var updateCommand = interceptionContext.Result as DbUpdateCommandTree; if (updateCommand != null) { List <DbModificationClause> finalSetClauses = new List <DbModificationClause>( (IEnumerable <DbModificationClause>)updateCommand.SetClauses.Select( a => { var dbSetClause = a as DbSetClause; if (dbSetClause != null) { var dbPropertyExpression = dbSetClause.Property as DbPropertyExpression; if (dbPropertyExpression != null) { var edmProperty = dbPropertyExpression.Property as EdmProperty; if (edmProperty != null && edmProperty.MaxLength != null && _typesToTrim.Contains(edmProperty.TypeName)) { var dbConstantExpression = dbSetClause.Value as DbConstantExpression; if (dbConstantExpression != null && dbConstantExpression.Value != null) { var value = dbConstantExpression.Value.ToString(); if (!string.IsNullOrEmpty(value) && value.Length > (int)edmProperty.MaxLength) { return(DbExpressionBuilder.SetClause( DbExpressionBuilder.Property( DbExpressionBuilder.Variable( updateCommand.Target.VariableType, updateCommand.Target.VariableName), dbPropertyExpression.Property.Name), EdmFunctions.Trim( dbConstantExpression.Value.ToString() .Substring(0, (int)edmProperty.MaxLength)))); } } } } } return(a); })); var newInsertCommand = new DbUpdateCommandTree( updateCommand.MetadataWorkspace, updateCommand.DataSpace, updateCommand.Target, updateCommand.Predicate, new ReadOnlyCollection <DbModificationClause>(finalSetClauses), updateCommand.Returning); interceptionContext.Result = newInsertCommand; } }
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()); }
public InsertCommandAction(DbInsertCommandTree commandTree) { this.commandTree = commandTree; }
internal static string GenerateInsertSql( DbInsertCommandTree tree, SqlGenerator sqlGenerator, out List <DbParameter> parameters, bool createParameters = true) { StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator( commandText, tree, null != tree.Returning, sqlGenerator, "InsertFunction", createParameters); // 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, true); parameters = translator.Parameters; return(commandText.ToString()); }
public SqlInsertGenerator(DbInsertCommandTree commandTree) { _commandTree = commandTree; }
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List <DbParameter> parameters, bool insertParametersValuesInSql = false) { var commandText = new StringBuilder(COMMANDTEXT_STRINGBUILDER_INITIALCAPACITY); var translator = new ExpressionTranslator(commandText, tree, tree.Returning != null, insertParametersValuesInSql); commandText.Append("insert into "); tree.Target.Expression.Accept(translator); // Actually is not possible to retrieve the last inserted guid from Access // We can understand if there is a guid checking the returning value of the insert // statement string guidAutogeneratedColumn = null; if (tree.Returning is DbNewInstanceExpression) { guidAutogeneratedColumn = GetGuidArgs(tree.Returning as DbNewInstanceExpression); } if (tree.SetClauses.Count != 0) { var first = true; // (c1, c2, c3, ...) commandText.Append("("); if (!string.IsNullOrEmpty(guidAutogeneratedColumn)) { commandText.Append(SQLiteProviderManifestHelper.QuoteIdentifier(guidAutogeneratedColumn)); first = false; } 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 ("); if (!string.IsNullOrEmpty(guidAutogeneratedColumn)) { _lastGuid = Guid.NewGuid(); commandText.Append(string.Format("{{{0}}}", _lastGuid)); first = false; } 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 { commandText.AppendLine(" default values;"); } // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return(commandText.ToString()); }
private DbSetClause GetInsertSetClause(string column, DbExpression newValueToSetToDb, DbInsertCommandTree insertCommand) { // Create the variable reference in order to create the property DbVariableReferenceExpression variableReference = DbExpressionBuilder.Variable(insertCommand.Target.VariableType, insertCommand.Target.VariableName); // Create the property to which will assign the correct value DbPropertyExpression tenantProperty = DbExpressionBuilder.Property(variableReference, column); // Create the set clause, object representation of sql insert command DbSetClause newSetClause = DbExpressionBuilder.SetClause(tenantProperty, newValueToSetToDb); return(newSetClause); }
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(); }
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 virtual void TreeCreated(DbCommandTreeInterceptionContext interceptionContext) { if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { // Check that there is an authenticated user in this context var identity = Thread.CurrentPrincipal.Identity; if (identity == null) { return; } //var userIdclaim = identity.Claims.SingleOrDefault(c => c.Type == ClaimTypes.NameIdentifier); //if (userIdclaim == null) //{ // return; //} // In case of query command change the query by adding a filtering based on tenantId var queryCommand = interceptionContext.Result as DbQueryCommandTree; if (queryCommand != null) { var newQuery = queryCommand.Query.Accept(new FactoryQueryVisitor()); interceptionContext.Result = new DbQueryCommandTree( queryCommand.MetadataWorkspace, queryCommand.DataSpace, newQuery); return; } var insertCommand = interceptionContext.Result as DbInsertCommandTree; if (insertCommand != null) { var column = EdmHelper.GetFactoryColumnName(insertCommand.Target.VariableType.EdmType); if (!string.IsNullOrEmpty(column)) { // Create the variable reference in order to create the property var variableReference = DbExpressionBuilder.Variable(insertCommand.Target.VariableType, insertCommand.Target.VariableName); // Create the property to which will assign the correct value var tenantProperty = DbExpressionBuilder.Property(variableReference, column); // Create the set clause, object representation of sql insert command var tenantSetClause = DbExpressionBuilder.SetClause(tenantProperty, DbExpression.FromString("1")); // Remove potential assignment of tenantId for extra safety var filteredSetClauses = insertCommand.SetClauses.Cast <DbSetClause>() .Where(sc => ((DbPropertyExpression)sc.Property).Property.Name != column); // Construct the final clauses, object representation of sql insert command values var finalSetClauses = new ReadOnlyCollection <DbModificationClause> (new List <DbModificationClause>(filteredSetClauses) { tenantSetClause }); var newInsertCommand = new DbInsertCommandTree( insertCommand.MetadataWorkspace, insertCommand.DataSpace, insertCommand.Target, finalSetClauses, insertCommand.Returning); interceptionContext.Result = newInsertCommand; return; } } } }
public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext) { var logSchemaName = "test"; var logTableName = "TransactionHistory"; if (!(interceptionContext.OriginalResult.CommandTreeKind.Equals(DbCommandTreeKind.Insert) || interceptionContext.OriginalResult.CommandTreeKind.Equals(DbCommandTreeKind.Update) || interceptionContext.OriginalResult.CommandTreeKind.Equals(DbCommandTreeKind.Delete))) { return; } DbInsertCommandTree insertCommand = null; DbUpdateCommandTree updateCommand = null; DbDeleteCommandTree deleteCommand = null; if (interceptionContext.OriginalResult.CommandTreeKind.Equals(DbCommandTreeKind.Insert) && interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { insertCommand = interceptionContext.Result as DbInsertCommandTree; } if (interceptionContext.OriginalResult.CommandTreeKind.Equals(DbCommandTreeKind.Update) && interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { updateCommand = interceptionContext.Result as DbUpdateCommandTree; } if (interceptionContext.OriginalResult.CommandTreeKind.Equals(DbCommandTreeKind.Delete) && interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { deleteCommand = interceptionContext.Result as DbDeleteCommandTree; } if (((insertCommand != null && insertCommand.Target.VariableName.Contains(logTableName)) || (updateCommand != null && updateCommand.Target.VariableName.Contains(logTableName)) || (deleteCommand != null && deleteCommand.Target.VariableName.Contains(logTableName)))) { return; } var command = interceptionContext.DbContexts.First().Database.Connection.CreateCommand(); command.CommandText = $"INSERT INTO [{logSchemaName}].[{logTableName}]([ChangedUser],[TransactionDetail],[ChangingUser],[TransactionDate]) SELECT @ChangedUser, @TransactionDetail_Created, @ChangingUser, @TransactionDate;" + command.CommandText; command.Parameters.AddRange(new[] { new SqlParameter("@ChangedUser", ""), new SqlParameter("@ChangingUser", ""), new SqlParameter("@TransactionDetail_Updated", ""), new SqlParameter("@TransactionDate", DateTime.Now) }); if (insertCommand != null) { var context = interceptionContext.WithDbContext(interceptionContext.DbContexts.First()); } if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) { var queryCommand = interceptionContext.Result as DbQueryCommandTree; if (queryCommand != null) { var newQuery = queryCommand.Query.Accept(new StringTrimmerQueryVisitor()); interceptionContext.Result = new DbQueryCommandTree( queryCommand.MetadataWorkspace, queryCommand.DataSpace, newQuery); } } }
internal static string GenerateInsertSql( DbInsertCommandTree tree, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool generateReturningSql = true, bool upperCaseKeywords = true, bool createParameters = true) { SqlStringBuilder commandText = new SqlStringBuilder(256) { UpperCaseKeywords = upperCaseKeywords }; DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, null != tree.Returning, sqlGenerator, (ICollection <EdmProperty>)null, createParameters); bool useGeneratedValuesVariable = DmlSqlGenerator.UseGeneratedValuesVariable(tree, sqlGenerator.SqlVersion); EntityType elementType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType; if (useGeneratedValuesVariable) { commandText.AppendKeyword("declare ").Append("@generated_keys").Append(" table("); bool flag = true; foreach (EdmMember keyMember in elementType.KeyMembers) { if (flag) { flag = false; } else { commandText.Append(", "); } commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember)).Append(" ").Append(DmlSqlGenerator.GetVariableType(sqlGenerator, keyMember)); Facet facet; if (keyMember.TypeUsage.Facets.TryGetValue("Collation", false, out facet)) { string s = facet.Value as string; if (!string.IsNullOrEmpty(s)) { commandText.AppendKeyword(" collate ").Append(s); } } } commandText.AppendLine(")"); } commandText.AppendKeyword("insert "); tree.Target.Expression.Accept((DbExpressionVisitor)translator); if (0 < tree.SetClauses.Count) { commandText.Append("("); bool flag = true; foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses) { if (flag) { flag = false; } else { commandText.Append(", "); } setClause.Property.Accept((DbExpressionVisitor)translator); } commandText.AppendLine(")"); } else { commandText.AppendLine(); } if (useGeneratedValuesVariable) { commandText.AppendKeyword("output "); bool flag = true; foreach (EdmMember keyMember in elementType.KeyMembers) { if (flag) { flag = false; } else { commandText.Append(", "); } commandText.Append("inserted."); commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember)); } commandText.AppendKeyword(" into ").AppendLine("@generated_keys"); } if (0 < tree.SetClauses.Count) { bool flag = true; commandText.AppendKeyword("values ("); foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses) { if (flag) { flag = false; } else { commandText.Append(", "); } setClause.Value.Accept((DbExpressionVisitor)translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } commandText.AppendLine(")"); } else { commandText.AppendKeyword("default values"); commandText.AppendLine(); } if (generateReturningSql) { DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)tree, elementType, translator, tree.Returning, useGeneratedValuesVariable); } parameters = translator.Parameters; return(commandText.ToString()); }
protected virtual IEnumerable <MigrationStatement> Generate(HistoryOperation operation) { foreach (var commandTree in operation.CommandTrees) { List <DbParameter> _; switch (commandTree.CommandTreeKind) { case DbCommandTreeKind.Insert: const int MigrationIdColumn = 0; const int ContextKeyColumn = 1; const int ModelColumn = 2; const int VersionColumn = 3; const int MaxChunkLength = 32000; var dbInsert = (DbInsertCommandTree)commandTree; var modelData = ((dbInsert.SetClauses[ModelColumn] as DbSetClause).Value as DbConstantExpression).Value as byte[]; // If model length is less than max value, stick to original version if (modelData.Length < MaxChunkLength) { using (var writer = SqlWriter()) { writer.Write(DmlSqlGenerator.GenerateInsertSql(dbInsert, out _, generateParameters: false)); yield return(Statement(writer)); } } else { // If it's bigger - we split it into chunks, as big as possible var dataChunks = modelData.Split(MaxChunkLength); // We can't change CommandTree, but we can create new one, only difference being data length using (var writer = SqlWriter()) { var setClauses = new ReadOnlyCollection <DbModificationClause>( new List <DbModificationClause> { dbInsert.SetClauses[MigrationIdColumn], dbInsert.SetClauses[ContextKeyColumn], DbExpressionBuilder.SetClause( ((DbSetClause)dbInsert.SetClauses[ModelColumn]).Property, dataChunks.ElementAt(0).ToArray() ), dbInsert.SetClauses[VersionColumn], }); var newCommandTree = new DbInsertCommandTree(dbInsert.MetadataWorkspace, commandTree.DataSpace, dbInsert.Target, setClauses, dbInsert.Returning); writer.Write(DmlSqlGenerator.GenerateInsertSql(newCommandTree, out _, generateParameters: false)); yield return(Statement(writer)); } // Now we have first Insert, let's update it with chunks of remaing data foreach (var dataChunk in dataChunks.Skip(1)) { using (var writer = SqlWriter()) { var modelProperty = (dbInsert.SetClauses[ModelColumn] as DbSetClause).Property as DbPropertyExpression; var modificationClauses = new List <DbModificationClause> { // Updating existing chunk of data with subsequent part DbExpressionBuilder.SetClause( modelProperty, // TODO: Better solution required // Best if we could use DbExpression.Concat, but it returns DbFunctionExpression, which is not supported // Here we'll get SET Model = 'data', which we can update as text later dataChunk.ToArray() ) }.AsReadOnly(); var updateCommandTree = new DbUpdateCommandTree(dbInsert.MetadataWorkspace, dbInsert.DataSpace, dbInsert.Target, // Predicate is MigrationId value DbExpressionBuilder.Equal( ((DbSetClause)dbInsert.SetClauses[MigrationIdColumn]).Property, ((DbSetClause)dbInsert.SetClauses[MigrationIdColumn]).Value), modificationClauses, dbInsert.Returning); writer.Write(DmlSqlGenerator.GenerateUpdateSql(updateCommandTree, out _, generateParameters: false)); // Since we couldn't concat before, replacing query as string // Replacing SET Model = 'data' // with SET Model = Model || 'data' // Model being first is important, since these are parts of single value var statement = writer.ToString(); var newStatement = statement.Replace($"SET \"{modelProperty.Property.Name}\" = ", $"SET \"{modelProperty.Property.Name}\" = \"{modelProperty.Property.Name}\" || "); yield return(Statement(newStatement)); } } } break; case DbCommandTreeKind.Delete: using (var writer = SqlWriter()) { writer.Write(DmlSqlGenerator.GenerateDeleteSql((DbDeleteCommandTree)commandTree, out _, generateParameters: false)); yield return(Statement(writer)); } break; } } }
internal static string GenerateInsertSql( DbInsertCommandTree tree, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool generateReturningSql = true, bool upperCaseKeywords = true, bool createParameters = true) { var commandText = new SqlStringBuilder(CommandTextBuilderInitialCapacity) { UpperCaseKeywords = upperCaseKeywords }; var translator = new ExpressionTranslator( commandText, tree, null != tree.Returning, sqlGenerator, createParameters: createParameters); var useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlGenerator.SqlVersion); var tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType; if (useGeneratedValuesVariable) { // manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)" commandText .AppendKeyword("declare ") .Append(GeneratedValuesVariableName) .Append(" table("); var first = true; foreach (var column in tableType.KeyMembers) { if (first) { first = false; } else { commandText.Append(", "); } commandText .Append(GenerateMemberTSql(column)) .Append(" ") .Append(GetVariableType(sqlGenerator, column)); Facet collationFacet; if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet)) { var collation = collationFacet.Value as string; if (!string.IsNullOrEmpty(collation)) { commandText.AppendKeyword(" collate ").Append(collation); } } } Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values"); commandText.AppendLine(")"); } // insert [schemaName].[tableName] commandText.AppendKeyword("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(")"); } else { commandText.AppendLine(); } if (useGeneratedValuesVariable) { // output inserted.id into @generated_values commandText.AppendKeyword("output "); var first = true; foreach (var column in tableType.KeyMembers) { if (first) { first = false; } else { commandText.Append(", "); } commandText.Append("inserted."); commandText.Append(GenerateMemberTSql(column)); } commandText .AppendKeyword(" into ") .AppendLine(GeneratedValuesVariableName); } if (0 < tree.SetClauses.Count) { // values c1, c2, ... var first = true; commandText.AppendKeyword("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.AppendKeyword("default values"); commandText.AppendLine(); } if (generateReturningSql) { GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable); } parameters = translator.Parameters; return(commandText.ToString()); }
public SqlInsertGenerator(DbInsertCommandTree commandTree, NpgsqlProviderManifest providerManifest) : base(providerManifest) { _commandTree = commandTree; }