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