public void SQLinqIf_004()
{
var query = (from d in new SQLinq<Person>()
where d.Age >= 18
select d);
var @if = new SQLinqIf(SQLinqIfOperator.Not, "1 = 1");
@if.Then = query;
var actual = @if.ToSQL();
var actualQuery = actual.ToQuery();
var expectedQuery = @"IF (NOT(1 = 1))
BEGIN
SELECT [ID], [FirstName], [LastName], [Age], [Is_Employed] AS [IsEmployed], [ParentID], [Column With Spaces] AS [ColumnWithSpaces] FROM [Person] WHERE [Age] >= @sqlinq_1
END
";
Assert.AreEqual(expectedQuery, actualQuery);
}
public void SQLinqIf_005()
{
var @if = new SQLinqIf(SQLinqIfOperator.Exists, (from d in new SQLinq<Person>()
where d.Age >= 19
select d));
@if.Then = (from d in new SQLinq<Person>()
where d.Age >= 18
select d);
var result = @if.ToSQL();
Assert.AreEqual(2, result.Parameters.Count);
Assert.AreEqual(19, result.Parameters["@sqlinq_1"]);
Assert.AreEqual(18, result.Parameters["@sqlinq_2"]);
}
public void SQLinqIf_009()
{
var query = (from d in new SQLinq<Person>()
where d.Age >= 18
select d);
var @if = new SQLinqIf(SQLinqIfOperator.Exists, query);
@if.Then = new DynamicSQLinq("Car")
.Where("Make = @0", "Ford")
.Where("Color = @0 OR Color = @1", 0, 1);
var actual = @if.ToSQL();
var actualQuery = actual.ToQuery();
var expectedQuery = @"IF (EXISTS(SELECT [ID], [FirstName], [LastName], [Age], [Is_Employed] AS [IsEmployed], [ParentID], [Column With Spaces] AS [ColumnWithSpaces] FROM [Person] WHERE [Age] >= @sqlinq_1))
BEGIN
SELECT * FROM Car WHERE (Make = @sqlinq_2) AND (Color = @sqlinq_3 OR Color = @sqlinq_4)
END
";
Assert.AreEqual(expectedQuery, actualQuery);
Assert.AreEqual(18, actual.Parameters["@sqlinq_1"]);
Assert.AreEqual("Ford", actual.Parameters["@sqlinq_2"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_3"]);
Assert.AreEqual(1, actual.Parameters["@sqlinq_4"]);
}
public void SQLinqIf_012()
{
var personId = Guid.NewGuid();
var @if = new SQLinqIf(SQLinqIfOperator.Exists, from p in new SQLinq<Person>()
where p.ID == personId
select p.ID);
@if.Then = new SQLinqUpdate<Person>(new Person());
@if.Else = new SQLinqInsert<Person>(new Person());
var actual = @if.ToSQL();
var actualQuery = actual.ToQuery();
var expectedQuery = @"IF (EXISTS(SELECT [ID] FROM [Person] WHERE [ID] = @sqlinq_1))
BEGIN
UPDATE [Person] SET [ID] = @sqlinq_2, [FirstName] = @sqlinq_3, [LastName] = @sqlinq_4, [Age] = @sqlinq_5, [Is_Employed] = @sqlinq_6, [ParentID] = @sqlinq_7, [Column With Spaces] = @sqlinq_8
END
ELSE
BEGIN
INSERT [Person] ([ID], [FirstName], [LastName], [Age], [Is_Employed], [ParentID], [Column With Spaces]) VALUES (@sqlinq_9, @sqlinq_10, @sqlinq_11, @sqlinq_12, @sqlinq_13, @sqlinq_14, @sqlinq_15)
END
";
Assert.AreEqual(expectedQuery, actualQuery);
Assert.AreEqual(15, actual.Parameters.Count);
Assert.AreEqual(personId, actual.Parameters["@sqlinq_1"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_2"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_3"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_4"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_5"]);
Assert.AreEqual(false, actual.Parameters["@sqlinq_6"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_7"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_8"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_9"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_10"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_11"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_12"]);
Assert.AreEqual(false, actual.Parameters["@sqlinq_13"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_14"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_15"]);
}
public void SQLinqIf_011()
{
var personId = Guid.NewGuid();
var @if = new SQLinqIf(SQLinqIfOperator.Exists, from p in new SQLinq<Person>()
where p.ID == personId
select p.ID);
@if.Then = new SQLinqInsert<Person>(new Person());
var actual = @if.ToSQL();
var actualQuery = actual.ToQuery();
var expectedQuery = @"IF (EXISTS(SELECT [ID] FROM [Person] WHERE [ID] = @sqlinq_1))
BEGIN
INSERT [Person] ([ID], [FirstName], [LastName], [Age], [Is_Employed], [ParentID], [Column With Spaces]) VALUES (@sqlinq_2, @sqlinq_3, @sqlinq_4, @sqlinq_5, @sqlinq_6, @sqlinq_7, @sqlinq_8);SELECT SCOPE_IDENTITY()
END
";
Assert.AreEqual(expectedQuery, actualQuery);
Assert.AreEqual(8, actual.Parameters.Count);
Assert.AreEqual(personId, actual.Parameters["@sqlinq_1"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_2"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_3"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_4"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_5"]);
Assert.AreEqual(false, actual.Parameters["@sqlinq_6"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_7"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_8"]);
}