public async Task <Student> GetByID(int id)
{
var q = new Query(nameof(Data.Student)).Where(nameof(Data.Student.Id), id).Take(1);
var query = new SqlServerCompiler().Compile(q);
using (IDbConnection conn = Connection)
{
conn.Open();
var result = await conn.QueryAsync <Student>(query.Sql, query.NamedBindings);
return(result.FirstOrDefault());
}
}
/// <inheritdoc/>
public async Task <IEnumerable <string> > GetAllUrisAsync()
{
using (IDbConnection connection = await this.GetConnection())
{
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
return(await db
.Query("Clients")
.Select("Uri")
.WhereNotNull("Uri")
.GetAsync <string>());
}
}
public QueryFactory GetQueryFactory()
{
try
{
var connection = new SqlConnection(_connectionString);
var compiler = new SqlServerCompiler();
return(new QueryFactory(connection, compiler));
}
catch (Exception e)
{
throw new Exception("Check that appsettings.config contains valid connection string", e);
}
}
static void Main(string[] args)
{
var connection = new SqlConnection(@"Data Source=DATASOURCE;initial Catalog=DATABASE;User Id=USER;Password=PASSWORD");
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
UserList(db);
SearchUser(db);
Console.ReadLine();
}
public void Delete_ContainsWithAndCondition_Test()
{
var compiler = new SqlServerCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => e.Id == 1 && e.Name.Contains("test")) as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(2, result.QueryParameters.Count);
Assert.Equal(1, result.QueryParameters["@P0"]);
Assert.Equal("test", result.QueryParameters["@P1"]);
Assert.Equal("DELETE FROM Employee WHERE (([Id] = @P0) AND ([Name] LIKE '%' + @P1 + '%'))", result.SqlQuery);
}
public static void SqlCompile_QueryLimitAndNestedLimit_ReturnsQueryWithTop()
{
var q = new Query().From("Foo as src").Limit(1).Select("MyData");
var n = new Query().From("Bar").Limit(1).Select("MyData");
q.Select(n, "Bar");
var target = new SqlServerCompiler();
var actual = target.Compile(q).ToString();
Assert.Contains("SELECT TOP (1) [MyData], (SELECT TOP (1) [MyData] FROM [Bar]) AS [Bar] FROM [Foo] AS [src]", actual);
}
/// <inheritdoc/>
public async Task <IdSrvClientDto> GetByIdAsync(Guid id)
{
using (IDbConnection connection = await this.GetConnection())
{
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
return(await db
.Query("Clients")
.Where(new { Id = id })
.Select("Id", "Name", "Uri", "Secret", "IsBlocked")
.FirstOrDefaultAsync <IdSrvClientDto>());
}
}
public void Delete_SimpleEqualWithOrCondition_Test()
{
var compiler = new SqlServerCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => e.Id == 1 || e.Name == "test") as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(2, result.QueryParameters.Count);
Assert.Equal(1, result.QueryParameters["@P0"]);
Assert.Equal("test", result.QueryParameters["@P1"]);
Assert.Equal("DELETE FROM Employee WHERE (([Id] = @P0) OR ([Name] = @P1))", result.SqlQuery);
}
public void Setup()
{
_compiler = new SqlServerCompiler();
_base = new Query("User")
.Select("UserId", "UserName", "CreatedAt");
_generic =
new Query <User>()
.Select(
u => u.UserId,
u => u.UserName,
u => u.CreatedAt);
}
public virtual async Task <Guid[]> GetIDsBy(QueryBuilder <TEntity> queryBuilder)
{
queryBuilder.Select($"{queryBuilder.TableName}.{"Id"}");
//TODO: Sql compiler
var compiler = new SqlServerCompiler();
var sqlResult = compiler.Compile(queryBuilder);
var query = sqlResult.Sql;
var bindings = sqlResult.Bindings.ToArray();
var result = await Context.Database.SqlQuery <Guid>(query, bindings).ToListAsync();
return(result.ToArray());
}
public static void SqlCompile_QueryLimitAndNestedLimit_BindingValue()
{
var n = new Query().From("Bar");
var q = new Query().From("Foo").Where("x", true).WhereNotExists(n);
// var q = new Query().From("Foo").Where("C", "c").WhereExists(n).Where("A", "a");
var target = new SqlServerCompiler();
var actual = target.Compile(q).ToString();
Assert.Contains("SELECT * FROM [Foo] WHERE [x] = true AND NOT EXISTS (SELECT TOP (1) 1 FROM [Bar])", actual);
// Assert.Contains("SELECT * FROM [Foo] WHERE [C] = 'c' AND EXISTS (SELECT TOP (1) 1 FROM [Bar]) AND [A] = 'a'", actual);
}
public int GetDatasetsCount()
{
Query query = new Query(Tables.Datasets).AsCount("data_set_id");
SqlResult queryResult = new SqlServerCompiler().Compile(query);
return(Convert.ToInt32(
SqlExecutionInstance.ExecuteScalar(new SqlCommand(queryResult.ToString())
{
CommandType = CommandType.Text
},
null,
new SqlConnection(this.connectionString))));
}
public override void Execute()
{
JDataBase.Resolve <SqlConnection>()
.DbExecutor <WEATHER_FORECAST>(con => {
var compiler = new SqlServerCompiler();
var db = new QueryFactory(con, compiler);
var weathers = db.Query("dbo.WEATHER_FORECAST").Get <WEATHER_FORECAST>();
weathers.forEach(item => {
item.TEMPERATURE_F = 32 + (int)(item.TEMPERATURE_C / 0.5556);
});
Result = weathers;
});
}
public void Delete_AndWithOrWithGroupingCondition_Test()
{
var compiler = new SqlServerCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => (e.Id == 1 && e.Name == "value1") || e.Name != "value2") as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(3, result.QueryParameters.Count);
Assert.Equal(1, result.QueryParameters["@P0"]);
Assert.Equal("value1", result.QueryParameters["@P1"]);
Assert.Equal("value2", result.QueryParameters["@P2"]);
Assert.Equal("DELETE FROM Employee WHERE ((([Id] = @P0) AND ([Name] = @P1)) OR ([Name] <> @P2))", result.SqlQuery);
}
public int DeleteNote(int idNote)
{
try
{
SqlServerCompiler compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
var rs = db.Query("Note").Where("IdNote", idNote).Delete();
return(rs);
}
catch (Exception ex)
{
throw ex;
}
}
private async Task <Guid> InsertDefaultClient(int number, bool hasUri = true, bool isBlocked = false)
{
using (IDbConnection connection = await this.ConnectionFactory.GetConnectionAsync())
{
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
return(await this.InsertClient(
$"n{number}",
hasUri?$"u{number}" : null,
$"p{number}",
isBlocked,
db));
}
}
/// <summary>
/// This Function return an etudiant by CNE
/// </summary>
/// <param name="CNI"></param>
/// <returns></returns>
public IEnumerable <Etudiant> GetEtudiantByCNE(string CNE)
{
try
{
SqlServerCompiler compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
var etudiant = db.Query("Etudiant").Where("Cne", CNE).Get <Etudiant>();
return(etudiant);
}
catch (Exception ex)
{
Console.WriteLine("Etudiant DAO Get Etudiant : " + ex.Message);
return(null);
}
}
public override void Execute()
{
//use sqlkata
var query = new Query("WEATHER_FORECAST").Where("ID", Request.ID).Select("*");
JDatabaseResolver.Resolve <SqlConnection>()
.DbExecutor(con => {
var compiler = new SqlServerCompiler();
var db = new QueryFactory(con, compiler);
var weather = db.Query("dbo.WEATHER_FORECAST").Where("ID", Request.ID)
.FirstOrDefault <WEATHER_FORECAST>();
weather.TEMPERATURE_F = 32 + (int)(weather.TEMPERATURE_C / 0.5556);
Result = weather;
});
}
public void update_should_compile_literal_without_parameters_holders()
{
var query = new Query("MyTable").AsUpdate(new
{
Name = "The User",
Address = new UnsafeLiteral("@address")
});
var compiler = new SqlServerCompiler();
var result = compiler.Compile(query);
Assert.Equal(
"UPDATE [MyTable] SET [Name] = ?, [Address] = @address",
result.RawSql);
}
/// <summary>
/// delete record from the database
/// </summary>
/// <param name="idAnneeScolaire"></param>
/// <returns></returns>
public int DeleteAnneeScolaire(int idAnneeScolaire)
{
try
{
SqlServerCompiler compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
var rs = db.Query("AnneeScolaire").Where("IdAnneeScolaire", idAnneeScolaire).Delete();
return(rs);
}
catch (Exception ex)
{
Console.WriteLine("AnneeScolaire DAO Delete : " + ex.Message);
return(0);
}
}
static void Main(string[] args)
{
var query = new Query("accounts").AsInsert(new
{
name = "new Account",
currency_id = "USD",
created_at = DateTime.UtcNow,
Value = SqlKata.Expressions.UnsafeLiteral("nextval('hello')", replaceQuotes: false)
});
var compiler = new SqlServerCompiler();
var sql = compiler.Compile(query).Sql;
Console.WriteLine(sql);
}
public Classe GetClasse(int idClasse)
{
try
{
SqlServerCompiler compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
var classe = db.Query("Classe").Where("IdClasse", idClasse).FirstOrDefault <Classe>();
return(classe);
}
catch (Exception ex)
{
Console.WriteLine("Classe DAO Get Classe : " + ex.Message);
return(null);
}
}
/// <summary>
/// This Function return an etudiant by idEtudiant
/// </summary>
/// <param name="idEtudiant"></param>
/// <returns></returns>
public Etudiant GetEtudiant(int idEtudiant)
{
try
{
SqlServerCompiler compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
var etudiant = db.Query("Etudiant").Where("IdEtudiant", idEtudiant).FirstOrDefault <Etudiant>();
return(etudiant);
}
catch (Exception ex)
{
Console.WriteLine("Etudiant DAO Get Etudiant : " + ex.Message);
return(null);
}
}
/// <summary>
/// This function returns the number of lines are affected by delete statement
/// </summary>
/// <param name="idEtudiant"></param>
/// <returns></returns>
public int DeleteEtudiant(int idEtudiant)
{
try
{
SqlServerCompiler compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
var rs = db.Query("Etudiant").Where("IdEtudiant", idEtudiant).Delete();
return(rs);
}
catch (Exception ex)
{
Console.WriteLine("Etudiant DAO Delete : " + ex.Message);
throw new Exception(ex.Message);
}
}
static async Task Main(string[] args)
{
var generator = new SqlServerCompiler();
await generator.GenerateAsync(new ModelConfig
{
ConnectionString = "Persist Security Info=False;User ID=sa;Password=xxx;Initial Catalog=test;Data Source=localhost;",
Database = "test",
Schema = "dbo",
Table = "Test1",
NameSpace = "DataAccess.Model",
FilePath = Directory.GetCurrentDirectory()
});
Console.ReadKey();
}
public QueryFactory GetDestination(string synchronizationSet)
{
var config = _config.Get().SynchronizationSets.Single(x => x.Name == synchronizationSet);
var connection = new SqlConnection(config.Destination.ConnectionString);
connection.Open();
var compiler = new SqlServerCompiler();
compiler.UseLegacyPagination = false;
var queryFactory = new QueryFactory(connection, compiler);
queryFactory.QueryTimeout = config.Source.Timeout ?? 30;
return(queryFactory);
}
/// <inheritdoc/>
public async Task <RepositoryResponse> DeleteAsync(Guid id)
{
using (IDbConnection connection = await this.GetConnection())
{
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
string passwordSalt = Guid.NewGuid().ToString();
int deleted = await db
.Query("Users")
.Where(new { Id = id })
.DeleteAsync();
return(deleted == 1 ? RepositoryResponse.Success : RepositoryResponse.NotFound);
}
}
public void DenyInvalidOperatorsInHaving(string op)
{
SqlServerCompiler compiler = new SqlServerCompiler();
Assert.Throws <Exception>(() =>
{
compiler.Compile(new Query("Table").Having("Id", op, 1));
compiler.Compile(new Query("Table").OrHaving("Id", op, 1));
compiler.Compile(new Query("Table").HavingNot("Id", op, 1));
compiler.Compile(new Query("Table").OrHavingNot("Id", op, 1));
compiler.Compile(new Query("Table").HavingColumns("Col1", op, "Col2"));
compiler.Compile(new Query("Table").OrHavingColumns("Col1", op, "Col2"));
});
}
/// <inheritdoc/>
public async Task <RepositoryResponse> ChangeBlockingAsync(IdSrvClientBlockDto block)
{
if (block == null)
{
throw new ArgumentNullException(nameof(block));
}
using (IDbConnection connection = await this.GetConnection())
{
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
int updated = await db.Query("Clients").Where(new { block.Id }).UpdateAsync(block);
return(updated == 1 ? RepositoryResponse.Success : RepositoryResponse.NotFound);
}
}
public void Parse_GetCommandWith_Depth_3()
{
Storm s = StormDefine();
GetCommand cmd = new GetCommand(s.schema.GetNavigator(), "Appointment");
cmd.With("Contact").With("AssignedUser").With("Contact.OwnerUser");
cmd.ParseSQL();
var compiler = new SqlServerCompiler();
SqlResult result = compiler.Compile(cmd.query);
string sql = result.Sql;
// Previusly Calculated check sum integrity
Assert.Equal("0B13F4B63EE5535DD7E2E3AC63EDB7FF", Checksum(sql));
}
