コード例 #1
0
        public void OrderQueryFact()
        {
            string expectedTemplate = "SELECT * FROM dbo.from f ";
            var    fromTable        = new Table("dbo.from", "f");
            var    joinTable        = new Table("dbo.join", "j");

            var builder = new SqlExpressionBuilderSelect();

            builder
            .From(fromTable)
            .Order("bla")
            .Order("blubb", OrderOptions.Desc);

            string expected = expectedTemplate + "ORDER BY f.bla ASC, f.blubb DESC";

            Assert.Equal(expected, builder.ToString());

            // Fails
            bool throws = false;

            builder = new SqlExpressionBuilderSelect();
            builder.From(fromTable).Order("bla");
            try
            {
                builder.Order("b.blubb", OrderOptions.Desc); // table with alias b not found
            }
            catch
            {
                throws = true;
            }
            Assert.True(throws);

            builder
            .JoinInner(joinTable, "wayne = wayne")
            .Order("j.blubb", OrderOptions.Desc)
            .Order("j.foo", ExpressionOptions.Overwrite)
            .Order(new ColumnExpression("bla", fromTable))
            .Order(new ColumnExpression("muh", fromTable), OrderOptions.Desc);
            expectedTemplate += "INNER JOIN dbo.join j ON wayne = wayne ";
            expected          = expectedTemplate + "ORDER BY j.foo ASC, f.bla ASC, f.muh DESC";
            Assert.Equal(expected, builder.ToString());

            builder.Order(new ColumnExpression("bla", fromTable), ExpressionOptions.Overwrite);
            expected = expectedTemplate + "ORDER BY f.bla ASC";
            Assert.Equal(expected, builder.ToString());

            builder.Order(new ColumnExpression("foo", fromTable), OrderOptions.Desc, ExpressionOptions.Overwrite);
            expected = expectedTemplate + "ORDER BY f.foo DESC";
            Assert.Equal(expected, builder.ToString());

            // Fails
            throws  = false;
            builder = new SqlExpressionBuilderSelect();
            builder.From(fromTable).Order("bla");
            try
            {
                builder.Order(new ColumnExpression("foo", joinTable)); // table does not exist in sql builder
            }
            catch
            {
                throws = true;
            }
            Assert.True(throws);
        }