internal static string GenerateUpdateSql(DbUpdateCommandTree tree, out List<DbParameter> parameters) { StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning); bool first = true; commandText.Append("UPDATE "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // set c1 = ..., c2 = ..., ... commandText.Append("SET "); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); commandText.Append(" = "); setClause.Value.Accept(translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } if (first) { // If first is still true, it indicates there were no set // clauses. Introduce a fake set clause so that: // - we acquire the appropriate locks // - server-gen columns (e.g. timestamp) get recomputed EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target; // hope this column isn't indexed to not waste power EdmMember someColumn = table.ElementType.Members.Last(x => !IsStoreGenerated(x)); commandText.AppendFormat("{0} = {0}", GenerateMemberSql(someColumn)); } commandText.AppendLine(); // where c1 = ..., c2 = ... commandText.Append("WHERE "); tree.Predicate.Accept(translator); commandText.AppendLine(); // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return commandText.ToString(); }
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(); }
internal static string GenerateDeleteSql(DbDeleteCommandTree tree, out List<DbParameter> parameters) { StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, false); commandText.Append("DELETE FROM "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // where c1 = ... AND c2 = ... commandText.Append("WHERE "); tree.Predicate.Accept(translator); parameters = translator.Parameters; return commandText.ToString(); }
/// <summary> /// Generates SQL fragment returning server-generated values. /// Requires: translator knows about member values so that we can figure out /// how to construct the key predicate. /// <code> /// Sample SQL: /// /// select IdentityValue /// from dbo.MyTable /// where @@ROWCOUNT > 0 and IdentityValue = scope_identity() /// /// or /// /// select TimestamptValue /// from dbo.MyTable /// where @@ROWCOUNT > 0 and Id = 1 /// /// Note that we filter on rowcount to ensure no rows are returned if no rows were modified. /// </code> /// </summary> /// <param name="commandText">Builder containing command text</param> /// <param name="tree">Modification command tree</param> /// <param name="translator">Translator used to produce DML SQL statement /// for the tree</param> /// <param name="returning">Returning expression. If null, the method returns /// immediately without producing a SELECT statement.</param> private static void GenerateReturningSql( StringBuilder commandText, DbModificationCommandTree tree, ExpressionTranslator translator, DbExpression returning) { // Nothing to do if there is no Returning expression if (returning == null) { return; } EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target; IEnumerable<EdmMember> columnsToFetch = table.ElementType.Members .Where(m => IsStoreGenerated(m)) .Except((!(tree is DbInsertCommandTree) ? table.ElementType.KeyMembers : Enumerable.Empty<EdmMember>())); StringBuilder startBlock = new StringBuilder(); string separator = string.Empty; startBlock.AppendLine("EXECUTE BLOCK ("); separator = string.Empty; foreach (FbParameter param in translator.Parameters) { startBlock.Append(separator); startBlock.Append(param.ParameterName.Replace("@", string.Empty)); startBlock.Append(" "); EdmMember member = translator.MemberValues.First(m => m.Value.Contains(param)).Key; startBlock.Append(SqlGenerator.GetSqlPrimitiveType(member.TypeUsage)); if (param.FbDbType == FbDbType.VarChar || param.FbDbType == FbDbType.Char) startBlock.Append(" CHARACTER SET UTF8"); startBlock.Append(" = "); startBlock.Append(param.ParameterName); separator = ", "; } startBlock.AppendLine(") "); startBlock.AppendLine("RETURNS ("); separator = string.Empty; foreach (EdmMember m in columnsToFetch) { startBlock.Append(separator); startBlock.Append(GenerateMemberSql(m)); startBlock.Append(" "); startBlock.Append(SqlGenerator.GetSqlPrimitiveType(m.TypeUsage)); separator = ", "; } startBlock.AppendLine(")"); startBlock.AppendLine("AS BEGIN"); string newCommand = ChangeParamsToPSQLParams(commandText.ToString(), translator.Parameters.Select(p => p.ParameterName).ToArray()); commandText.Remove(0, commandText.Length); commandText.Insert(0, newCommand); commandText.Insert(0, startBlock.ToString()); commandText.Append("RETURNING "); separator = string.Empty; foreach (EdmMember m in columnsToFetch) { commandText.Append(separator); commandText.Append(GenerateMemberSql(m)); separator = ", "; } commandText.Append(" INTO "); separator = string.Empty; foreach (EdmMember m in columnsToFetch) { commandText.Append(separator); commandText.Append(":" + GenerateMemberSql(m)); separator = ", "; } commandText.AppendLine(";"); commandText.AppendLine("SUSPEND;"); commandText.AppendLine("END"); }