Esempio n. 1
0
 public void GenerateMemberTSql_returns_the_quoted_member_name()
 {
     Assert.Equal("[Magic]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic").Object));
     Assert.Equal("[Magic]]Unicorn]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic]Unicorn").Object));
     Assert.Equal("[[Magic]]]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("[Magic]").Object));
     Assert.Equal("[Magic[Unicorn]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic[Unicorn").Object));
 }
        public string GenerateDelete(ICollection <DbDeleteCommandTree> commandTrees, string rowsAffectedParameter)
        {
            DebugCheck.NotNull(commandTrees);

            List <SqlParameter> _;

            var sql = new StringBuilder();

            sql.AppendLine(
                DmlSqlGenerator.GenerateDeleteSql(
                    commandTrees.First(),
                    _sqlGenerator,
                    out _));

            sql.AppendLine();

            foreach (var commandTree in commandTrees.Skip(1))
            {
                sql.AppendLine(
                    DmlSqlGenerator.GenerateDeleteSql(
                        commandTree,
                        _sqlGenerator,
                        out _));

                sql.AppendLine("AND @@ROWCOUNT > 0");
                sql.AppendLine();
            }

            AppendSetRowsAffected(sql, rowsAffectedParameter);

            return(sql.ToString().TrimEnd());
        }
        private string IntroduceRequiredLocalVariables(
            EntityType entityType,
            DbInsertCommandTree commandTree)
        {
            List <EdmProperty> list        = entityType.KeyProperties.Where <EdmProperty>((Func <EdmProperty, bool>)(p => p.IsStoreGeneratedIdentity)).ToList <EdmProperty>();
            SqlStringBuilder   commandText = new SqlStringBuilder()
            {
                UpperCaseKeywords = true
            };

            if (list.Any <EdmProperty>())
            {
                foreach (EdmProperty edmProperty in list)
                {
                    commandText.Append(commandText.Length == 0 ? "DECLARE " : ", ");
                    commandText.Append("@");
                    commandText.Append(edmProperty.Name);
                    commandText.Append(" ");
                    commandText.Append(DmlSqlGenerator.GetVariableType(this._sqlGenerator, (EdmMember)edmProperty));
                }
                commandText.AppendLine();
                DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)commandTree, true, this._sqlGenerator, (ICollection <EdmProperty>)entityType.KeyProperties, true);
                DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)commandTree, entityType, translator, commandTree.Returning, DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, this._sqlGenerator.SqlVersion));
                commandText.AppendLine();
                commandText.AppendLine();
            }
            return(commandText.ToString());
        }
        public string GenerateUpdate(
            ICollection <DbUpdateCommandTree> commandTrees,
            string rowsAffectedParameter)
        {
            if (!commandTrees.Any <DbUpdateCommandTree>())
            {
                return((string)null);
            }
            StringBuilder       sql = new StringBuilder();
            List <SqlParameter> parameters;

            sql.AppendLine(DmlSqlGenerator.GenerateUpdateSql(commandTrees.First <DbUpdateCommandTree>(), this._sqlGenerator, out parameters, false, true));
            foreach (DbUpdateCommandTree tree in commandTrees.Skip <DbUpdateCommandTree>(1))
            {
                sql.Append(DmlSqlGenerator.GenerateUpdateSql(tree, this._sqlGenerator, out parameters, false, true));
                sql.AppendLine("AND @@ROWCOUNT > 0");
                sql.AppendLine();
            }
            List <DbUpdateCommandTree> list = commandTrees.Where <DbUpdateCommandTree>((Func <DbUpdateCommandTree, bool>)(ct => ct.Returning != null)).ToList <DbUpdateCommandTree>();

            if (list.Any <DbUpdateCommandTree>())
            {
                DmlFunctionSqlGenerator.ReturningSelectSqlGenerator selectSqlGenerator = new DmlFunctionSqlGenerator.ReturningSelectSqlGenerator();
                foreach (DbUpdateCommandTree updateCommandTree in list)
                {
                    updateCommandTree.Target.Expression.Accept((DbExpressionVisitor)selectSqlGenerator);
                    updateCommandTree.Returning.Accept((DbExpressionVisitor)selectSqlGenerator);
                    updateCommandTree.Predicate.Accept((DbExpressionVisitor)selectSqlGenerator);
                }
                sql.AppendLine(selectSqlGenerator.Sql);
                sql.AppendLine();
            }
            DmlFunctionSqlGenerator.AppendSetRowsAffected(sql, rowsAffectedParameter);
            return(sql.ToString().TrimEnd());
        }
        internal static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion)
        {
            bool flag1 = false;

            if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null)
            {
                HashSet <EdmMember> edmMemberSet = new HashSet <EdmMember>(tree.SetClauses.Cast <DbSetClause>().Select <DbSetClause, EdmMember>((Func <DbSetClause, EdmMember>)(s => ((DbPropertyExpression)s.Property).Property)));
                bool flag2 = false;
                foreach (EdmMember keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers)
                {
                    if (!edmMemberSet.Contains(keyMember))
                    {
                        if (flag2)
                        {
                            flag1 = true;
                            break;
                        }
                        flag2 = true;
                        if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage))
                        {
                            flag1 = true;
                            break;
                        }
                    }
                }
            }
            return(flag1);
        }
        public string GenerateUpdate(ICollection <DbUpdateCommandTree> commandTrees, string rowsAffectedParameter)
        {
            DebugCheck.NotNull(commandTrees);

            if (!commandTrees.Any())
            {
                return(null);
            }

            List <SqlParameter> _;

            var sql = new StringBuilder();

            sql.AppendLine(
                DmlSqlGenerator.GenerateUpdateSql(
                    commandTrees.First(),
                    _sqlGenerator,
                    out _,
                    generateReturningSql: false));

            foreach (var commandTree in commandTrees.Skip(1))
            {
                sql.Append(
                    DmlSqlGenerator.GenerateUpdateSql(
                        commandTree,
                        _sqlGenerator,
                        out _,
                        generateReturningSql: false));

                sql.AppendLine("AND @@ROWCOUNT > 0");
                sql.AppendLine();
            }

            var returningCommandTrees
                = commandTrees
                  .Where(ct => ct.Returning != null)
                  .ToList();

            if (returningCommandTrees.Any())
            {
                var returningSelectSqlGenerator = new ReturningSelectSqlGenerator();

                foreach (var commandTree in returningCommandTrees)
                {
                    commandTree.Target.Expression.Accept(returningSelectSqlGenerator);
                    commandTree.Returning.Accept(returningSelectSqlGenerator);
                    commandTree.Predicate.Accept(returningSelectSqlGenerator);
                }

                sql.AppendLine(returningSelectSqlGenerator.Sql);
                sql.AppendLine();
            }

            AppendSetRowsAffected(sql, rowsAffectedParameter);

            return(sql.ToString().TrimEnd());
        }
 public override void Visit(DbPropertyExpression expression)
 {
     Check.NotNull <DbPropertyExpression>(expression, nameof(expression));
     if (!string.IsNullOrEmpty(this.PropertyAlias))
     {
         this._commandText.Append(this.PropertyAlias);
         this._commandText.Append(".");
     }
     this._commandText.Append(DmlSqlGenerator.GenerateMemberTSql(expression.Property));
 }
        internal static string GenerateUpdateSql(
            DbUpdateCommandTree tree,
            SqlGenerator sqlGenerator,
            out List <SqlParameter> parameters,
            bool generateReturningSql = true,
            bool upperCaseKeywords    = true)
        {
            SqlStringBuilder commandText = new SqlStringBuilder(256)
            {
                UpperCaseKeywords = upperCaseKeywords
            };

            DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, null != tree.Returning, sqlGenerator, (ICollection <EdmProperty>)null, true);
            if (tree.SetClauses.Count == 0)
            {
                commandText.AppendKeyword("declare ");
                commandText.AppendLine("@p int");
            }
            commandText.AppendKeyword("update ");
            tree.Target.Expression.Accept((DbExpressionVisitor)translator);
            commandText.AppendLine();
            bool flag = true;

            commandText.AppendKeyword("set ");
            foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses)
            {
                if (flag)
                {
                    flag = false;
                }
                else
                {
                    commandText.Append(", ");
                }
                setClause.Property.Accept((DbExpressionVisitor)translator);
                commandText.Append(" = ");
                setClause.Value.Accept((DbExpressionVisitor)translator);
            }
            if (flag)
            {
                commandText.Append("@p = 0");
            }
            commandText.AppendLine();
            commandText.AppendKeyword("where ");
            tree.Predicate.Accept((DbExpressionVisitor)translator);
            commandText.AppendLine();
            if (generateReturningSql)
            {
                DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)tree, (EntityType)null, translator, tree.Returning, false);
            }
            parameters = translator.Parameters;
            return(commandText.ToString());
        }
        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());
        }
        public string GenerateDelete(
            ICollection <DbDeleteCommandTree> commandTrees,
            string rowsAffectedParameter)
        {
            StringBuilder       sql = new StringBuilder();
            List <SqlParameter> parameters;

            sql.AppendLine(DmlSqlGenerator.GenerateDeleteSql(commandTrees.First <DbDeleteCommandTree>(), this._sqlGenerator, out parameters, true, true));
            sql.AppendLine();
            foreach (DbDeleteCommandTree tree in commandTrees.Skip <DbDeleteCommandTree>(1))
            {
                sql.AppendLine(DmlSqlGenerator.GenerateDeleteSql(tree, this._sqlGenerator, out parameters, true, true));
                sql.AppendLine("AND @@ROWCOUNT > 0");
                sql.AppendLine();
            }
            DmlFunctionSqlGenerator.AppendSetRowsAffected(sql, rowsAffectedParameter);
            return(sql.ToString().TrimEnd());
        }
        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 string GenerateInsert(ICollection <DbInsertCommandTree> commandTrees)
        {
            DebugCheck.NotNull(commandTrees);

            var sql = new StringBuilder();

            List <SqlParameter> _;

            var firstCommandTree = commandTrees.First();

            sql.Append(
                DmlSqlGenerator.GenerateInsertSql(
                    firstCommandTree,
                    _sqlGenerator,
                    out _,
                    generateReturningSql: false,
                    createParameters: false));

            sql.AppendLine();

            var firstTable
                = (EntityType)((DbScanExpression)firstCommandTree.Target.Expression).Target.ElementType;

            sql.Append(IntroduceRequiredLocalVariables(firstTable, firstCommandTree));

            foreach (var commandTree in commandTrees.Skip(1))
            {
                sql.Append(
                    DmlSqlGenerator.GenerateInsertSql(
                        commandTree,
                        _sqlGenerator,
                        out _,
                        generateReturningSql: false,
                        createParameters: false));

                sql.AppendLine();
            }

            var returningCommandTrees
                = commandTrees
                  .Where(ct => ct.Returning != null)
                  .ToList();

            if (returningCommandTrees.Any())
            {
                var returningSelectSqlGenerator = new ReturningSelectSqlGenerator();

                foreach (var commandTree in returningCommandTrees)
                {
                    commandTree.Target.Expression.Accept(returningSelectSqlGenerator);
                    commandTree.Returning.Accept(returningSelectSqlGenerator);
                }

                foreach (var keyProperty in firstTable.KeyProperties)
                {
                    var parameterReference
                        = firstCommandTree
                          .SetClauses
                          .Cast <DbSetClause>()
                          .Where(sc => ((DbPropertyExpression)sc.Property).Property == keyProperty)
                          .Select(sc => sc.Value)
                          .SingleOrDefault()
                          ?? keyProperty.TypeUsage.Parameter(keyProperty.Name);

                    firstCommandTree
                    .Target
                    .Variable
                    .Property(keyProperty)
                    .Equal(parameterReference)
                    .Accept(returningSelectSqlGenerator);
                }

                sql.Append(returningSelectSqlGenerator.Sql);
            }

            return(sql.ToString().TrimEnd());
        }
        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());
        }
 internal static void GenerateReturningSql(
     SqlStringBuilder commandText,
     DbModificationCommandTree tree,
     EntityType tableType,
     DmlSqlGenerator.ExpressionTranslator translator,
     DbExpression returning,
     bool useGeneratedValuesVariable)
 {
     if (returning == null)
     {
         return;
     }
     commandText.AppendKeyword("select ");
     if (useGeneratedValuesVariable)
     {
         translator.PropertyAlias = "t";
     }
     returning.Accept((DbExpressionVisitor)translator);
     if (useGeneratedValuesVariable)
     {
         translator.PropertyAlias = (string)null;
     }
     commandText.AppendLine();
     if (useGeneratedValuesVariable)
     {
         commandText.AppendKeyword("from ");
         commandText.Append("@generated_keys");
         commandText.AppendKeyword(" as ");
         commandText.Append("g");
         commandText.AppendKeyword(" join ");
         tree.Target.Expression.Accept((DbExpressionVisitor)translator);
         commandText.AppendKeyword(" as ");
         commandText.Append("t");
         commandText.AppendKeyword(" on ");
         string keyword = string.Empty;
         foreach (EdmMember keyMember in tableType.KeyMembers)
         {
             commandText.AppendKeyword(keyword);
             keyword = " and ";
             commandText.Append("g.");
             string memberTsql = DmlSqlGenerator.GenerateMemberTSql(keyMember);
             commandText.Append(memberTsql);
             commandText.Append(" = t.");
             commandText.Append(memberTsql);
         }
         commandText.AppendLine();
         commandText.AppendKeyword("where @@ROWCOUNT > 0");
     }
     else
     {
         commandText.AppendKeyword("from ");
         tree.Target.Expression.Accept((DbExpressionVisitor)translator);
         commandText.AppendLine();
         commandText.AppendKeyword("where @@ROWCOUNT > 0");
         EntitySetBase target = ((DbScanExpression)tree.Target.Expression).Target;
         bool          flag   = false;
         foreach (EdmMember keyMember in target.ElementType.KeyMembers)
         {
             commandText.AppendKeyword(" and ");
             commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember));
             commandText.Append(" = ");
             SqlParameter sqlParameter;
             if (translator.MemberValues.TryGetValue(keyMember, out sqlParameter))
             {
                 commandText.Append(sqlParameter.ParameterName);
             }
             else
             {
                 if (flag)
                 {
                     throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey((object)target.Name));
                 }
                 if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage))
                 {
                     throw new InvalidOperationException(Strings.Update_NotSupportedIdentityType((object)keyMember.Name, (object)keyMember.TypeUsage.ToString()));
                 }
                 commandText.Append("scope_identity()");
                 flag = true;
             }
         }
     }
 }