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]
");
}
