public void SelectAll()
{
var actualSelect = _sql.From(TableName)
.Select()
.ToString();
var expectedSelect = Regex.Escape($"SELECT * FROM {TableName}");
Assert.That(Regex.Escape(actualSelect), Is.EqualTo(expectedSelect));
}
public void OrderByAscending(params string[] fields)
{
var actualSelect = _sql.From(TableName)
.Select()
.OrderBy(fields)
.ToString();
Console.WriteLine(actualSelect);
var expectedSelect = Regex.Escape($"SELECT * FROM {TableName}{Environment.NewLine}" +
$"ORDER BY {string.Join(" ASC, ", fields) + " ASC"}");
Assert.That(Regex.Escape(actualSelect), Is.EqualTo(expectedSelect));
}
public void TestMethod5()
{
var where = _sql1
.From(AsExp("nbe.BANK_DATA", "B1"), true)
.Join(AsExp("nbe.BANK_DATA", "B2"), ConstantExpCol("B1.REC_ID") == "B2.REC_ID")
.Where(
ConstantExpCol("B1.AMOUNT") < 35000 &
!InExp("B1.REC_ID", SelectTopExp(FromExp("nbe.BANK_DATA"), 4, "REC_ID")) &
!!!LikeExp("B1.RESTRICT_CODE", "%dd%") &
ConstantExpCol("B1.ADD_DATE") > "@date"
, Params(Param("@date", new DateTime(2012, 1, 1)))
);
var actualSelect1 = where.Select()
.ToString();
var actualSelect2 = where.SelectTop(11)
.OrderByDescending("B1.AMOUNT")
.ToString();
string expectedSelect1 = Regex.Escape(string.Format(
"SELECT * FROM nbe.BANK_DATA AS [B1] WITH(NOLOCK){0}" +
"INNER JOIN nbe.BANK_DATA AS [B2] ON B1.REC_ID = B2.REC_ID{0}" +
"WHERE ((( B1.AMOUNT < 35000 AND B1.REC_ID NOT IN ( SELECT TOP 4 REC_ID FROM nbe.BANK_DATA )) AND B1.RESTRICT_CODE NOT LIKE '%dd%' ) AND B1.ADD_DATE > @date )",
Environment.NewLine));
string expectedSelect2 = Regex.Escape(string.Format(
"SELECT TOP 11 * FROM nbe.BANK_DATA AS [B1] WITH(NOLOCK){0}" +
"INNER JOIN nbe.BANK_DATA AS [B2] ON B1.REC_ID = B2.REC_ID{0}" +
"WHERE ((( B1.AMOUNT < 35000 AND B1.REC_ID NOT IN ( SELECT TOP 4 REC_ID FROM nbe.BANK_DATA )) AND B1.RESTRICT_CODE NOT LIKE '%dd%' ) AND B1.ADD_DATE > @date ){0}" +
"ORDER BY B1.AMOUNT DESC",
Environment.NewLine));
Assert.Multiple(
() =>
{
Assert.That(Regex.Escape(actualSelect1), Is.EqualTo(expectedSelect1));
Assert.That(Regex.Escape(actualSelect2), Is.EqualTo(expectedSelect2));
});
}
public void SkipInSelect(int offset, int?fetchNext)
{
var actualSelect = _sql.From(TableName)
.Select()
.Skip(offset, fetchNext)
.ToString();
Console.WriteLine(actualSelect);
var expectedSelect = Regex.Escape($"SELECT * FROM {TableName}{Environment.NewLine}" +
$"OFFSET {offset} ROWS" +
(fetchNext != null
? $" FETCH NEXT {fetchNext} ROWS ONLY"
: ""));
Assert.That(Regex.Escape(actualSelect), Is.EqualTo(expectedSelect));
}
public void InnerJoin(string joinToTable, string firstTableMatchingCol, string secondTableMatchingCol)
{
var actualSelect1 = _sql.From(TableName)
.Join(joinToTable, ConstantExpCol(firstTableMatchingCol) == secondTableMatchingCol)
.Select()
.ToString();
var actualSelect2 = _sql.From(TableName)
.Join(joinToTable, firstTableMatchingCol, secondTableMatchingCol)
.Select()
.ToString();
var expectedSelect = Regex.Escape($"SELECT * FROM {TableName}{Environment.NewLine}" +
$"INNER JOIN {joinToTable} ON {firstTableMatchingCol} = {secondTableMatchingCol}");
Assert.Multiple(
() =>
{
Assert.That(Regex.Escape(actualSelect1), Is.EqualTo(expectedSelect));
Assert.That(Regex.Escape(actualSelect2), Is.EqualTo(expectedSelect));
}
);
}
