public static void CreateDatabase(DatabaseFields settings, IHostEnvironment hostingEnvironment)
{
var servername = settings.Servername;
var database = settings.Database;
var username = settings.Username;
var password = settings.Password;
var pathToTableCreationScript = Path.Combine(hostingEnvironment.ContentRootPath, "Database", "OrcaDatabase.sql");
var tableCreationScript = File.ReadAllText(pathToTableCreationScript);
// set Database to postgres in case it hasn't been created yet
using (var conn = new NpgsqlConnection($"Server = {servername}; Database = postgres; User Id= {username}; Password= {password}; Ssl Mode=Prefer;"))
{
conn.Open();
using (var checkDbExistsCommand = new NpgsqlCommand($"SELECT COUNT(datname) FROM pg_catalog.pg_database WHERE datname = '{database}'", conn))
{
bool dbExists = ((long)checkDbExistsCommand.ExecuteScalar()) > 0;
if (!dbExists)
{
using (var dbCreationCommand = new NpgsqlCommand($"CREATE DATABASE {database} ENCODING = 'UTF8'", conn))
{
dbCreationCommand.ExecuteNonQuery();
}
}
}
}
// create DB tables
using (var conn = new NpgsqlConnection($"Server = {servername}; Database = {database}; User Id= {username}; Password= {password}; Ssl Mode=Prefer;"))
{
conn.Open();
using (var sqlScript = new NpgsqlCommand(tableCreationScript, conn))
{
sqlScript.ExecuteNonQuery();
}
}
}
public static bool HasDatabase(DatabaseFields fields)
{
return(fields.Servername != null && fields.Username != null && fields.Password != null && fields.Database != null);
}