public void Parse_Filter_LessOrEqualTo()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].LessOrEqualTo.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("5D7050823EE2EC6956E201D0400DA419", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].LessOrEqualTo.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("05069F883119759EB2718B153F573D1A", Helpers.Checksum(sql2));
}
public void Parse_Filter_Like()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].Like.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("DE63F4F1988F9EE29ABD22F382A210D3", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].Like.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("461335C8133CF486EB72CC7A2B7B5606", Helpers.Checksum(sql2));
}
public void Parse_Filter_LessTo()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].LessTo.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("8C816D6911AF6D3456AF68589502AFFE", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].LessTo.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("09864DD8F14F77517975BFDBBC9CE637", Helpers.Checksum(sql2));
}
public void Parse_Filter_GreaterOrEqualTo()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].GreaterOrEqualTo.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("34AABBD1AE39782F004A906AE1B2AB72", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].GreaterOrEqualTo.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("B3F599E6750A4CEE5E467FA1A902F705", Helpers.Checksum(sql2));
}
public void Parse_Filter_GreaterTo()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].GreaterTo.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("3AFF85B634EC75196A52F423A4308A30", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].GreaterTo.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("A69ECA081ECE81EF542FD3CF164F914E", Helpers.Checksum(sql2));
}
public void Parse_Filter_NotLike()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].NotLike.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("40DD56CBBD7FB9B8F6B8475CC9FECB26", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].NotLike.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("33D480C67D31B5769AA65E4CA87403C2", Helpers.Checksum(sql2));
}
public IEnumerable <DTOs.UserAccount> GetAllUserAccounts()
{
var query = new Query("UserAccount");
var sqlResult = _compiler.Compile(query);
return(_connection.Query <DTOs.UserAccount>(sqlResult.Sql));
}
public void Parse_Filter_EqualTo()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].EqualTo.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("4E0A97FDD8410E7E2B981D76CE1FC8C9", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].EqualTo.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("D44B3E75EA1EEAAC343E96981603CD43", Helpers.Checksum(sql2));
}
public void Parse_Filter_NotEqualTo()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Get(Model_1)
.Where(e => e["data"].NotEqualTo.Val("data1"));
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("0E80502056208D5E9BD4A68A47059921", Helpers.Checksum(sql1));
var cmd2 = con.Get(Model_1)
.Where(e => e["data"].NotEqualTo.Ref("Child_2.data"));
cmd2.ParseSQL();
SqlResult result2 = compiler.Compile(cmd2.query);
string sql2 = result2.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("296C9F346FD332C7E5ECBA70C5FEAFFA", Helpers.Checksum(sql2));
}
public void SqlServerTop()
{
var query = new Query("table").Limit(1);
var result = compiler.Compile(query);
Assert.Equal("SELECT TOP (@p0) * FROM [table]", result.Sql);
}
public void Compile_RawSql_WithLimit_ReturnsCorrectQuery()
{
var q = new Query().From("Foo as src").Limit(1);
var actual = compiler.Compile(q).ToString();
Assert.Contains("SELECT TOP (1) * FROM [Foo]", actual);
}
public void ItShouldContainJoinsWhenFormulaValid()
{
var sql = compiler.Compile(queryParser.Parse(new List <string> {
"53", "+", "183"
})).ToString();
Assert.Contains("JOIN", sql);
}
private string[] Compile(Query q)
{
return(new[]
{
_sqlsrv.Compile(q.Clone()).ToString(),
_mysql.Compile(q.Clone()).ToString(),
_pg.Compile(q.Clone()).ToString(),
});
}
public void Test_SqlKata()
{
var query = new Query("Users").Where("Id", 1).Where("Status", "Active");
SqlResult result = _compiler.Compile(query);
_iOutput.WriteLine(result.Sql);
}
public void Parse_SetCommand_Update_UsingModel()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Set(Model_0, 12)
.Value(new Model_0()
{
ID = 12, //<= this shold not take in consideration because it's primary key of the entity
Model1ID = 1,
Field1 = "value1",
Field2 = "value2",
Field3 = "value3",
Field4 = "value4",
Field5 = "value5",
});
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("ADD301250F02D5AF0240ACE77079032E", Helpers.Checksum(sql1));
}
public static SqlResult CompileWithLastIdToLong(this SqlServerCompiler compiler, Query query)
{
SqlResult result = compiler.Compile(query);
string sqlComplement = result.Sql + ";SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";
return(new SqlResult(sqlComplement, result.RawBindings));
}
public async Task <PagingModel <ProfileDto> > GetProfiles(ProfileFilter filter, int page, int pageSize)
{
Query baseQuery = new Query().From("Profiles");
if (filter != null)
{
baseQuery.WhereFilter(filter);
}
Query profilesQuery = baseQuery.Clone().Select("FirstName", "LastName", "MiddleName", "Birthday");
profilesQuery.Offset((page - 1) * pageSize);
profilesQuery.Limit(pageSize);
Query totalQuery = baseQuery.Clone().AsCount("Id");
SqlResult result = compiler.Compile(new Query[] { profilesQuery, totalQuery });
using (IDbConnection connection = dbConnectionFactory.CreateConnection())
{
using (SqlMapper.GridReader reader = await connection.QueryMultipleAsync(result.Sql, result.NamedBindings))
{
return(new PagingModel <ProfileDto>
{
Items = await reader.ReadAsync <ProfileDto>(),
Total = await reader.ReadSingleAsync <int>()
});
}
}
}
public void Postgres()
{
var compiler = new PostgresCompiler();
var helper = compiler.GetHelper();
var query = new Query()
.From(new Query("category_translations").As("t"))
.Join(new Query("categories").As("c"), j => j.On("c.Id", "t.CategoryId"))
// فعلا سطح اول
.Where("c.ParentId", Guid.Empty)
.Where("t.Culture", "fa")
.Where("c.Visible", true)
.Select("t.Id as TranslationId", "t.Title", "t.Subtitle", "t.Thumbs");
var queryString = compiler.Compile(query);
query = new Query()
.From(new Query("table").As("table_alias"))
.Select(helper.Select.NoAlias <TableModel>(false, "table_alias", "Password"));
queryString = compiler.Compile(query);
var c2 = new SqlServerCompiler();
var h2 = c2.GetHelper();
var query2 = new Query()
.From(new Query("table").As("table_alias"))
.Select(h2.Select.NoAlias <TableModel>(false, "table_alias", "Password"));
var s2 = c2.Compile(query2);
}
public string SelectQuery(GetFilter filter)
{
var compiler = new SqlServerCompiler();
var query = new Query(filter.Table).SelectRaw(filter.Columns);
if (filter.Where.Count > 0)
{
foreach (var where in filter.Where)
{
query.Where(where.Column, where.Operator, where.Value);
}
}
if (!string.IsNullOrEmpty(filter.Sort))
{
query.OrderByRaw(filter.Sort);
}
if (filter.Join.Count > 0)
{
foreach (var join in filter.Join)
{
query.Join(join.JoinTable, join.SourceColumn, join.JoinColumn);
}
}
return(compiler.Compile(query).ToString());
}
public static string Compile <T>(this T query) where T : ICompilable
{
var q = Unbox(query).KataQuery;
var compiler = new SqlServerCompiler();
return(compiler.Compile(q).Sql);
}
public void Parse_SetCommand_Update()
{
Storm storm = new Storm();
storm.EditSchema(SampleSchema);
var compiler = new SqlServerCompiler();
var con = storm.OpenConnection(new EmptyConnection());
var cmd1 = con.Set(Model_0, 12)
.Value(new Dictionary <string, object>()
{
{ "ID", "asd" }, //<= this shold not take in consideration because it's primary key of the entity
{ "Model1ID", "1" },
{ "Field1", "value1" },
{ "Field2", "value2" },
{ "Field3", "value3" },
{ "Field4", "value4" },
{ "Field5", "value5" },
{ "NotExistingField", "some value" } // <= this shold not take in consideration because it's not a field of the entity
});
cmd1.ParseSQL();
SqlResult result1 = compiler.Compile(cmd1.query);
string sql1 = result1.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("ADD301250F02D5AF0240ACE77079032E", Helpers.Checksum(sql1));
}
public static SqlResult CompileWithLastIdToGuid(this SqlServerCompiler compiler, Query query, string primaryKeyName = "id")
{
SqlResult result = compiler.Compile(query);
string sqlComplement = result.Sql;
sqlComplement = sqlComplement.Insert(result.Sql.IndexOf(" VALUE"), $" OUTPUT INSERTED.{primaryKeyName} ");
return(new SqlResult(sqlComplement, result.RawBindings));
}
protected override SqlResult GetSqlResult(Query query)
{
var compiler = new SqlServerCompiler();
SqlResult sqlResult = compiler.Compile(query);
return(sqlResult);
}
private string[] Compile(Query q)
{
return(new[] {
mssql.Compile(q.Clone()).ToString(),
mysql.Compile(q.Clone()).ToString(),
pgsql.Compile(q.Clone()).ToString(),
fbsql.Compile(q.Clone()).ToString(),
});
}
public virtual async Task <IEnumerable <TEntity> > FindByAsync(QueryBuilder <TEntity> queryBuilder)
{
var compiler = new SqlServerCompiler();
var sqlResult = compiler.Compile(queryBuilder);
var query = sqlResult.Sql;
var bindings = sqlResult.Bindings.ToArray();
return(await Context.Database.SqlQuery <TEntity>(query, bindings).ToListAsync());
}
public static void Test()
{
var compiler = new SqlServerCompiler();
var query = new Query("").Where("", 1).Where("Status", "Active");
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
}
public void ShouldAllowWhiteListedOperatorsInNestedWhere()
{
Compiler compiler = new SqlServerCompiler().Whitelist("!!");
Query query = new Query("Table")
.Where(q => q.Where("A", "!!", "value"));
compiler.Compile(query);
}
/// <summary>
/// Generates a report
/// </summary>
/// <param name="dto">report definition</param>
/// <returns>list of data</returns>
public async Task <ReportDataDto <object>?> GenerateReport(ReportDto dto)
{
var reportSource = await db.ReportSources.FirstOrDefaultAsync(w => w.ReportSourceId == dto.ReportSourceId);
if (reportSource == null)
{
return(null);
}
var report = new ReportDataDto <object>();
//var columns = await GetReportSourceColumns(reportSource);
//report.RemovedColumns = CheckColumns(dto, columns);
if (dto.Columns.Count == 0)
{
return(report);
}
var query = new SqlKata.Query(reportSource.SQLName)
{
IsDistinct = true
};
SelectClause(query, dto);
GroupByClause(query, dto);
OrderByClause(query, dto);
var compiler = new SqlServerCompiler();
var result = compiler.Compile(query);
using var cmd = (SqlCommand)db.Database.GetDbConnection().CreateCommand();
var isOpen = cmd.Connection.State == ConnectionState.Open;
if (!isOpen)
{
await cmd.Connection.OpenAsync();
}
cmd.CommandText = result.RawSql;
var adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
var dataSet = new DataSet();
adapter.Fill(dataSet);
if (!isOpen)
{
await cmd.Connection.CloseAsync();
}
report.Data = dataSet.Tables[0].ToList().ToList();
return(report);
}
public static void SqlCompile_QueryLimitAndNestedLimit_BindingValue()
{
var n = new Query().From("Bar");
var q = new Query().From("Foo").Select("MyData").Where("x", true).WhereNotExists(n);
var target = new SqlServerCompiler();
var actual = target.Compile(q).ToString();
Assert.Contains("SELECT [MyData] FROM [Foo] WHERE [x] = True AND NOT EXISTS (SELECT TOP (1) 1 FROM [Bar])", actual);
}
public async Task <UserAccount> Handle(Query request, CancellationToken cancellationToken)
{
var query = new SqlKata.Query("UserAccount")
.Where("Username", request.Username);
var sqlResult = _compiler.Compile(query);
var userAccountPoco =
await _dbConnection.QuerySingleOrDefaultAsync <POCOs.UserAccount>(sqlResult.Sql,
new { p0 = sqlResult.Bindings[0] });
return(_mapper.Map <UserAccount>(userAccountPoco));
}