public async Task create_server_async_without_using_block()
        {
            var server = new MysticMind.PostgresEmbed.PgServer(
                "9.5.5.1",
                PG_USER,
                addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
                clearInstanceDirOnStop: true);

            try
            {
                await server.StartAsync();

                string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
                var    conn    = new Npgsql.NpgsqlConnection(connStr);
                var    cmd     =
                    new Npgsql.NpgsqlCommand(
                        "CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
                        conn);

                await conn.OpenAsync();

                await cmd.ExecuteNonQueryAsync();

                conn.Close();
            }
            finally
            {
                await server.StopAsync();
            }
        }
        public async Task create_server_async_and_table_test()
        {
            using (var server = new MysticMind.PostgresEmbed.PgServer(
                       "9.5.5.1",
                       PG_USER,
                       addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
                       clearInstanceDirOnStop: true))
            {
                await server.StartAsync();

                // Note: set pooling to false to prevent connecting issues
                // https://github.com/npgsql/npgsql/issues/939
                string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
                var    conn    = new Npgsql.NpgsqlConnection(connStr);
                var    cmd     =
                    new Npgsql.NpgsqlCommand(
                        "CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
                        conn);

                await conn.OpenAsync();

                await cmd.ExecuteNonQueryAsync();

                conn.Close();
            }
        }
예제 #3
0
        private async Task UpdateAllocations()
        {
            con = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["TalentDataContextPostgres"].ConnectionString);

            try
            {
                con.Open();
                List <ProjectAllocation> oldPractices = sqlDataContext.ProjectAllocations.ToList();
                // update primary column id
                List <ProjectAllocation> newPractices = postgressDataContext.ProjectAllocations.ToList();
                foreach (ProjectAllocation newRec in newPractices)
                {
                    int    oldPracticeID     = oldPractices.FirstOrDefault(p => p.EmployeeID == newRec.EmployeeID && p.ProjectID == newRec.ProjectID).AllocationEntryID;
                    string qry               = $"UPDATE \"TalentManager\".\"ProjectAllocation\" SET \"AllocationEntryID\"={oldPracticeID} WHERE \"AllocationEntryID\"={newRec.AllocationEntryID}";
                    Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
                    int val = await cmd.ExecuteNonQueryAsync();
                }
            }
            catch (Exception) { }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
예제 #4
0
        private async Task UpdateDropDownSubCategories()
        {
            con = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["TalentDataContextPostgres"].ConnectionString);

            try
            {
                con.Open();
                List <DropDownSubCategory> oldCategories = sqlDataContext.DropDownSubCategories.ToList();
                // update primary column id
                List <DropDownSubCategory> newCategories = postgressDataContext.DropDownSubCategories.ToList();
                foreach (DropDownSubCategory newRec in newCategories)
                {
                    int    oldPracticeID     = oldCategories.FirstOrDefault(p => p.SubCategoryName == newRec.SubCategoryName).SubCategoryID;
                    string qry               = $"UPDATE \"TalentManager\".\"DropDownSubCategory\" SET \"SubCategoryID\"={oldPracticeID} WHERE \"SubCategoryID\"={newRec.SubCategoryID}";
                    Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
                    int val = await cmd.ExecuteNonQueryAsync();
                }
            }
            catch (Exception) { }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
