public static SqlResult Compile(this Query query, string databaseType) { Compiler compiler = null; if (DataBaseType == ConnectionType.MYSQL) { compiler = new MySqlCompiler(); } else if (DataBaseType == ConnectionType.SQL) { compiler = new MySqlCompiler(); } else if (DataBaseType == ConnectionType.ORACLE) { compiler = new OracleCompiler(); } else if (DataBaseType == ConnectionType.SQLITE) { compiler = new SqliteCompiler(); } else if (DataBaseType == ConnectionType.POSTRGRESQL) { compiler = new PostgresCompiler(); } return(compiler.Compile(query)); }
static SqlStatements() { var conn = DatabaseFactory.GetFactory().GetConnection(); var compiler = new PostgresCompiler(); queryfactory = new QueryFactory(conn, compiler); }
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 static Compiler GetCompiler(DatabaseType databaseType, string connectionString) { Compiler compiler = null; if (databaseType == DatabaseType.MySql) { compiler = new MySqlCompiler(); } else if (databaseType == DatabaseType.SqlServer) { compiler = new SqlServerCompiler { UseLegacyPagination = DatoryUtils.IsUseLegacyPagination(databaseType, connectionString) }; } else if (databaseType == DatabaseType.PostgreSql) { compiler = new PostgresCompiler(); } else if (databaseType == DatabaseType.Oracle) { compiler = new OracleCompiler { UseLegacyPagination = DatoryUtils.IsUseLegacyPagination(databaseType, connectionString) }; } return(compiler); }
public static SqlResult CompileWithLastId(this PostgresCompiler compiler, Query query, string primaryKeyName = "id") { SqlResult result = compiler.Compile(query); //return new SqlResult(result.Sql + ";SELECT lastval();", result.RawBindings); return(new SqlResult(result.Sql + $" RETURNING \"{primaryKeyName}\"", result.RawBindings)); }
private static Compiler GetCompiler <T>() where T : IDbConnection, new() { Compiler result; var name = typeof(T).Name; switch (name) { case "SqliteConnection ": result = new SqliteCompiler(); break; case "FbConnection": result = new FirebirdCompiler(); break; case "MySqlConnection": result = new MySqlCompiler(); break; case "OracleConnection": result = new OracleCompiler(); break; case "NpgsqlConnection": result = new PostgresCompiler(); break; default: result = new SqlServerCompiler(); break; } return(result); }
private static string ToRawSQL(Query query) { var compiler = new PostgresCompiler(); SqlResult result = compiler.Compile(query); return(result.ToString()); }
public void Delete_WithoutCondition_Test() { var compiler = new PostgresCompiler(); var builder = new QueryBuilder <Employee>(compiler); var result = builder.Delete() as DbCompileResult; Assert.NotNull(result); Assert.Equal("DELETE FROM Employee", result.SqlQuery); }
public async Task <TResult> ExecuteQuery <TResult>(Func <QueryFactory, Task <TResult> > execute) { using (var connection = new NpgsqlConnection(_connectionString)) { await connection.OpenAsync(); var compiler = new PostgresCompiler(); var db = new QueryFactory(connection, compiler); return(await execute(db)); } }
public void Delete_NotNullCondition_Test() { var compiler = new PostgresCompiler(); var builder = new QueryBuilder <Employee>(compiler); var result = builder.Delete(e => e.Name != null) as DbCompileResult; Assert.NotNull(result); Assert.Equal(0, result.QueryParameters.Count); Assert.Equal("DELETE FROM Employee WHERE (\"Name\" IS NOT NULL)", result.SqlQuery); }
private async Task <TResult> ExecuteQuery <TResult>(Func <QueryFactory, Task <TResult> > execute) { using (var connection = new NpgsqlConnection(_options.ConnectionString)) { await connection.OpenAsync(); connection.TypeMapper.UseJsonNet(); var compiler = new PostgresCompiler(); var db = new QueryFactory(connection, compiler); return(await execute(db)); } }
public void Delete_GreaterThanCondition_Test() { var compiler = new PostgresCompiler(); var builder = new QueryBuilder <Employee>(compiler); var result = builder.Delete(e => e.Id > 1) as DbCompileResult; Assert.NotNull(result); Assert.Equal(1, result.QueryParameters.Count); Assert.Equal(1, result.QueryParameters["@P0"]); Assert.Equal("DELETE FROM Employee WHERE (\"Id\" > @P0)", result.SqlQuery); }
public void Delete_ContainsCondition_Test() { var compiler = new PostgresCompiler(); var builder = new QueryBuilder <Employee>(compiler); var result = builder.Delete(e => e.Name.Contains("test")) as DbCompileResult; Assert.NotNull(result); Assert.Equal(1, result.QueryParameters.Count); Assert.Equal("test", result.QueryParameters["@P0"]); Assert.Equal("DELETE FROM Employee WHERE (\"Name\" LIKE '%' || @P0 || '%')", result.SqlQuery); }
public static async IAsyncEnumerable <T> QueryKataAsync <T>(this NpgsqlConnection conn, SqlKata.Query query) { var compiled = new PostgresCompiler().Compile(query); await using var reader = await conn.ExecuteReaderAsync(compiled.Sql, compiled.NamedBindings); var parser = reader.GetRowParser <T>(); while (await reader.ReadAsync()) { yield return(parser(reader)); } }
public void Delete_SimpleEqualWithOrCondition_Test() { var compiler = new PostgresCompiler(); 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 Delete_AndWithOrWithGroupingCondition_Test() { var compiler = new PostgresCompiler(); 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 static void AddAdoDataAccess(this IServiceCollection services, string connectionString) { services.AddTransient <DbConnection>(s => ConnectionFactory.Create(connectionString)); services.AddTransient <QueryFactory>(s => { var sqlCompiler = new PostgresCompiler(); var connection = s.GetService <DbConnection>(); return(new QueryFactory(connection, sqlCompiler)); }); services.AddRepositories(); }
private static QueryFactory SqlServerQueryFactory() { var compiler = new PostgresCompiler(); var connection = new SqlConnection( "Server=tcp:localhost,1433;Initial Catalog=Lite;User ID=sa;Password=P@ssw0rd" ); var db = new QueryFactory(connection, compiler); db.Logger = result => { Console.WriteLine(result.ToString()); }; return(db); }
public void ShouldHitDB() { var Configuration = StubConfiguration.GetTestAppsettings(); var str = Configuration["ConnectionString"]; var connection = new NpgsqlConnection( str //"Host=localhost;Port=5432;Username=ahatch1490;Database=exposure;" ); var compiler = new PostgresCompiler(); var q = new QueryFactory(connection, compiler); var sites = q.Query("sites").Limit(10).Get <Site>(); Assert.True(sites.Count() == 1); }
public void Insert_WithColumOrAttribute_Test() { var compiler = new PostgresCompiler(); var builder = new QueryBuilder <EmployeeWithAttributes>(compiler); var employee = new EmployeeWithAttributes() { Id = 1, Name = "Employee Name" }; var result = builder.Insert(employee) as DbCompileResult; Assert.NotNull(result.QueryParameters); Assert.Equal(1, result.QueryParameters.Count); Assert.Equal(result.QueryParameters["@P0"], employee.Name); Assert.Equal("INSERT INTO dbo.employee (\"name\") VALUES (@P0)", result.SqlQuery); }
// This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddMvc(); services.AddSingleton <IContextClient, ContextClient>(); services.AddSingleton <IGoodReadsSettings, GoodReadsSettings>(); services.AddSingleton <IXMLClient, XMLClient>(); services.AddSingleton <IGoodReadsService, GoodReadsService>(); services.AddSingleton <SiteService, SiteService>(); services.AddSingleton(qf => { var str = Configuration["ConnectionString"]; //"Host=localhost;Port=5432;Username=ahatch1490;Database=exposure;" var connection = new NpgsqlConnection(str); var compiler = new PostgresCompiler(); return(new QueryFactory(connection, compiler)); }); }
public void Test2() { var connStr = "User ID=postgres;Password=1234;Host=localhost;Port=5432;Database=DemoForGenericRepos;Pooling=true;"; var compiler = new PostgresCompiler(); var logger = new ConsoleLogger(LogLevel.Debug | LogLevel.Error | LogLevel.Warning); using (var crud = new Crud(compiler, logger, () => { return(new NpgsqlConnection(conStr)); })) { try { var employee = crud.Select <Employee>(e => e.FirstName == "fist name"); } catch (Exception ex) { } } }
public static IServiceCollection AddPostgressSQL(this IServiceCollection services, IConfiguration configuration, bool addHealthCheck = true) { var logger = services.BuildServiceProvider().GetRequiredService <ILogger <PostgresSettings> >(); services.Configure <PostgresSettings>(configuration.GetSection(nameof(PostgresSettings))); services.AddScoped <SqlKataPostgresConnection>((provider) => { var settings = provider.GetRequiredService <IOptions <PostgresSettings> >().Value; var connection = new NpgsqlConnection(configuration.GetConnectionString("PostgreSQL")); void ProvideClientCertificates(X509CertificateCollection clientCerts) { var clientCertPath = settings.ClientCertPath; var cert = new X509Certificate2(clientCertPath); clientCerts.Add(cert); } if (!string.IsNullOrEmpty(settings.ClientCertPath)) { connection.ProvideClientCertificatesCallback += ProvideClientCertificates; } var compiler = new PostgresCompiler(); return(new SqlKataPostgresConnection(connection, compiler) { Logger = compiled => logger.LogInformation(compiled.ToString()) }); }); //Adicionando healthcheck if (addHealthCheck) { services.AddHealthChecks() .AddNpgSql(configuration.GetConnectionString("PostgreSQL"), tags: new string[] { "db", "sql", "postgres" }); } return(services); }
public void Test1() { var compiler = new PostgresCompiler(); var logger = new ConsoleLogger(LogLevel.Debug | LogLevel.Error | LogLevel.Warning); using (var crud = new Crud(compiler, logger, () => { return(new NpgsqlConnection(conStr)); })) { // Assert.Throws<System.Net.Sockets.SocketException>(() => // { // var affectedRows = crud.Insert(new Employee() // { // Guid = Guid.NewGuid(), // FirstName = "fist name", // LastName = "last name", // Active = true, // CreateDate = DateTime.Now, // }); // Assert.Equal(1, affectedRows); // }); } }
public static QueryFactory CreateInstance(IDbConnection connection) { if (connection.GetType() == typeof(MySqlConnection)) { var compiler = new MySqlCompiler(); return(new QueryFactory(connection, compiler)); } if (connection.GetType() == typeof(NpgsqlConnection)) { var compiler = new PostgresCompiler(); return(new QueryFactory(connection, compiler)); } if (connection.GetType() == typeof(SqlConnection)) { var compiler = new SqlServerCompiler(); return(new QueryFactory(connection, compiler)); } return(null); }
public static NpgsqlCommand ToNpgsqlCommand(this Query query) { if (query == null) { throw new ArgumentNullException(nameof(query)); } SqlResult compiledQuery = new PostgresCompiler() .Compile(query); NpgsqlCommand compiledCommand = new NpgsqlCommand(compiledQuery.Sql); if (compiledQuery.NamedBindings != null) { foreach (KeyValuePair <string, object> paramWithValue in compiledQuery.NamedBindings) { compiledCommand.Parameters.AddWithValue(paramWithValue.Key, paramWithValue.Value); } } return(compiledCommand); }
private void JsonData_TextChanged() { if (((SQL.QueryType)LstQueryTypes.SelectedItem) == SQL.QueryType.SELECT) { var compiler = new PostgresCompiler(); Query select = new Query((string)((TreeViewItem)TrvQueueList.SelectedItem).Header); bool found = false; foreach (StackPanelLocal stkl in StkJsonData.Children) { if (Regex.IsMatch(stkl.Text, @"^.*=(['].+[']|[0-9]+)$")) { found = true; if (Regex.IsMatch(stkl.Text, @"='true'$")) { select.WhereTrue(Regex.Match(stkl.Text, @"^.*(?=[=])").Value); } else if (Regex.IsMatch(stkl.Text, @"='false'$")) { select.WhereFalse(Regex.Match(stkl.Text, @"^.*(?=[=])").Value); } else { select.WhereLike(Regex.Match(stkl.Text, @"^.*(?=[=])").Value, Regex.Match(stkl.Text, @"(?<=[=]).*$").Value, true); } } } if (!found) { var res1 = compiler.Compile(select); TxtQueryStart.Text = res1.Sql; return; } SqlResult result = compiler.Compile(select); string sql = result.Sql; List <object> bindings = result.Bindings; int i = 0; foreach (object o in bindings) { sql = sql.Replace($"@p{i++}", o.ToString()); } TxtQueryStart.Text = sql; } else if (((SQL.QueryType)LstQueryTypes.SelectedItem) == SQL.QueryType.INSERT) { var compiler = new PostgresCompiler(); Query insert = new Query((string)((TreeViewItem)TrvQueueList.SelectedItem).Header); List <string> lst = new List <string>(); foreach (StackPanelLocal stkl in StkJsonData.Children) { if (Regex.IsMatch(stkl.Text, @"^.*=(['].+[']|[0-9]+)$")) { lst.Add(stkl.Text); } } if (lst.Count == 0) { TxtQueryStart.Text = $"INSERT INTO {((TreeViewItem)TrvQueueList.SelectedItem).Header}"; return; } List <string> names = new List <string>(); List <string> values = new List <string>(); foreach (string s in lst) { names.Add(Regex.Match(s, @"^.*(?=[=])").Value); values.Add(Regex.Match(s, @"(?<=[=]).*$").Value); } insert.AsInsert(names.ToArray(), values.ToArray()); SqlResult result = compiler.Compile(insert); string sql = result.Sql; List <object> bindings = result.Bindings; int i = 0; foreach (object o in bindings) { sql = sql.Replace($"@p{i++}", o.ToString()); } TxtQueryStart.Text = sql; } else if (((SQL.QueryType)LstQueryTypes.SelectedItem) == SQL.QueryType.UPDATE) { } }
public PostgreSqlLimitTests() { compiler = Compilers.Get <PostgresCompiler>(EngineCodes.PostgreSql); }
/// <summary> /// Create new instance of PostgreSQL script generator /// </summary> /// <param name="options"></param> public PostgresqlTestGenerator(Action <TestGeneratorOptions> options = null) : base(Models.DatabaseKind.PostgreSql, options) { _postgresCompiler = new PostgresCompiler(); }
public ValuesController(CosmosDbService cosmosDbService, PostgresCompiler compiler) { _cosmosDbService = cosmosDbService; _compiler = compiler; }