public void ResolveSqlNoParams()
{
var query = new ActiveEmployees();
string sql = QueryUtil.ResolveSql(query.Sql, query);
Assert.IsTrue(sql.Equals("SELECT * FROM [Employee] WHERE [IsActive]=@isActive ORDER BY [Nothing]"));
}
public void ResolveSqlNoCriteria()
{
string query = "SELECT * FROM [Whatever] {where} ORDER BY [Nothing]";
string result = QueryUtil.ResolveSql(query);
Assert.IsTrue(result.Equals("SELECT * FROM [Whatever] ORDER BY [Nothing]"));
}
public void ResolveSqlWithQuery()
{
var query = new ActiveEmployees()
{
LastName = "hello"
};
string sql = QueryUtil.ResolveSql(query.Sql, query);
Assert.IsTrue(sql.Equals("SELECT * FROM [Employee] WHERE [IsActive]=@isActive AND [LastName] LIKE @lastName ORDER BY [Nothing]"));
}
public void ResolveSqlWithCase()
{
string query = "SELECT * FROM [Whatever] {where} ORDER BY [Nothing]";
string result = QueryUtil.ResolveSql(query, new Criteria()
{
Level = 2
});
Assert.IsTrue(result.Equals("SELECT * FROM [Whatever] WHERE [Level]>=10 AND [Level]<20 ORDER BY [Nothing]"));
}
public void ResolveSqlWithWhere()
{
string query = "SELECT * FROM [Whatever] {where} ORDER BY [Nothing]";
string result = QueryUtil.ResolveSql(query, new Criteria()
{
LastName = "hello"
});
Assert.IsTrue(result.Equals("SELECT * FROM [Whatever] WHERE [LastName] LIKE @lastName ORDER BY [Nothing]"));
}
public void ResolveSqlWithParams()
{
var query = new ActiveEmployees()
{
LastName = "hello", IsActive = true
};
string sql = QueryUtil.ResolveSql(query.Sql, query, out DynamicParameters queryParams);
Assert.IsTrue(queryParams.Get <string>("LastName").Equals("hello"));
Assert.IsTrue(queryParams.Get <bool>("IsActive").Equals(true));
}
public void PagingQueryWithoutPage()
{
var query = new PagingQuery();
string sql = QueryUtil.ResolveSql(query.Sql, query);
Assert.IsTrue(sql.Equals(@"SELECT
[dr].*,
ROW_NUMBER() OVER (ORDER BY [Date]) AS [RowNumber]
FROM
dbo.FnDateRange('4/1/19', '5/1/19') [dr]
ORDER BY
[Date]
" ));
}
public void PagingQueryWithAnyPage()
{
var query = new PagingQuery()
{
Page = 3
};
string sql = QueryUtil.ResolveSql(query.Sql, query);
Assert.IsTrue(sql.Equals(@"SELECT
[dr].*,
ROW_NUMBER() OVER (ORDER BY [Date]) AS [RowNumber]
FROM
dbo.FnDateRange('4/1/19', '5/1/19') [dr]
ORDER BY
[Date]
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY" ));
}
public FileResult OnGetExcelDownload()
{
// get the query this dashboard uses
var qry = GetQuery();
// get the query's underlying SQL, resolving conditional criteria and parameters
var sql = QueryUtil.ResolveSql(qry.Sql, qry);
using (var cn = Data.GetConnection())
{
// query the data and return a data table
var data = cn.QueryTableWithParams(sql, qry);
data.TableName = "Open Work Items";
var wb = new ClosedXML.Excel.XLWorkbook();
var ws = wb.AddWorksheet(data);
return(wb.Deliver("OpenWorkItems.xlsx"));
}
}
