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