예제 #5
0
        private async Task UpdateSystemSettings()
        {
            con = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["TalentDataContextPostgres"].ConnectionString);

            try
            {
                con.Open();
                sqlDataContext = new Agilisium.TalentManager.Model.TalentManagerDataContext();
                List <SystemSetting> oldSettings = sqlDataContext.SystemSettings.ToList();
                // update primary column id
                postgressDataContext = new Agilisium.TalentManager.PostgresModel.TalentManagerDataContext();
                List <SystemSetting> newSettings = postgressDataContext.SystemSettings.ToList();
                foreach (SystemSetting newRec in newSettings)
                {
                    int    oldPracticeID     = oldSettings.FirstOrDefault(p => p.SettingName == newRec.SettingName).SettingEntryID;
                    string qry               = $"UPDATE \"TalentManager\".\"SystemSettings\" SET \"SettingEntryID\"={oldPracticeID} WHERE \"SettingEntryID\"={newRec.SettingEntryID}";
                    Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
                    int val = await cmd.ExecuteNonQueryAsync();
                }
            }
            catch (Exception) { }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        /// <summary>
        /// Runs a query and returns void
        /// </summary>
        /// <param name="name">Stored function name</param>
        /// <param name="param">List of parameter to use. Set null for no parameters.</param>
        /// <param name="connectionString">Connection sting to use</param>
        /// <param name="cancellationToken">Cancellation Token</param>
        /// <returns>boolean</returns>
        public static async Task <bool> VoidAsync(string name, IEnumerable <Npgsql.NpgsqlParameter> param, string connectionString, CancellationToken cancellationToken = default(CancellationToken))
        {
            if (Generics.StringExtensions.IsEmpty(name) ||
                Generics.StringExtensions.IsEmpty(connectionString) ||
                cancellationToken.IsCancellationRequested)
            {
                return(false);
            }

            using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
            {
                using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection))
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    if (param != null)
                    {
                        foreach (var item in param)
                        {
                            command.Parameters.Add(item);
                        }
                    }
                    else
                    {
                        command.Parameters.Clear();
                    }

                    try
                    {
                        if (cancellationToken.IsCancellationRequested)
                        {
                            throw new OperationCanceledException(cancellationToken);
                        }

                        await connection.OpenAsync(cancellationToken);

                        var transaction = connection.BeginTransaction();
                        command.Transaction = transaction;
                        var t = await command.ExecuteNonQueryAsync(cancellationToken);

                        transaction.Commit();
                    }
                    catch (AggregateException e)
                    {
                        ExceptionDispatchInfo.Capture(e.InnerException).Throw();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }

            return(true);
        }
예제 #7
0
        // ===== FUNCOES ========
        public async Task LogRequest(string Usuario, string Endpoint, long TempoRequisicao)
        {
            using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
            {
                Npgsql.NpgsqlCommand comm = conn.CreateCommand();

                comm.CommandType = CommandType.Text;
                comm.CommandText = "INSERT INTO LogRequest (Usuario, Endpoint, TempoRequisicao, DataRequest) VALUES ('" + Usuario + "', '" + Endpoint + "', " + TempoRequisicao.ToString() + ", now());";

                conn.Open();

                await comm.ExecuteNonQueryAsync();
            }
        }
예제 #8
0
        public static async Task ExecuteDatabaseAwareTest(Func <DocumentStore, MartenBasedClientRepository, Task> executor)
        {
            Random random1 = new();

            String dbName = $"MartenBased-{random1.GetAlphanumericString()}";

            String connectionString = $"{ConfigurationUtility.GetConnectionString("PostgresTest")};Database={dbName};";

            var store = DocumentStore.For((storeOptions) =>
            {
                storeOptions.CreateDatabasesForTenants(c =>
                {
                    c.ForTenant()
                    .WithEncoding("UTF-8")
                    .ConnectionLimit(-1)
                    .OnDatabaseCreated(_ =>
                    {
                    });
                });
                storeOptions.Connection(connectionString);
                storeOptions.AutoCreateSchemaObjects = AutoCreate.All;
            });

            MartenBasedClientRepository repo = new(store);

            try
            {
                await executor(store, repo);
            }
            finally
            {
                store.Advanced.Clean.CompletelyRemoveAll();
                store.Dispose();

                using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection($"{ConfigurationUtility.GetConnectionString("PostgresTest")};Database=postgres;"))
                {
                    await connection.OpenAsync();

                    Npgsql.NpgsqlCommand dropCmd = new Npgsql.NpgsqlCommand($"DROP DATABASE \"{dbName}\" WITH (FORCE);", connection);
                    await dropCmd.ExecuteNonQueryAsync();
                }
            }
        }
예제 #9
0
        /// <summary>
        /// Query with no return
        /// </summary>
        /// <param name="query">Query string</param>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="cancellationToken">Cancellation Token</param>
        /// <returns>Boolean</returns>
        public static async Task <bool> VoidAsync(string query, string connectionString, CancellationToken cancellationToken = default(CancellationToken))
        {
            if (Generics.StringExtensions.IsEmpty(query) || Generics.StringExtensions.IsEmpty(connectionString))
            {
                return(false);
            }

            using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
            {
                using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection))
                {
                    command.CommandType = System.Data.CommandType.Text;

                    try
                    {
                        await connection.OpenAsync(cancellationToken);

                        var transaction = connection.BeginTransaction();
                        command.Transaction = transaction;
                        var t = await command.ExecuteNonQueryAsync(cancellationToken);

                        transaction.Commit();
                    }
                    catch (AggregateException e)
                    {
                        ExceptionDispatchInfo.Capture(e.InnerException).Throw();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }

            return(true);
        }