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