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();
}
}
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();
}
}
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();
}
}
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);
}
// ===== 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();
}
}
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();
}
}
}
/// <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);
}