Exemple #1
0
        public void MultipleJoins()
        {
            var u = new UserTable();
            var p1 = new PostTable("p1");
            var p2 = new PostTable("p2");
            var tv = new CustomTableValuedFunction();

            var q = new SharpQuery();
            q.Select(u.AllColumns)
                .From(u)
                .InnerJoin(p1, p1.UserId.IsEqualTo(u.Id))
                .InnerJoin(p2, Predicate.TRUE)
                .CrossApply(tv, Predicate.TRUE)
                .LeftOuterJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .CrossApply(tv, Predicate.TRUE)
                .LeftOuterJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .EndStatement();
        }
Exemple #2
0
        public void InnerJoin()
        {
            var u = new UserTable();
            var p = new PostTable();

            var q = new SharpQuery();

            q.Select(p.AllColumns)
                .From(p)
                .InnerJoin(u, u.Id.IsEqualTo(p.UserId))
                .Where(u.FirstName.IsEqualTo("Mike"))
                .EndStatement();
            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, @"
            SELECT
            [dbo].[Post].[Id]
              , [dbo].[Post].[UserId]
              , [dbo].[Post].[Title]
              , [dbo].[Post].[Text]FROM
            [dbo].[Post]
            INNER JOIN [dbo].[User] ON [dbo].[User].[Id] = [dbo].[Post].[UserId]WHERE
            [dbo].[User].[Name] =  @p0
            ");
        }
Exemple #3
0
        public void CodeUsingSharpQL()
        {
            var u = new UserTable();
            var p = new PostTable();

            var q = new SharpQuery();
            var condition = Predicate.TRUE;
            if (string.IsNullOrWhiteSpace(this.Email)) {
                condition &= u.Email.IsEqualTo(this.Email);
            }

            if (this.Date.HasValue) {
                condition &= p.PostDate.IsGreaterThanOrEqualTo(this.Date);
            }

            q.Select(p.Id, p.Title, p.Text, p.PostDate, u.Email.As("UserMail"))
                .From(p)
                .InnerJoin(u, p.UserId.IsEqualTo(u.Id))
                .Where(condition)
                .EndStatement()
                .OrderByDesc(p.PostDate)
                .EndStatement();

            using (var connection = new SqlConnection("connectionString")) {
                var cmd = q.CreateCommand(connection);
                using (var reader = cmd.ExecuteReader()) {
                    // code to read data goes here
                }
            }
        }
Exemple #4
0
        public void Having()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            int userId = 1;
            q.Select(u.Id, Sql.Count(1))
                .From(u)
                .InnerJoin(p, u.Id.IsEqualTo(p.UserId))
                .Where(u.Id.IsEqualTo(userId) & p.PostDate.IsGreaterThanOrEqualTo(DateTime.Now.AddDays(-7)))
                .GroupBy(u.Id)
                .Having(Sql.Count(1).IsGreaterThan(10))
                .EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            SELECT   [dbo].[User].[Id],
             COUNT(@p0)
            FROM     [dbo].[User]
             INNER JOIN
             [dbo].[Post]
             ON [dbo].[User].[Id] = [dbo].[Post].[UserId]
            WHERE    ([dbo].[User].[Id] = @p0
              AND [dbo].[Post].[PostDate] >= @p1)
            GROUP BY [dbo].[User].[Id]
            HAVING   COUNT(@p0) > @p2;
            ");
        }
Exemple #5
0
        public void QueryAlwaysGeneratesTheSameScript()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            q.Select(u.FirstName, u.Email, p.Title).From(u).LeftOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();

            var script1 = q.ToString();
            var script2 = q.ToString();

            TSqlAssert.ScriptsAreEqual(script1, script2);
        }
Exemple #6
0
        public void Test1()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            q.Select(u.Id, Sql.Count(1))
                .From(u)
                .InnerJoin(p, p.UserId.IsEqualTo(u.Id))
                .GroupBy(u.Id)
                .EndStatement();
            var query = q.ToString();

            TSqlAssert.ScriptsAreEqual(query, @"
            SELECT
            [dbo].[User].[Id]
              , COUNT( @p0  )
            FROM [dbo].[User]
            INNER JOIN [dbo].[Post] ON [dbo].[Post].[UserId] = [dbo].[User].[Id]
            GROUP BY
            [dbo].[User].[Id]");
        }
Exemple #7
0
        public void RightOuterJoin()
        {
            var u = new UserTable();
            var p = new PostTable();

            var q = new SharpQuery();

            q.Select(u.FirstName, p.Title).From(u).RightOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();

            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, @"
            SELECT
            [dbo].[User].[Name]
              , [dbo].[Post].[Title]FROM
            [dbo].[User]RIGHT OUTER JOIN
            [dbo].[Post] ON [dbo].[Post].[UserId] = [dbo].[User].[Id]
            ");
        }