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