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; "); }
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 "); }
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(); }
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 } } }
public void SelectSingleColumnCreatesSingleExpSelectStatement() { var u = new UserTable(); var q = new SharpQuery(); var stm = q.Select(u.Id).From(u).Statement; Assert.IsInstanceOf(typeof(SingleExprSelectStatement), stm); }
public void InsertWithMultipleValues() { var u = new UserTable(); var q = new SharpQuery(); q.InsertInto(u, u.Id, u.FirstName, u.Email) .Values(1, "a", "b") .Values(2, "a", "b") .Values(3, "a", "b") .EndStatement(); var str = q.ToString(); }
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); }
public void GetMatchingColumnsFor() { var table = new UserTable(); var cols = table.GetMatchingColumnsFor(typeof(User)); var expected = new[] { table.Id, table.Email, table.FirstName }; CollectionAssert.AreEquivalent(expected, cols); }
public void InsertIntoTable() { var u = new UserTable(); var q = new SharpQuery(); q.InsertInto(u, u.Email, u.FirstName).Values("*****@*****.**", "username").EndStatement(); TSqlAssert.ScriptsAreEqual(q.ToString(), @" INSERT INTO [dbo].[User]( [dbo].[User].[Email] , [dbo].[User].[Name]) VALUES( @p0 , @p1 ) "); }
public void OrderBy() { var q = new SharpQuery(); var u = new UserTable(); q.Select(u.AllColumns).From(u).EndStatement(); q.OrderByAsc(u.Id, u.FirstName).OrderByDesc(u.Email).EndStatement(); TSqlAssert.ScriptsAreEqual(q.ToString(), @" SELECT [dbo].[User].[Id] , [dbo].[User].[Name] , [dbo].[User].[Email] FROM [dbo].[User] ORDER BY [dbo].[User].[Id] ASC , [dbo].[User].[Name] ASC , [dbo].[User].[Email] DESC "); }
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]"); }
public void SelectAliasedColumnFromTable() { var u = new UserTable(); var q = new SharpQuery(); q.Select(u.FirstName.As("Username")).From(u).EndStatement(); var script = q.ToString(); TSqlAssert.ScriptsAreEqual(script, @" SELECT [dbo].[User].[Name] AS [Username] FROM [dbo].[User] "); }
public void SelectSingleColumnFromAliasedTable() { var u = new UserTable("u"); var q = new SharpQuery(); q.Select(u.FirstName).From(u).EndStatement(); var script = q.ToString(); TSqlAssert.ScriptsAreEqual(script, @" SELECT [u].[Name] FROM [dbo].[User] AS [u] "); }
public void FalsePredicate() { var q = new SharpQuery(); var u = new UserTable(); q.Select(1).From(u).Where(Predicate.FALSE).EndStatement(); TSqlAssert.ScriptsAreEqual(q.ToString(), @" SELECT @p0 FROM [dbo].[User] WHERE 1 <> 1; "); }
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] "); }
public void SelectSingleColumnFromTable() { /*? <span class='title'>Example</span> * **Description: Returns whether event.stopImmediatePropagation() was ever called on this event object. * <span>This method does not accept any arguments.</span> * Checks whether event.stopImmediatePropagation() was called. * */ var u = new UserTable(); var q = new SharpQuery(); q.Select(u.FirstName).From(u).EndStatement(); var script = q.ToString(); TSqlAssert.ScriptsAreEqual(script, @" SELECT [dbo].[User].[Name] FROM [dbo].[User] "); }
public void SelectWithWhere() { var u = new UserTable(); var q = new SharpQuery(); q.Select(u.FirstName).From(u).Where(u.Id.IsEqualTo(1)).EndStatement(); var script = q.ToString(); Console.WriteLine(script); TSqlAssert.ScriptsAreEqual(script, @" SELECT [dbo].[User].[Name] FROM [dbo].[User] WHERE [dbo].[User].[Id] = @p0 "); }
public void SelectAllColumnsFromTable() { var q = new SharpQuery(); var u = new UserTable(); q.Select(u.AllColumns).From(u).EndStatement(); TSqlAssert.ScriptsAreEqual(q.ToString(), @" SELECT [dbo].[User].[Id] , [dbo].[User].[Name] , [dbo].[User].[Email] FROM [dbo].[User] "); }