public void QueryInjectCriteriaNone() { var query = new TypicalQuery(); var sql = QueryHelper.ResolveSql(query.Sql, query); Assert.IsTrue(sql.Equals("SELECT [FirstName], [Weight], [SomeDate], [Notes], [Id] FROM [SampleTable] <<macro>> ORDER BY [FirstName]")); }
public void QueryWithTrace() { using (var cn = LocalDb.GetConnection(dbName)) { List <QueryTrace> traces = new List <QueryTrace>(); var results = new TypicalQuery().ExecuteAsync(cn, traces: traces).Result; Assert.IsTrue(traces.Count() == 1); } }
public void QueryDefaultPageSize() { var qry = new TypicalQuery(); qry.PageNumber = 10; var sql = qry.ResolveSql(); Assert.IsTrue(sql.Equals("SELECT [FirstName], [Weight], [SomeDate], [Notes], [Id] FROM [SampleTable] <<macro>> ORDER BY [FirstName] OFFSET 200 ROWS FETCH NEXT 20 ROWS ONLY")); }
public void QueryInjectCriteria() { var query = new TypicalQuery() { FirstNameLike = "arxo" }; var sql = QueryHelper.ResolveSql(query.Sql, query); Assert.IsTrue(sql.Equals("SELECT [FirstName], [Weight], [SomeDate], [Notes], [Id] FROM [SampleTable] WHERE [FirstName] LIKE '%'+@firstNameLike+'%' <<macro>> ORDER BY [FirstName]")); }
public void OffsetQuery() { using (var cn = LocalDb.GetConnection(dbName)) { var results = new TypicalQuery() { PageNumber = 4 }.Execute(cn); Debug.Print($"result count = {results.Count()}"); Assert.IsTrue(results.Count() == 20); } }
public void OffsetQueryNewPageSize() { using (var cn = LocalDb.GetConnection(dbName)) { var results = new TypicalQuery() { PageNumber = 4 }.ExecuteAsync(cn, newPageSize: 10).Result; Debug.Print($"result count = {results.Count()}"); Assert.IsTrue(results.Count() == 10); } }
public void PhraseQuery() { using (var cn = LocalDb.GetConnection(dbName)) { var qry = new TypicalQuery() { NotesContain = "this whatever" }; var results = qry.Execute(cn); Debug.Print(qry.ResolvedSql); // for my own curiosity when running locally Assert.IsTrue(results.Any()); } }
public void ParamDeclarations() { var qry = new TypicalQuery() { MinWeight = 12, MaxWeight = 36, FirstNameLike = "yohoo", MinDate = new DateTime(2020, 1, 15) }; QueryHelper.ResolveSql(qry.Sql, qry, out DynamicParameters queryParms); var syntax = QueryHelper.ResolveParams(qry, queryParms); Assert.IsTrue(syntax.ReplaceWhitespace().Equals( @"DECLARE @MinWeight decimal, @MaxWeight decimal, @FirstNameLike nvarchar(max), @MinDate datetime; SET @MinWeight = 12; SET @MaxWeight = 36; SET @FirstNameLike = 'yohoo'; SET @MinDate = '1/15/2020 12:00:00 AM';".ReplaceWhitespace())); }
public void DebugSql() { var qry = new TypicalQuery() { MinWeight = 9, MaxWeight = 56, FirstNameLike = "warbler", MinDate = new DateTime(2020, 1, 15) }; qry.ResolveSql(removeMacros: true); string debug = qry.DebugSql; Assert.IsTrue(debug.ReplaceWhitespace().Equals( @"DECLARE @MinWeight decimal, @MaxWeight decimal, @FirstNameLike nvarchar(max), @MinDate datetime; SET @MinWeight = 9; SET @MaxWeight = 56; SET @FirstNameLike = 'warbler'; SET @MinDate = '1/15/2020 12:00:00 AM'; SELECT [FirstName], [Weight], [SomeDate], [Notes], [Id] FROM [SampleTable] WHERE [Weight]>=@minWeight AND [Weight]<=@maxWeight AND [FirstName] LIKE '%'+@firstNameLike+'%' AND [SomeDate]>=@minDate ORDER BY [FirstName]".ReplaceWhitespace())); }